如何在每次迭代中重用SqlCommand参数？ [英] How to reuse SqlCommand parameter through every iteration?

问题描述

我想为我的数据库实现一个简单的删除按钮。事件方法如下所示：

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;
    }
}

但我收到此消息：

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

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

有什么方法可以重用此变量？

Is there any way to reuse this variable?

推荐答案

Parameters.AddWithValue 将新参数添加到命令中。由于您是在同一个名称的循环中执行此操作，因此会出现异常变量名称必须唯一 。

Parameters.AddWithValue adds 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".

因此，您只需要一个参数，将其添加到循环之前，并仅更改其值即可。

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;
    // ...
}

另一种方法是使用 command.Parameters。首先清除（）; 。然后，您也可以在循环中添加参数，而无需两次创建相同的参数。

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.

这篇关于如何在每次迭代中重用SqlCommand参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！