将DataTable作为参数传递给存储过程 [英] Passing DataTable to stored procedure as an argument
问题描述
我有一个用C#创建的数据表。
I have a data table created in 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
我的问题是:如何将这三个元组插入SQL表?我们是否需要使用点运算符访问列值?还是有其他方法?
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?
推荐答案
您可以更改存储过程以接受表值参数作为输入。但是,首先,您需要创建一个与C#DataTable的结构匹配的用户定义表TYPE:
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:
CREATE TYPE dbo.PersonType AS TABLE
(
Name NVARCHAR(50), -- match the length of SomeTable.Column1
Age INT
);
调整您的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
在C#中,将数据表绑定到PROC参数时,需要将参数指定为:
In C#, when you bind the datatable to the PROC parameter, you need to specify the parameter as:
parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "dbo.PersonType";
另请参见示例将表值参数传递给存储过程
这篇关于将DataTable作为参数传递给存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!