SqlCommand Text multiple queries

Question

I am using a list collection and i need to insert each item of my collection inside sql using also parameters.

Here is an example

foreach (var item in listCollection)
{
    cmd.CommandText += "Insert into WebAppOrders (id) values (@id)";
    cmd.Parameters.Add("@id", SqlDbType.Int).Value = item.id;
}
cmd.ExecuteNonQuery();

I am getting an error

The variable name '@id' has already been declared. Variable names must be unique within a query batch or stored procedure.

Is there any way which I can make this work?

Each of your parameters has to have a different name, if you want to execute the command once in the end. Or execute the command for each element, and only change the parameter value instead of adding it. — GSerg, May 15 '20 at 18:03
i can use an auto increment for this. Is there any other way? — DmO, May 15 '20 at 18:04
Possible duplicate of [Insert List(Of Integer) into SQL Server Table](https://stackoverflow.com/questions/17200163/insert-listof-integer-into-sql-server-table) — GSerg, May 15 '20 at 18:05
Dapper has a solution for this. If you are a raw ADO.NET user and you start using Dapper, you will never want to go back. — Flydog57, May 15 '20 at 18:44

score 1 · Answer 1 · answered May 15 '20 at 18:19

You can use a simple count variable, which you then append to the parameter-name @id.

A sample would look like...

Example Item-class:

public class Item
{
    public Item(int id)
    {
        this.Id = id;
    }

    public int Id { get; set; }
}

Simulation:

var listCollection = new List<Item>() { new Item(1), new Item(2), new Item(3), new Item(4) };
using (var connection = new SqlConnection("<your connectionstring>"))
using (var cmd = new SqlCommand("", connection))
{
    connection.Open();

    int i = 0; // count
    foreach (var item in listCollection)
    {
        cmd.CommandText += $"Insert into WebAppOrders (id) values (@id{i})"; // add count here
        cmd.Parameters.Add($"@id{i}", SqlDbType.Int).Value = item.Id; // ... and here
        i++;
    }

    cmd.ExecuteNonQuery();
}

Thank you oRole. Should be easier to convert it into dataTable and then to make a bulk Insert? — DmO, May 15 '20 at 18:29
Hard to say if it's easier, but have a look [here](https://stackoverflow.com/questions/5022531/best-way-to-bulk-insert-from-a-c-sharp-datatable). There are multiple ways to do what you want to achieve. — oRole, May 15 '20 at 18:39

SqlCommand Text multiple queries

1 Answers1