C# SQL Server - 将列表传递给存储过程 [英] C# SQL Server - Passing a list to a stored procedure

问题描述

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

using (SqlConnection conn = new SqlConnection(connstring)){conn.Open();使用 (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;//执行查询SqlDataReader QueryReader = cmd.ExecuteReader();

我的存储过程相当标准，但与 QueryTable 连接(因此需要使用存储过程).

现在:我想向参数集添加一个字符串列表，List.例如，我的存储过程查询是这样的:

SELECT 功能从表 1 t1内部联接@QueryTable t2 ON t1.fid = t2.fidWHERE title IN 

然而，字符串列表是动态的，并且有几百长.

有没有办法将字符串列表 List 传递给存储过程??? 或者有更好的方法吗?>

非常感谢，布雷特

解决方案

如果您使用的是 SQL Server 2008，则有一个称为用户定义表类型的新功能.以下是如何使用它的示例:

创建您的用户定义表类型:

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

接下来你需要在你的存储过程中正确使用它:

创建程序 [dbo].[sp_UseStringList]@list StringList 只读作为开始-- 只返回我们传入的项目SELECT l.Item FROM @list l;结尾

最后这里有一些 sql 可以在 c# 中使用它:

using (var con = new SqlConnection(connstring)){con.Open();使用 (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 = 表；cmd.Parameters.Add(pList);使用 (var dr = cmd.ExecuteReader()){而 (dr.Read())Console.WriteLine(dr["Item"].ToString());}}}}

从 SSMS 执行此操作

DECLARE @list AS StringList插入@list 值('Apple')插入@list 值('香蕉')插入@list 值('橙色')-- 或者，您可以使用 INSERT-SELECT 填充 @list插入@list从水果中选择名称EXEC sp_UseStringList @list

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

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?

Many thanks, Brett

解决方案

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 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#:

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());
          }
         }
    }
}

To execute this from SSMS

DECLARE @list AS StringList

INSERT INTO @list VALUES ('Apple')
INSERT INTO @list VALUES ('Banana')
INSERT INTO @list VALUES ('Orange')

-- Alternatively, you can populate @list with an INSERT-SELECT
INSERT INTO @list
   SELECT Name FROM Fruits

EXEC sp_UseStringList @list

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