C＃的SQL Server - 传递一个列表到存储过程 [英] C# SQL Server - Passing a list to a stored procedure

问题描述

我从我的C＃code调用SQL Server存储过程：

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

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).

现在我想添加一个字符串列表，列表与LT;串＆GT; ，到参数设置。例如，我的存储过程的查询是这样的：

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.

有没有一种方法来传递字符串列表℃的名单;串＆GT; 到存储过程??? 或是否有更好的办法要做到这一点？

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?

非常感谢，
布雷特

Many thanks, Brett

推荐答案

如果你使用SQL Server 2008中，有一个新的功能称为用户定义表类型。下面是如何使用它的例子：

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:

创建用户定义表类型：

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

最后，这里的一些SQL在C＃中使用它：

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

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

    using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
    {
        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());
        }
    }
}

这篇关于C＃的SQL Server - 传递一个列表到存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！