如何在每次迭代中重用 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 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.Clear(); 首先.然后您也可以在循环中添加参数，而无需创建两次相同的参数.

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屋！