将DataTable作为参数传递给存储过程

I have a data table created in C#.

我有一个用C#创建的数据表。

DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Age", typeof(int));

dt.Rows.Add("James", 23);
dt.Rows.Add("Smith", 40);
dt.Rows.Add("Paul", 20);

I want to pass this to the following stored procedure.

我想将此传递给以下存储过程。

CREATE PROCEDURE  SomeName(@data DATATABLE)
AS
BEGIN
    INSERT INTO SOMETABLE(Column2,Column3)
    VALUES(......);
END

My question is : How do we insert those 3 tuples to the SQL table ? do we need to access the column values with the dot operator ? or is there any other way of doing this?

我的问题是:我们如何将这3个元组插入SQL表?我们需要使用点运算符访问列值吗?或者还有其他方法吗?

2 个解决方案

#1

You can change the stored procedure to accept a table valued parameter as an input. First however, you will need to create a user defined table TYPE which matches the structure of the C# DataTable:

您可以更改存储过程以接受表值参数作为输入。首先,您需要创建一个用户定义的表TYPE,它匹配C#DataTable的结构:

CREATE TYPE dbo.PersonType AS TABLE
(
    Name NVARCHAR(50), -- or whatever the length of the `SOMETABLE` column
    Age INT
);

Adjust your SPROC:

调整您的SPROC:

CREATE PROCEDURE dbo.InsertPerson
    @Person dbo.PersonType READONLY
AS
BEGIN
  INSERT INTO SomeTable(Column1, Column2) 
     SELECT p.Name, p.Age
     FROM @Person p;
END

When you bind the datatable to the PROC parameter, you need to specify the parameter as:

将数据表绑定到PROC参数时,需要将参数指定为:

parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "dbo.PersonType";

See also the example here Passing a Table-Valued Parameter to a Stored Procedure

另请参见此处的示例将表值参数传递给存储过程

#2

First you need to create a Userdefined type of table that resembles your actual table. See the example below,

首先,您需要创建一个类似于您的实际表的Userdefined类型的表。见下面的例子,

CREATE TYPE SomeType AS TABLE 
(
   C1 int, 
   C2 VARCHAR(50)
)
After this you need to create a stored procedure that takes this table type   as parameter.


CREATE PROCEDURE SomeUSP
  @tabType SomeType READONLY
AS
BEGIN
   SET NOCOUNT ON;

   INSERT INTO Yourtable(C1,C2)
   SELECT C1,C2 FROM @tabType 
END

That's it...Job done :)

就是这样......工作完成:)

#1