将数组传递给 SQL Server 存储过程 [英] Passing array to a SQL Server Stored Procedure

问题描述

如何使用 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 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！