本文介绍了数组传递到SQL Server存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何使用C#和插入阵列值成一整排传递一个数组变量到SQL Server存储过程?

提前

感谢。



SQL Server表:

  ID |产品|说明
-------------------------------
8A3H |纯碱| 600毫升瓶



C#数组:

 的String []信息=新的String [] {7J9P,苏打,2000毫升瓶}; 



SQL Server存储过程:

  ALTER PROC INSERT 
(@INFO_ARRAY ARRAY)
AS
BEGIN
INSERT INTO产品VALUES(@INFO_ARRAY)


解决方案

在SQL Server 2008及更高版本



创建像这样在SQL Server类型:

  CREATE TYPE DBO .ProductArray 
如表

ID INT,
的产物NVARCHAR(50),
说明NVARCHAR(255)$ b $二);



ALTER在SQL Server的过程:

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

然后你需要创建一个数据表与值对象在C#中传递:

  DataTable的DT =新的DataTable(); 
//添加列
dt.Columns.Add(ID);
dt.Columns.Add(产品);
dt.Columns.Add(描述);
//添加行
dt.Rows.Add(7J9P,苏打,2000毫升瓶);使用

(康涅狄格州)
{
的SqlCommand CMD =新的SqlCommand(dbo.INSERT_SP,康恩);
cmd.CommandType = CommandType.StoredProcedure;
的SqlParameter dtparam = cmd.Parameters.AddWithValue(@ INFO_ARRAY,DT);
dtparam.SqlDbType = SqlDbType.Structured;
}


How can I pass an array variable to a SQL Server stored procedure using C# and insert array values into a whole row?

Thanks in advance.

SQL Server table:

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

C# array:

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

SQL Server stored procedure:

ALTER PROC INSERT
    (@INFO_ARRAY ARRAY)
AS
BEGIN
    INSERT INTO Products VALUES (@INFO_ARRAY)
END
解决方案

In SQL Server 2008 and later

Create a type in SQL Server like so:

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

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

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

07-31 13:26