如何将数组传递给SQL SERVER 2008存储过程并将数组的所有值插入表中 [英] How to pass an array to SQL SERVER 2008 stored procedure and insert all the values of array into a table

问题描述

如何将数组传递给SQL SERVER 2008存储过程并将数组的所有值插入数据库表。



例如：来自ASP.NET C＃，我会传递一个参数，其中有多个值以逗号分隔



如下所示



string category = Cat1，Cat2，Cat3，Cat4;



我想在名为Categories的表中插入上述字符串的所有值。



如何使用SQL SERVER 2008实现这一目标。

How to pass an array to SQL SERVER 2008 stored procedure and insert all the values of a array into a database table.

For Example: From ASP.NET C#, I would pass a parameter having multiple values separated by comma

as below

string category = "Cat1, Cat2, Cat3, Cat4";

I would like to insert all the values of above string in a table called Categories.

How could I achieve this using SQL SERVER 2008.

推荐答案

试试这个：



1.在SQL Server中创建一个类型

as：

Try this:

1. Create a Type in SQL Server
as:

CREATE TYPE dbo.MyDataTable -- you can be more speciifc here
AS TABLE
(
  Category NVARCHAR(200)
);
GO

2.创建存储过程（在SQL Server上）消耗上面创建的TYPE并插入到类别中（假设您的表名是类别

2. Create a Stored Proc(on SQL Server) consume the above TYPE created and insert into Categories(assuming your table name is "Categories"

CREATE PROCEDURE dbo.InsertCategories
  @dt AS dbo.MyDataTable READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.Categories(Category) 
  SELECT Category 
  FROM @dt 
  WHERE Category NOT IN (SELECT Category FROM dbo.Categories);
END

现在c＃部分：

Now the c# part:

string category = "Cat1, Cat2, Cat3, Cat4";
string[] categories = category.Split(',');

DataTable dt_Categories = new DataTable();
dt_Categories.Columns.Add("Category", typeof(String));
DataRow workRow;
foreach(string cat in categories)
{
  workRow = dt_Categories.NewRow();
  workRow["Category"] = cat.Trim();
  dt_Categories.Rows.Add(workRow);
}
using (connectionObject)//assuming you have a connection here
{
    SqlCommand cmd = new SqlCommand("dbo.InsertCategories", connectionObject);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", dt_Categories);
    tvparam.SqlDbType = SqlDbType.Structured;
    cmd.ExecuteNonQuery();
}

您有几种可能性。

将值作为表值参数传递是一个不错的选择。还有其他，请看我的答案：如何将集合值传递给sql server 2008存储过程？ [ ^ ]

除了我在那里提到的，你可以使用像< a href =http://www.codeproject.com/Articles/38843/An-Easy-But-Effective-Way-to-Split-a-String-using>这个 [ ^ ]将字符串拆分为t-sql并将其作为表格。



但在您的情况下，最简单的方法是将其拆分为c＃并简单地多次发出insert语句。不要让事情复杂化！

You have several possibilities.
Passing the values as a table valued parameter is a good option. There are other though, see my answer here: How to pass collection values to sql server 2008 Stored Procedures ?[^]
Besides what I have mentioned there, you can use a function like this[^] to split the string on t-sql side and have it as table.

But in your case, the simplest approach would be split it in c# and simply issue the insert statement multiple times. Don't complicate things!

这篇关于如何将数组传递给SQL SERVER 2008存储过程并将数组的所有值插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！