如何将数据表作为输入参数从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屋！