Error using Sqlcommand and foreach

Question

I’m trying to go through a list of objects and persist your data in Sqlserver:

foreach (var item in pedidos)
{
   cmd.CommandText = "INSERT INTO MinhaTabela (Coluna_A, Coluna_B, Coluna_C) " +
            "VALUES (@Val_A, @Val_B, @Val_C)";

   cmd.Parameters.AddWithValue("@Val_A", item.AttributoA);
   cmd.Parameters.AddWithValue("@Val_B", item.AttributoB);
   cmd.Parameters.AddWithValue("@Val_C", item.AttributoC);
   cmd.ExecuteNonQuery();
}

I have received the following Exception:

The variable name '@Val_a' has already been declared. Variable Names must be Unique Within a query batch or stored Procedure.

How to solve?

Answer 1

If you have to add the parameter and then delete to add again because in your case it is an item interaction and you can still change the CommandText out of the foreach, you only need to know this once, example:

cmd.CommandText = "INSERT INTO MinhaTabela (Coluna_A, Coluna_B, Coluna_C) " +
            "VALUES (@Val_A, @Val_B, @Val_C)";

foreach (var item in pedidos)
{       
   cmd.Parameters.AddWithValue("@Val_A", item.AttributoA);
   cmd.Parameters.AddWithValue("@Val_B", item.AttributoB);
   cmd.Parameters.AddWithValue("@Val_C", item.AttributoC);
   cmd.ExecuteNonQuery();
   cmd.Parameters.Clear(); // adicione essa linha para limpar os parametros existentes.
}

has another way that would use the same parameter created and change its value, but, your code would have to be checking the existence or not of the created parameter, I believe it is more practical to clear the parameters and add again, but, will a example of that also:

cmd.CommandText = "INSERT INTO MinhaTabela (Coluna_A, Coluna_B, Coluna_C) " +
            "VALUES (@Val_A, @Val_B, @Val_C)";

foreach (var item in pedidos)
{
    if (command.Parameters.Contains("@Val_A"))
    {
        command.Parameters["@Val_A"].Value = item.AttributoA;
    }
    else 
    {
        command.Parameters.AddWithValue("@Val_A", item.AttributoA);
    }
    if (command.Parameters.Contains("@Val_B"))
    {
        command.Parameters["@Val_B"].Value = item.AttributoB;
    }
    else 
    {
        command.Parameters.AddWithValue("@Val_B", item.AttributoB);
    }
    if (command.Parameters.Contains("@Val_C"))
    {
        command.Parameters["@Val_C"].Value = item.AttributoC;
    }
    else 
    {
        command.Parameters.AddWithValue("@Val_C", item.AttributoC);
    }
}

_References

• Sqlcommand class
• Sqlcommand.Parameters property