通过列表<字符串>进入 SQL 参数 [英] Pass List&lt;string&gt; Into SQL Parameter

问题描述

程序是用 C# 编写的，我试图传递一个 List 作为参数.

The program is in C#, and I'm trying to pass a List<string> as a parameter.

List<string> names = new List<string>{"john", "brian", "robert"};

在普通 SQL 中，查询将如下所示:

In plain SQL, the query will look like this:

DELETE FROM Students
WHERE name = 'john' or name = 'brian' or name = 'robert'

在 C# 代码中运行 SQL 命令时，我知道正确的做法是使用参数而不是将所有内容连接成一个巨大的字符串.

When running a SQL command in C# code, I know that the proper way of doing it is to use parameters instead of concatenating everything into one giant string.

command.CommmandText = "DELETE FROM Students WHERE name = @name";
command.Parameters.Add(new MySqlParameter("@name", String.Format("'{0}'", String.Join("' or name = '", names)));
command.NonQuery();

以上方法无效.它没有抛出任何错误/异常，只是没有按照我想要的方式工作.

The above method did not work. It didn't throw any error/exception, it just simply didn't work the way I want it to.

我应该怎么做?

我想过遍历 List 并只对每个名称执行.

I thought about looping through the List<string> and just execute on every single name.

foreach(string name in names)
{
    command.CommmandText = "DELETE FROM Students WHERE name = @name";
    command.Parameters.Add(new MySqlParameter("@name", name));
    command.NonQuery();
    command.Parameters.Clear();
}

但这将需要很长时间，因为实际的List 非常大.我想尽量少执行.

But this will take a long time as the actual List<string> is quite large. I want to try execute at little as possible.

谢谢！

推荐答案

您可以在 IN 子句中参数化列表中的每个值:

You can parameterize each value in the list in an IN clause:

List<string> names = new List<string> { "john", "brian", "robert" };
string commandText = "DELETE FROM Students WHERE name IN ({0})";
string[] paramNames = names.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(",", paramNames);
using (var command = new SqlCommand(string.Format(commandText, inClause), con))
{
    for (int i = 0; i < paramNames.Length; i++)
    {
        command.Parameters.AddWithValue(paramNames[i], names[i]);
    }
    int deleted = command.ExecuteNonQuery();
} 

类似于:

"... WHERE Name IN (@tag0,@tag1,@tag2)"

command.Parameters["@tag0"].Value = "john";
command.Parameters["@tag1"].Value = "brian";
command.Parameters["@tag2"].Value = "robert";

改编自:https://stackoverflow.com/a/337792/284240

这篇关于通过列表<字符串>进入 SQL 参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！