c# - 如何将数组传递给 SQL Server 存储过程

Question

如何将数组传递给 SQL Server 存储过程？

例如，我有一个员工列表。我想将此列表用作表格并将其与另一个表格连接。但是员工列表应该作为参数从 C# 传递。

Answer 1

SQL Server 2016(或更新版本)

您可以传入分隔列表或 JSON 并使用 STRING_SPLIT() 或 OPENJSON()。

STRING_SPLIT():

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM STRING_SPLIT(@List, ',');
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

OPENJSON():

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM OPENJSON(CONCAT('["',
    REPLACE(STRING_ESCAPE(@List, 'JSON'), 
    ',', '","'), '"]')) AS j;
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

我在这里写了更多相关内容:

• Handling an unknown number of parameters in SQL Server
• Ordered String Splitting in SQL Server with OPENJSON

SQL Server 2008(或更新版本)

首先，在您的数据库中，创建以下两个对象:

CREATE TYPE dbo.IDList
AS TABLE
(
  ID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY
AS
BEGIN
  SET NOCOUNT ON;
  
  SELECT ID FROM @List; 
END
GO

现在在您的 C# 代码中:

// Obtain your list of ids to send, this is just an example call to a helper utility function
int[] employeeIds = GetEmployeeIds();

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));

// populate DataTable from your List here
foreach(var id in employeeIds)
    tvp.Rows.Add(id);

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    // these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
    tvparam.SqlDbType = SqlDbType.Structured;
    tvparam.TypeName = "dbo.IDList";
    // execute query, consume results, etc. here
}

SQL 服务器 2005

如果您使用的是 SQL Server 2005，我仍然建议使用 XML 上的拆分函数。首先，创建一个函数:

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );
GO

现在你的存储过程可以是:

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;
  
  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); 
END
GO

在您的 C# 代码中，您只需将列表作为 '1,2,3,12'...

---

我发现通过表值参数传递的方法简化了使用它的解决方案的可维护性，并且与其他实现(包括 XML 和字符串拆分)相比通常具有更高的性能。

输入是明确定义的(没有人必须猜测分隔符是逗号还是分号)，并且我们不依赖于其他处理函数，如果不检查存储过程的代码就不明显。

与涉及用户定义的 XML 模式而不是 UDT 的解决方案相比，这涉及相似数量的步骤，但根据我的经验，代码的管理、维护和阅读要简单得多。

In many solutions you may only need one or a few of these UDTs (User defined Types) that you re-use for many stored procedures. As with this example, the common requirement is to pass through a list of ID pointers, the function name describes what context those Ids should represent, the type name should be generic.