C# MySQL 使用循环添加多个相同参数

问题描述

更新:由于最初的问题基本上得到了回答，我已将其标记为完整.

Update: as the original question was essentially answered, I've marked this as complete.

我有一个 C# 项目，我想在其中查询数据库.SQL 查询将从表中进行 SELECT，在 WHERE 子句中，我想从 C# 中指定的预定义值列表中过滤结果.

I have a C# project in which I'd like to query a database. The SQL query will SELECT from a table, and in the WHERE clause I want to filter the results from a pre-defined list of values specified in C#.

List<string> productNames = new List<string >() { "A", "B", "C" };

foreach (name in productNames)
{
    string query = @"
        SELECT *
        FROM products
        WHERE name IN (@name);";

    // Execute query
    MySqlCommand cmd = new MySqlCommand(query, dbConn);
    cmd.Parameters.AddWithValue("name", name);

    MySqlDataReader row = cmd.ExecuteReader();
    while (row.Read())
    {
        // Process result
        // ...
    }
}

但是我收到一个错误:

已经有一个与此连接关联的打开的 DataReader必须先关闭

难道不能使用 for 循环以这种方式将参数添加到 SELECT 语句中吗?

Is it not possible then to use a for loop to add parameters this way to a SELECT statement?

推荐答案

您需要处理您的对象，以免出现异常.但是，您不需要迭代值并对列表中的每个值运行查询.试试下面的代码.它为每个值创建一个参数并将其添加到命令中以在IN (...)"子句中使用.

You need to dispose your object to not get the exception. However you don't need to iterate over values and run a query for every value in the list. Try the following code. It makes a parameter for every value and adds it to command to use in "IN (...)" clause.

using"关键字也用于处理对象.

Also "using" keywords handles disposing objects.

List<string> productsIds = new List<string>() { "23", "46", "76", "88" };
        string query = @"
            SELECT *
            FROM products
            WHERE id IN ({0});";

        // Execute query
        using (MySqlCommand cmd = new MySqlCommand(query, dbConn))
        {
            int index = 0;
            string sqlWhere = string.Empty;
            foreach (string id in productsIds)
            {
                string parameterName = "@productId" + index++;
                sqlWhere += string.IsNullOrWhiteSpace(sqlWhere) ? parameterName : ", " + parameterName;
                cmd.Parameters.AddWithValue(parameterName, id);
            }

            query = string.Format(query, sqlWhere);
            cmd.CommandText = query;
            using (MySqlDataReader row = cmd.ExecuteReader())
            {
                while (row.Read())
                {
                    // Process result
                    // ...
                }
            }
        }

这篇关于C# MySQL 使用循环添加多个相同参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！