sql server - What’s the best way to bulk database inserts from c#?

Question

How do I/what’s the best way to do bulk database inserts?

In C#, I am iterating over a collection and calling an insert stored procedure for each item in the collection.

How do I send all the data in one database call?

E.g. say I have a person list (List<Person>) containing 10 items. I am currently calling the InsertPerson stored proc 10 times. I would like to reduce this to 1 call.

I am using MS SQL Server 2005.

Answer 1

CsharperGuyInLondon, here's a simple example of SqlBulkCopy code:

using System.Data.SqlClient;

DataTable table = new DataTable("States");
// construct DataTable
table.Columns.Add(new DataColumn("id_state", typeof(int))); 
table.Columns.Add(new DataColumn("state_name", typeof(string)));

// note: if "id_state" is defined as an identity column in your DB,
// row values for that column will be ignored during the bulk copy
table.Rows.Add("1", "Atlanta");
table.Rows.Add("2", "Chicago");
table.Rows.Add("3", "Springfield");

using(SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
  bulkCopy.BulkCopyTimeout = 600; // in seconds
  bulkCopy.DestinationTableName = "state";
  bulkCopy.WriteToServer(table);
}