SqlCommand 中的字符串列表通过 C# 中的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/83471/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
String list in SqlCommand through Parameters in C#
提问by graffic
Working with a SqlCommand in C# I've created a query that contains a IN (list...) part in the where clause. Instead of looping through my string list generating the list I need for the query (dangerous if you think in sqlInjection). I thought that I could create a parameter like:
在 C# 中使用 SqlCommand 我创建了一个查询，其中在 where 子句中包含一个 IN (list...) 部分。而不是遍历我的字符串列表生成查询所需的列表（如果您认为 sqlInjection 是危险的）。我以为我可以创建一个参数，如：
SELECT blahblahblah WHERE blahblahblah IN @LISTOFWORDS
Then in the code I try to add a parameter like this:
然后在代码中我尝试添加这样的参数：
DataTable dt = new DataTable();
dt.Columns.Add("word", typeof(string));
foreach (String word in listOfWords)
{
dt.Rows.Add(word);
}
comm.Parameters.Add("LISTOFWORDS", System.Data.SqlDbType.Structured).Value = dt;
But this doesn't work.
但这不起作用。
Questions:
问题：
- Am I trying something impossible?
- Did I took the wrong approach?
- Do I have mistakes in this approach?
- 我在尝试不可能的事情吗？
- 我采取了错误的方法吗？
- 我在这种方法中有错误吗？
Thanks for your time :)
谢谢你的时间 ：）
采纳答案by Mark Lindell
What you are trying to do is possible but not using your current approach. This is a very common problem with all possible solutions prior to SQL Server 2008 having trade offs related to performance, security and memory usage.
您尝试做的事情是可能的，但没有使用您当前的方法。这是 SQL Server 2008 之前的所有可能解决方案都具有与性能、安全性和内存使用相关的权衡的一个非常常见的问题。
This link shows some approaches for SQL Server 2000/2005
此链接显示了 SQL Server 2000/2005 的一些方法
SQL Server 2008 supports passing a table value parameter.
I hope this helps.
我希望这有帮助。
回答by Joel Coehoorn
You want to think about where that list comes from. Generally that information is in the database somewhere. For example, instead of this:
您想考虑该列表的来源。通常，该信息位于数据库中的某处。例如，而不是这样：
SELECT * FROM [Table] WHERE ID IN (1,2,3)
You could use a subquery like this:
您可以使用这样的子查询：
SELECT * FROM [Table] WHERE ID IN ( SELECT TableID FROM [OtherTable] WHERE OtherTableID= @OtherTableID )
回答by Vyrotek
I would recommend setting the parameter as a comma delimited string of values and use a Split function in SQL to turn that into a single column table of values and then you can use the IN feature.
我建议将参数设置为逗号分隔的值字符串，并在 SQL 中使用 Split 函数将其转换为值的单列表，然后您可以使用 IN 功能。
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648- Split Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648- 拆分函数
回答by Judah Gabriel Himango
If I understand right, you're trying to pass a list as a SQL parameter.
如果我理解正确，您正在尝试将列表作为 SQL 参数传递。
Some folks have attempted this before with limited success:
有些人以前尝试过，但成功有限：
Passing Arrays to Stored Procedures
Passing Array of Values to SQL Server without String Manipulation
在不进行字符串操作的情况下将值数组传递给 SQL Server
Using MS SQL 2005's XML capabilities to pass a list of values to a command
回答by Dana
If you want to pass the list as a string in a parameter, you could just build the query dynamically.
如果您想在参数中将列表作为字符串传递，您可以动态构建查询。
DECLARE @query varchar(500) SET @query = 'SELECT blah blah WHERE blahblah in (' + @list + ')' EXECUTE(@query)
DECLARE @query varchar(500) SET @query = 'SELECT blah blah WHERE blahblah in (' + @list + ')' EXECUTE(@query)
回答by Arno
I used to have the same problem, I think there is now way to do this directly over the ADO.NET API.
我曾经遇到过同样的问题，我认为现在可以通过 ADO.NET API 直接执行此操作。
You might consider inserting the words into a temptable (plus a queryid or something) and then refering to that temptable from the query. Or dynamically creating the query string and avoid sql injection by other measures (e.g. regex checks).
您可能会考虑将单词插入到临时表中（加上查询 ID 或其他内容），然后从查询中引用该临时表。或者动态创建查询字符串并通过其他措施（例如正则表达式检查）避免 sql 注入。
回答by Dani
- Am I trying something impossible?
- 我在尝试不可能的事情吗？
No, it isn't impossible.
不，这不是不可能的。
- Did I took the wrong approach?
- 我采取了错误的方法吗？
Your approach is not working (at least in .net 2)
您的方法不起作用（至少在 .net 2 中）
- Do I have mistakes in this approach?
- 我在这种方法中有错误吗？
I would try "Joel Coehoorn" solution (2nd answers) if it is possible. Otherwise, another option is to send a "string" parameter with all values delimited by an separator. Write a dynamic query (build it based on values from string) and execute it using "exec".
如果可能的话，我会尝试“Joel Coehoorn”解决方案（第二个答案）。否则，另一种选择是发送一个“字符串”参数，其中所有值都由分隔符分隔。编写一个动态查询（基于字符串中的值构建它）并使用“exec”执行它。
Another solution will be o build the query directly from code. Somthing like this:
另一个解决方案是直接从代码构建查询。像这样的东西：
StringBuilder sb = new StringBuilder();
for (int i=0; i< listOfWords.Count; i++)
{
sb.AppendFormat("p{0},",i);
comm.Parameters.AddWithValue("p"+i.ToString(), listOfWords[i]);
}
comm.CommandText = string.Format(""SELECT blahblahblah WHERE blahblahblah IN ({0})",
sb.ToString().TrimEnd(','));
The command should look like:
该命令应如下所示：
SELECT blah WHERE blah IN (p0,p1,p2,p3...)...p0='aaa',p1='bbb'
In MsSql2005, "IN" is working only with 256 values.
在 MsSql2005 中，“IN”仅适用于 256 个值。
回答by aarona
This is an old question but I've come up with an elegant solution for this that I love to reuse and I think everyone else will find it useful.
这是一个老问题，但我为此提出了一个优雅的解决方案，我喜欢重用它，我认为其他人都会发现它很有用。
First of all you need to create a FUNCTIONin SqlServer that takes a delimited input and returns a table with the items split into records.
首先，您需要FUNCTION在 SqlServer 中创建一个接受分隔输入并返回一个表，其中项目被拆分为记录。
Here is the following code for this:
这是以下代码：
ALTER FUNCTION [dbo].[Split]
(
@RowData nvarchar(max),
@SplitOn nvarchar(5) = ','
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
You can now do something like this:
你现在可以做这样的事情：
Select Id, Data from dbo.Split('123,234,345,456',',')
And fear not, this can't be susceptible to Sql injection attacks.
不用担心，这不会受到 Sql 注入攻击的影响。
Next write a stored procedure that takes your comma delimited data and then you can write a sql statement that uses this Split function:
接下来编写一个使用逗号分隔数据的存储过程，然后您可以编写使用此 Split 函数的 sql 语句：
CREATE PROCEDURE [dbo].[findDuplicates]
@ids nvarchar(max)
as
begin
select ID
from SomeTable with (nolock)
where ID in (select Data from dbo.Split(@ids,','))
end
Now you can write a C# wrapper around it:
现在您可以围绕它编写一个 C# 包装器：
public void SomeFunction(List<int> ids)
{
var idsAsDelimitedString = string.Join(",", ids.Select(id => id.ToString()).ToArray());
// ... or however you make your connection
var con = GetConnection();
try
{
con.Open();
var cmd = new SqlCommand("findDuplicates", con);
cmd.Parameters.Add(new SqlParameter("@ids", idsAsDelimitedString));
var reader = cmd.ExecuteReader();
// .... do something here.
}
catch (Exception)
{
// catch an exception?
}
finally
{
con.Close();
}
}
