如何将数组传递到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(用户定义类型)重新用于许多存储过程。与该示例一样,通常的要求是传递ID指针列表,函数名称描述那些Id应该表示的上下文,类型名称应该是通用的。