C# - Inserting multiple rows using a stored procedure

Question

I have a list of objects, this list contains about 4 million objects. there is a stored proc that takes objects attributes as params , make some lookups and insert them into tables.

what s the most efficient way to insert this 4 million objects to db?

How i do :

-- connect to sql - SQLConnection ...

foreach(var item in listofobjects)
{    
   SQLCommand sc = ...

   // assign params

   sc.ExecuteQuery();    
}

THis has been really slow.

is there a better way to do this?

this process will be a scheduled task. i will run this ever hour, so i do expect high volume data like this.

Answer 1

Take a look at the SqlBulkCopy Class

based on your comment, dump the data into a staging table then do the lookup and insert into the real table set based from a proc....it will be much faster than row by row

Answer 2

It's never going to be ideal to insert four million records from C#, but a better way to do it is to build the command text up in code so you can do it in chunks.

This is hardly bulletproof, and it doesn't illustrate how to incorporate lookups (as you've mentioned you need), but the basic idea is:

// You'd modify this to chunk it out - only testing can tell you the right
// number - perhaps 100 at a time.

for(int i=0; i < items.length; i++) {

    // e.g., 'insert dbo.Customer values(@firstName1, @lastName1)'
    string newStatement = string.Format(
        "insert dbo.Customer values(@firstName{0}, @lastName{0})", i);
    command.CommandText += newStatement;

    command.Parameters.Add("@firstName" + i, items[i].FirstName);
    command.Parameters.Add("@lastName" + i, items[i].LastName);
}
// ...
command.ExecuteNonQuery();