如何将数据表作为输入参数从C#传递给存储过程(sql server 2000)? [英] How to pass datatable as input parameter from C# to stored procedure(sql server 2000)?
问题描述
我将数据表作为输入参数从C#传递给存储过程。为此,我创建了名为EmpType的用户定义表类型。数据表包含id,Name,Lname,Mobileno,EmpId。 Employee表包含[Name],[Lname],[mobno],[Did]作为列。我想将数据表中的记录插入到尚未存在的Employee表中。这是我的sql部分。
CREATE TYPE [dbo]。[EmpType] AS TABLE
(
[名称] [ varchar ]( 50 ) NULL ,
[Lname] [ varchar ]( 10 ) NULL ,
[MobileNo] [ varchar ]( 50 ) NULL ,
[EmpId] [ int ] NULL
)
创建 过程 Proc_InsertEmpDetails
@ tblEmp EmpType READONLY,
@ DId int
as
开始
INSERT INTO 员工
([姓名],[Lname],[mobno],[已])
SELECT [名称],[Lname],[mobno] @ DId
FROM < span class =code-sdkkeyword> @ tblEmp A
内部 加入 (
选择 min(Id)为 minID,mobno 来自 @ tblEmp group by mobno
)MinIDTbl
MinIDTb l.minID = A.ExcelId
WHERE NOT EXISTS ( SELECT 1
FROM 员工B
WHERE B. [mobno] = A. [mobno]
AND B. [Did] = @ DId )
end
工作正常。我正在使用sql server 2013.但我的开发服务器是sql server 2000.Sql server 2000不支持用户定义的表类型。有没有其他方法可以做到这一点?
我希望以下链接可以帮助你
http://sqlraaga.blogspot.in/2014/10/what-is-tvp-table-value-parameter-how.html [ ^ ]
< blockquote> TVP是一个美丽的东西在 c# [< a href =https://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspxtarget =_ blanktitle =New Window> ^ ]
(不幸的是,我不得不在OLEDB和ODBC中使用它们......)
I am passing datatable as input parameter from C# to stored procedure. For that I created user defined table type called EmpType. Datatable contains id, Name,Lname,Mobileno,EmpId. Employee table contains [Name],[Lname],[mobno],[Did] as columns. I want to insert record from datatable to Employee table which are not already present.Here my sql part.
CREATE TYPE [dbo].[EmpType] AS TABLE
(
[Name] [varchar](50) NULL,
[Lname] [varchar](10) NULL,
[MobileNo] [varchar](50) NULL,
[EmpId] [int] NULL
)
Create Procedure Proc_InsertEmpDetails
@tblEmp EmpType READONLY,
@DId int
as
begin
INSERT INTO Employee
([Name],[Lname],[mobno],[Did])
SELECT [Name],[Lname],[mobno] @DId
FROM @tblEmp A
Inner join (
select min(Id) as minID, mobno from @tblEmp group by mobno
) MinIDTbl
on MinIDTbl.minID = A.ExcelId
WHERE NOT EXISTS (SELECT 1
FROM Employee B
WHERE B.[mobno] = A.[mobno]
AND B.[Did] = @DId )
end
It is working properly. I am using sql server 2013.But my developement server is sql server 2000.Sql server 2000 does not support User defined table type.Is there any alternative way to do this?
I hope below link may help you
http://sqlraaga.blogspot.in/2014/10/what-is-tvp-table-value-parameter-how.html[^]
TVP's are a thing of beauty in c#[^]
(Unfortunately, I had to impl them in OLEDB and ODBC ...)
这篇关于如何将数据表作为输入参数从C#传递给存储过程(sql server 2000)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!