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
– GSerg, Commented May 15, 2020 at 18:03
i can use an auto increment for this. Is there any other way? — DmO
– DmO, Commented May 15, 2020 at 18:04
Possible duplicate of Insert List(Of Integer) into SQL Server Table — GSerg
– GSerg, Commented May 15, 2020 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
– Flydog57, Commented May 15, 2020 at 18:44

oRole · Accepted Answer · 2020-05-15 18:19:26Z

1

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

answered May 15, 2020 at 18:19

oRole

1,3461 gold badge8 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DmO Over a year ago

Thank you oRole. Should be easier to convert it into dataTable and then to make a bulk Insert?

oRole Over a year ago

Hard to say if it's easier, but have a look here. There are multiple ways to do what you want to achieve.

Collectives™ on Stack Overflow

SqlCommand Text multiple queries

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related