How to insert multiple rows into an database in C#/ADO.NET?

Question

I've got a DataTable which has a number of records.

I've tried the following code for iterating through the DataTable and inserting into the database, but I am receiving an error message saying that the parameters have not been declared:

       using (SqlCommand command = new SqlCommand(("My insert statement"), connection))
                    {

                        SqlParameter param1 = new SqlParameter();
                        SqlParameter param2 = new SqlParameter();

                        param1.ParameterName = "@ProductID";
                        param2.ParameterName = "@ID";

                        foreach (DataRow row in table.Rows)
                        {
                            param1.Value = row[0].ToString();
                            param2.Value = row[1].ToString();

                            command.ExecuteNonQuery();
                        }

                    }

Any ideas? Please do not suggest stored procedures - I'd like to do this through this method or something similar.

I don't see you passing the parameters to the command object in this code. — svanryckeghem
– svanryckeghem, Commented Nov 12, 2011 at 19:02

Kevin LaBranche · Accepted Answer · 2011-11-12 19:09:30Z

1

You need to add your parameters to the command.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Caution though

The way you have your code if you put the add in the foreach your first execute will work and then the next will fail with an error message stating that you have a variable named @ProductID / @ID already.

You need to clear the parameters each time.

See this for an example.

edited Nov 12, 2011 at 19:09

answered Nov 12, 2011 at 19:04

Kevin LaBranche

21.1k5 gold badges55 silver badges77 bronze badges

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

1 Comment

Dot NET Over a year ago

Thanks - this was my problem :)

ptfaulkner · Accepted Answer · 2011-11-12 19:17:31Z

1

Check out SqlBulkCopy, it will allow you to pass in your Datatable straight to the database. Link

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
    bulkCopy.DestinationTableName = "dbo.TableYouWantToInsertTheRowsTo";

    try
    {
         // Write from the source to the destination.
         bulkCopy.WriteToServer(newProductsDataTable);
    }
    catch (Exception ex)
    {
         Console.WriteLine(ex.Message);
    }
}

edited Nov 12, 2011 at 19:17

answered Nov 12, 2011 at 19:04

ptfaulkner

7124 silver badges14 bronze badges

Comments

hemant · Accepted Answer · 2011-11-22 09:38:45Z

0

If you are using SQL2008 than entire List of object/datatable can be passed as parameter to store procedure. In this case you need to declare same class in sql also.

answered Nov 22, 2011 at 9:38

hemant

2,4351 gold badge13 silver badges5 bronze badges

Collectives™ on Stack Overflow

How to insert multiple rows into an database in C#/ADO.NET?

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related