如何将数组传递给 SQL Server 存储过程?
例如,我有一个员工列表。我想将此列表用作表格并将其与另一个表格连接。但是员工列表应该作为 C# 的参数传递。
首先,在您的数据库中,创建以下两个对象:
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 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'…
'1,2,3,12'
我发现传递表值参数的方法简化了使用它的解决方案的可维护性,并且与包括 XML 和字符串拆分在内的其他实现相比,通常具有更高的性能。
输入是明确定义的(没有人必须猜测分隔符是逗号还是分号),并且我们不依赖于其他处理函数,这些函数在不检查存储过程的代码的情况下并不明显。
与涉及用户定义的 XML 模式而不是 UDT 的解决方案相比,这涉及相似数量的步骤,但根据我的经验,代码的管理、维护和阅读要简单得多。
在许多解决方案中,您可能只需要这些 UDT(用户定义的类型)中的一个或几个,这些 UDT(用户定义的类型)可以重复用于许多存储过程。与这个例子一样,常见的要求是传递一个 ID 指针列表,函数名称描述了这些 Id 应该代表什么上下文,类型名称应该是通用的。