将数组传递给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?

如何使用C＃将数组变量传递给SQL Server存储过程并将数组值插入整行？

Thanks in advance.

提前致谢。

SQL Server table:

SQL Server表：

ID    | Product | Description
-------------------------------
8A3H  | Soda    | 600ml bottle

C# array:

C＃数组：

string[] info = new string[] {"7J9P", "Soda", "2000ml bottle"};

SQL Server stored procedure:

SQL Server存储过程：

ALTER PROC INSERT
    (@INFO_ARRAY ARRAY)
AS
BEGIN
    INSERT INTO Products VALUES (@INFO_ARRAY)
END

2 个解决方案

#1

In SQL Server 2008 and later

在SQL Server 2008及更高版本中

Create a type in SQL Server like so:

在SQL Server中创建一个类型，如下所示：

CREATE TYPE dbo.ProductArray 
AS TABLE
(
  ID INT,
  Product NVARCHAR(50),
  Description NVARCHAR(255)
);

Alter your procedure in SQL Server:

在SQL Server中更改您的过程：

ALTER PROC INSERT_SP
@INFO_ARRAY AS dbo.ProductArray READONLY
AS
BEGIN
    INSERT INTO Products SELECT * FROM @INFO_ARRAY
END

Then you'll need to create a DataTable object with values to pass in C#:

然后，您需要创建一个DataTable对象，其值包含在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;
}

#2

here is a way simpler example:

这是一个更简单的例子：

I've been searching through all the examples and answers of how to pass any array to sql server,till i found this linK, below is how I applied it to my project:

我一直在搜索如何将任何数组传递给sql server的所有示例和答案，直到我找到这个linK，下面是我如何将它应用到我的项目：

--The following code is going to get an Array as Parameter and insert the values of that --array into another table

- 以下代码将获取一个Array作为参数，并将--array的值插入另一个表中

Create Procedure Proc1 



@INFO_ARRAY ARRAY nvarchar(max)  //this is the array your going to pass from C# code

AS

    declare @xml xml

    set @xml = N'<root><r>' + replace(@INFO_ARRAY,',','</r><r>') + '</r></root>'

    Insert into Products 
    select 
     t.value('.','varchar(max)')


    from @xml.nodes('//root/r') as a(t)
END

Hope you enjoy it

希望你喜欢它

#1