CODEWITHSUNDEEP
c#sql-server
DmO
DmO

Reputation: 399

SqlCommand Text multiple queries

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?

Upvotes: 0

Views: 201

Answers (1)

oRole
oRole

Reputation: 1346

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

Upvotes: 1

Related Questions