C# 如何在每次迭代中重用 SqlCommand 参数？

How to reuse SqlCommand parameter through every iteration?

提问by miller

I want to implement a simple delete button for my database. The event method looks something like this:

我想为我的数据库实现一个简单的删除按钮。事件方法看起来像这样：

private void btnDeleteUser_Click(object sender, EventArgs e)
{
    if (MessageBox.Show("Are you sure?", "delete users",MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
    {
        command = new SqlCommand();
        try
        {
            User.connection.Open();
            command.Connection = User.connection;
            command.CommandText = "DELETE FROM tbl_Users WHERE userID = @id";
            int flag;
            foreach (DataGridViewRow row in dgvUsers.SelectedRows)
            {
                int selectedIndex = row.Index;
                int rowUserID = int.Parse(dgvUsers[0,selectedIndex].Value.ToString());

                command.Parameters.AddWithValue("@id", rowUserID);
                flag = command.ExecuteNonQuery();
                if (flag == 1) { MessageBox.Show("Success!"); }

                dgvUsers.Rows.Remove(row);
            }
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        finally
        {
            if (ConnectionState.Open.Equals(User.connection.State)) 
               User.connection.Close();
        }
    }
    else
    {
        return;
    }
}

but I get this message:

但我收到这条消息：

A variable @id has been declared. Variable names must be unique within a query batch or stored procedure.

已声明变量@id。变量名在查询批处理或存储过程中必须是唯一的。

Is there any way to reuse this variable?

有没有办法重用这个变量？

采纳答案by Tim Schmelter

Parameters.AddWithValueadds a new Parameter to the command. Since you're doing that in a loop with the same name, you're getting the exception "Variable names must be unique".

Parameters.AddWithValue向命令添加一个新参数。由于您在具有相同名称的循环中执行此操作，因此您会收到异常"Variable names must be unique"。

So you only need one parameter, add it before the loop and change only it's value in the loop.

所以你只需要一个参数，在循环之前添加它并且只改变它在循环中的值。

command.CommandText = "DELETE FROM tbl_Users WHERE userID = @id";
command.Parameters.Add("@id", SqlDbType.Int);
int flag;
foreach (DataGridViewRow row in dgvUsers.SelectedRows)
{
    int selectedIndex = row.Index;
    int rowUserID = int.Parse(dgvUsers[0,selectedIndex].Value.ToString());
    command.Parameters["@id"].Value = rowUserID;
    // ...
}

Another way is to use command.Parameters.Clear();first. Then you can also add the parameter(s) in the loop without creating the same parameter twice.

另一种方法是先使用command.Parameters.Clear();。然后，您还可以在循环中添加参数，而无需两次创建相同的参数。

回答by Shyju

Error is because you are adding the same parameter again and again in each iteration of the loop.

错误是因为您在循环的每次迭代中一次又一次地添加相同的参数。

I would move that code to a seperate method so that i can call it from multiple places as needed.

我会将该代码移动到一个单独的方法，以便我可以根据需要从多个地方调用它。

public bool DeleteUser(int userId)
{
    string connString = "your connectionstring";
    try
    {
      using (var conn = new SqlConnection(connString))
      {
        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "DELETE FROM tbl_Users WHERE userID = @id";
            cmd.Parameters.AddWithValue("@id", userId);
            conn.Open();
            cmd.ExecuteNonQuery();
            return true;
        }
      }
    }
    catch(Exception ex)
    {
      //Log the Error here for Debugging
      return false;
    }

}

Then call it like this

然后这样调用

 foreach (DataGridViewRow row in dgvUsers.SelectedRows)
 {
   int selectedIndex = row.Index;
   if(dgvUsers[0,selectedIndex]!=null)
   {
     int rowUserID = int.Parse(dgvUsers[0,selectedIndex].Value.ToString());
     var result=DeleteUser(rowUserID)
   }
   else
   {
      //Not able to get the ID. Show error message to user
   } 
 }

回答by Cashley

Rather than:

而不是：

command.Parameters.AddWithValue("@id", rowUserID);

Use something like:

使用类似的东西：

System.Data.SqlClient.SqlParameter p = new System.Data.SqlClient.SqlParameter();

Outside the foreach, and just set manually inside the loop:

在 foreach 之外，只需在循环内手动设置：

p.ParameterName = "@ID";
p.Value = rowUserID;

回答by R.Garcia

I would use this:

我会用这个：

public static class DbExtensions
{
    public static void AddParameter(SQLiteCommand command, string name, DbType type, object value)
    {
        var param = new SQLiteParameter(name, type);
        param.Value = value;
        command.Parameters.Add(param);
    }
}

Then, call this:

然后，调用这个：

DbExtensions.AddParameter(command, "@" + fieldOfSearch[i], DbType.String, value[i]);