将列表传递给存储过程

I am calling a SQL Server stored procedure from my C# code:

我正在调用c#代码中的SQL Server存储过程:

using (SqlConnection conn = new SqlConnection(connstring))
{
   conn.Open();
   using (SqlCommand cmd = new SqlCommand("InsertQuerySPROC", conn))
   {
      cmd.CommandType = CommandType.StoredProcedure;

      var STableParameter = cmd.Parameters.AddWithValue("@QueryTable", QueryTable);
      var NDistanceParameter = cmd.Parameters.AddWithValue("@NDistanceThreshold", NDistanceThreshold);
      var RDistanceParameter = cmd.Parameters.AddWithValue(@"RDistanceThreshold", RDistanceThreshold);

      STableParameter .SqlDbType = SqlDbType.Structured;
      NDistanceParameter.SqlDbType = SqlDbType.Int;
      RDistanceParameter.SqlDbType = SqlDbType.Int;

      // Execute the query
      SqlDataReader QueryReader = cmd.ExecuteReader();

My stored proc is fairly standard but does a join with QueryTable (hence the need for for using a stored proc).

我的存储proc是相当标准的，但是与QueryTable进行连接(因此需要使用存储proc)。

Now: I want to add a list of strings, List<string>, to the parameter set. For example, my stored proc query goes like this:

现在:我想在参数集中添加一个字符串列表，列表。

SELECT feature 
FROM table1 t1 
INNER JOIN @QueryTable t2 ON t1.fid = t2.fid 
WHERE title IN <LIST_OF_STRINGS_GOES_HERE>

However, the list of strings is dynamic and a few hundred long.

然而，字符串列表是动态的，有几百个。

Is there a way to pass a list of strings List<string> to the stored proc??? Or is there a better way to do this?

是否有方法将字符串列表传递给存储的proc??或者有更好的方法吗?

Many thanks, Brett

许多谢谢,布雷特

7 个解决方案

#1

143

If you're using SQL Server 2008, there's a new featured called a User Defined Table Type. Here is an example of how to use it:

如果您正在使用SQL Server 2008，有一个新特性叫做用户定义的表类型。这里有一个如何使用它的例子:

Create your User Defined Table Type:

创建用户定义的表类型:

CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

Next you need to use it properly in your stored procedure:

接下来，您需要在存储过程中正确地使用它:

CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @list l;
END

Finally here's some sql to use it in c#:

最后，这里有一些sql在c#中使用:

using (var con = new SqlConnection(connstring))
{
    con.Open();

    using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
    {
        using (var table = new DataTable()) {
          table.Columns.Add("Item", typeof(string));

          for (int i = 0; i < 10; i++)
            table.Rows.Add("Item " + i.ToString());

          var pList = new SqlParameter("@list", SqlDbType.Structured);
          pList.TypeName = "dbo.StringList";
          pList.Value = table;

          cmd.Parameters.Add(pList);

          using (var dr = cmd.ExecuteReader())
          {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
          }
         }
    }
}

#2

The typical pattern in this situation is to pass the elements in a comma delimited list, and then in SQL split that out into a table you can use. Most people usually create a specified function for doing this like:

这种情况下的典型模式是将元素以逗号分隔的列表传递，然后在SQL中将其拆分为可以使用的表。大多数人通常为这样做创建一个指定的函数:

 INSERT INTO <SomeTempTable>
 SELECT item FROM dbo.SplitCommaString(@myParameter)

And then you can use it in other queries.

然后你可以在其他查询中使用它。

#3

No, arrays/lists can't be passed to SQL Server directly.

不，数组/列表不能直接传递到SQL Server。

The following options are available:

以下可供选择:

Passing a comma-delimited list and then having a function in SQL split the list. The comma delimited list will most likely be passed as an Nvarchar()
通过一个逗号分隔的列表，然后在SQL中有一个函数拆分列表。逗号分隔的列表很可能作为Nvarchar()传递
Pass xml and have a function in SQL Server parse the XML for each value in the list
传递xml并让SQL Server中的函数解析列表中的每个值的xml
Use the new defined User Defined table type (SQL 2008)
使用新定义的用户定义表类型(SQL 2008)
Dynamically build the SQL and pass in the raw list as "1,2,3,4" and build the SQL statement. This is prone to SQL injection attacks, but it will work.
动态构建SQL并将原始列表传递为“1,2,3,4”，并构建SQL语句。这很容易受到SQL注入攻击，但它会起作用。

#4

Yep, make Stored proc parameter as VARCHAR(...) And then pass comma separated values to a stored procedure.

是的，将存储的proc参数设置为VARCHAR(…)然后将逗号分隔的值传递给存储过程。

If you are using Sql Server 2008 you can leverage TVP (Table Value Parameters): SQL 2008 TVP and LINQ if structure of QueryTable more complex than array of strings otherwise it would be an overkill because requires table type to be created within SQl Server

如果您正在使用Sql Server 2008，您可以利用TVP(表值参数)

#5

If you prefer splitting a CSV list in SQL, there's a different way to do it using Common Table Expressions (CTEs). See Efficient way to string split using CTE.

如果您喜欢在SQL中拆分CSV列表，那么可以使用通用表表达式(Common Table Expressions, cte)来实现。查看使用CTE进行字符串分割的有效方法。

#6

The only way I'm aware of is building CSV list and then passing it as string. Then, on SP side, just split it and do whatever you need.

我知道的唯一方法是构建CSV列表，然后将其作为字符串传递。然后，在SP方，只要分开它，做任何你需要的。

#7

Make a datatable with one column instead of List and add strings to the table. You can pass this datatable as structured type and perform another join with title field of your table.

用一列代替列表创建一个datatable，并向表中添加字符串。您可以将这个datatable作为结构化类型传递，并执行与表的title字段的另一个连接。

#1

143