vb.net 使用“IN”命令将数组作为要在 SQL 查询中使用的参数传递
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18459776/
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
passing an Array as a Parameter to be used in a SQL Query using the "IN" Command
提问by Law
Good Afternoon to All,
大家下午好，
I have a question concerning on SQL Queries. is it possible to use an array as a parameter to a query using the "IN" command?
我有一个关于 SQL 查询的问题。是否可以使用“IN”命令将数组用作查询的参数？
for example,
例如，
int x = {2,3,4,5}
整数 x = {2,3,4,5}
UPDATE 'table_name' set 'field' = data WHERE field_ID IN (x)
UPDATE 'table_name' set 'field' = data WHERE field_ID IN (x)
the reason I am asking this is to avoid an iterative SQL Statement when I have to update data in a database. I also thought of using a for each statement in for the UPDATE Query but I don't know if it will affect the performance of the query if it will slow down the system if ever 100+ records are updated.
我问这个的原因是为了在我必须更新数据库中的数据时避免迭代 SQL 语句。我还想过在 UPDATE Query 中使用 for each 语句，但我不知道它是否会影响查询的性能，如果它会在更新 100 多条记录时减慢系统速度。
I am using VB.Net btw. My Database is MySQL Workbench.
我正在使用 VB.Net 顺便说一句。我的数据库是 MySQL Workbench。
采纳答案by Law
I have gotten the answer. I just simply need to convert each elements to a String then concatenate it with a "," for each element. so the parameter that i will pass will be a string.
我得到了答案。我只需要将每个元素转换为字符串，然后为每个元素将它与“,”连接起来。所以我将传递的参数将是一个字符串。
ANSWER: int x = {2,3,4,5} will become string x = "2,3,4,5"
答案： int x = {2,3,4,5} 将变成字符串 x = "2,3,4,5"
My Query string will become "UPDATE tablename SET field=value WHERE ID IN("&x&")"
我的查询字符串将变为“UPDATE tablename SET field=value WHERE ID IN("&x&")”
Thank you to all who helped.
感谢所有帮助过的人。
回答by the_lotus
If you have the query in a variable (not a stored procedure) and you don't have a huge amount of ids, you could built your own IN. I haven't tested the speed of this approach.
如果您在变量（不是存储过程）中有查询并且没有大量的 id，则可以构建自己的 IN。我还没有测试过这种方法的速度。
This code won't compile, it's just to give you an idea.
这段代码不会编译，它只是给你一个想法。
query = "SELECT * FROM table WHERE col IN ("
For t = 0 TO x.Length-1
If t > 0 Then query &= ","
query &= "@var" & t
Next
query &= ")"
...
For t = 0 TO x.Length-1
cmd.Parameters.Add("@var" & t, SqlDbType.Int).Value = x(t)
Next
回答by Rex
i am not familiar with mySQL, but when dealing with MSSQL, I normally have a split function in DB so that I can use it to split concatenated integer values as a table, at VB side, something like:
我不熟悉 mySQL，但是在处理 MSSQL 时，我通常在 DB 中有一个拆分函数，以便我可以使用它在 VB 端将连接的整数值拆分为一个表，例如：
Dim myIds = {1, 2, 3, 4}
Dim sql = <sql>
SELECT m.* FROM dbo.tblMyData m
INNER JOIN dbo.fncSplitIntegerValues(@Ids, ',') t ON t.id = m.Id
</sql>.Value
Using con As New SqlConnection("My connection string..."),
cmd As New SqlCommand(sql, con)
cmd.Parameters.Add("@Ids", SqlDbType.VarChar).Value =
myIds.Select(Function(m) m.ToString).Aggregate(Function(m, n) m & "," & n)
con.Open()
Dim rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While rdr.Read()
Console.WriteLine(rdr.GetValue(0))
' do something else...
End While
End Using
dbo.fncSplitIntegerValues is a db function to split concatenated integer varchar into integer Id with given separator.
dbo.fncSplitIntegerValues 是一个 db 函数，用于将连接的整数 varchar 拆分为具有给定分隔符的整数 Id。
it's important not to use plain sql, instead, use sql parameters.
重要的是不要使用普通的 sql，而是使用 sql 参数。
Note: above sample is not tested...
注：以上样品未经测试...
