How to reuse SqlCommand parameter through every iteration?

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:

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

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

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.


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]);