将多个值分配给单个ADO.Net SqlClient参数 [英] Having multiple values assigned to a single ADO.Net SqlClient parameter
问题描述
我希望运行与此类似的SQL select语句
I wish to run an SQL select statement similar to this
SELECT * FROM CatalogueItems WHERE id IN (1,10,15,20);
使用ADO.Net SqlClient样式@name参数。我试过使用存储的SQL字符串
using ADO.Net SqlClient style @name parameters. I've tried using a stored SQL strings
SELECT * FROM CatalogueItems WHERE id IN (@Ids)
,然后在我的C#代码中
and then in my C# code
SqliteCommand command;
//...
//returns 0 results
command.Parameters.Add("@Ids", null).Value = "1,10,15,20";
//returns 0 results
command.Parameters.Add("@Ids", DbType.String).Value = "1,10,15,20";
//returns 1 or more results
command.Parameters.Add("@Ids", null).Value = "1";
返回空结果集,但单个字符串参数返回结果。
returns an empty result set, yet the individual string parameters return results.
此查询支持吗?
谢谢。
推荐答案
您不能这样做。 SQL-Server参数必须是单个值。
但是您可以在此处进行构建参数列表。
You can't do that. A SQL-Server parameter must be a single value. But you could have a look here to build the parameter list dynamically.
编辑:如果使用SQl-Server> = 2008,则可以使用表值参数,如此答案。它对我来说是新的,但是不幸的是我在2005年。
if using SQl-Server >=2008 you could use Table-valued parameters as suggested in this answer. It's new to me, but i'm on 2005 unfortunately.
另一个选择是创建一个用户定义函数,该函数从您的csv-string返回一个表,如下所示:
Another option would be to create a user-defined-function that returns a table from your csv-string as this:
CREATE FUNCTION [dbo].[Split]
(
@ItemList NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(MAX)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
然后您可以使用此函数拆分ID并将其与结果集结合。
例如:
Then you could use this function to split your ID's and join it with your result set. Such as this:
SELECT CatalogueItems .*
FROM CatalogueItems INNER JOIN
dbo.Split(@Ids, ',') AS IdList ON CatalogueItems.ID = IdList.Item
一个ID的字符串参数就足够了。
In this case one string-parameter for all ID's is sufficient.
这篇关于将多个值分配给单个ADO.Net SqlClient参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!