本文介绍了将数组传递给 SQL Server 存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何使用 C# 将数组变量传递给 SQL Server 存储过程并将数组值插入整行?
How can I pass an array variable to a SQL Server stored procedure using C# and insert array values into a whole row?
提前致谢.
SQL Server 表:
SQL Server table:
ID | Product | Description
-------------------------------
8A3H | Soda | 600ml bottle
C# 数组:
string[] info = new string[] {"7J9P", "Soda", "2000ml bottle"};
SQL Server 存储过程:
SQL Server stored procedure:
ALTER PROC INSERT
(@INFO_ARRAY ARRAY)
AS
BEGIN
INSERT INTO Products VALUES (@INFO_ARRAY)
END
推荐答案
在 SQL Server 2008 及更高版本中
像这样在 SQL Server 中创建一个类型:
Create a type in SQL Server like so:
CREATE TYPE dbo.ProductArray
AS TABLE
(
ID INT,
Product NVARCHAR(50),
Description NVARCHAR(255)
);
在 SQL Server 中更改您的程序:
Alter your procedure in SQL Server:
ALTER PROC INSERT_SP
@INFO_ARRAY AS dbo.ProductArray READONLY
AS
BEGIN
INSERT INTO Products SELECT * FROM @INFO_ARRAY
END
然后您需要创建一个 DataTable
对象,其中包含要在 C# 中传递的值:
Then you'll need to create a DataTable
object with values to pass in C#:
DataTable dt = new DataTable();
//Add Columns
dt.Columns.Add("ID");
dt.Columns.Add("Product");
dt.Columns.Add("Description");
//Add rows
dt.Rows.Add("7J9P", "Soda", "2000ml bottle");
using (conn)
{
SqlCommand cmd = new SqlCommand("dbo.INSERT_SP", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter dtparam = cmd.Parameters.AddWithValue("@INFO_ARRAY", dt);
dtparam.SqlDbType = SqlDbType.Structured;
}
这篇关于将数组传递给 SQL Server 存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!