What’s the best way to bulk database inserts from c#

Question

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

From a c# perspective 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) containing 10 items, I am currently calling the InsertPerson store proc 10 times. I would like to reduce this to 1 call.

/I am using MS SQL Server 2005.

Thanks a lot.

Answer 1

Dump your data to a pipe delimited (or something else if your data has pipes in it) text file and use Bulk Insert.

Answer 2

You can build a BLOB (image) and send it as a parameter to a stored procedure. Inside the stored procedure, you can fetch all the items using substring().

Answer 3

The .NET SqlBulkCopy class works quite well.

Answer 4

You could update with an Xml document, Sql 2005 works very well with them. One node per row, but just one parameter for Xml.

Answer 5

Well, 10 items isn't what I call bulk, but for larger sets, SqlBulkCopy is your friend. All you need to do is feed it either a DataTable or an IDataReader (my preferred option, 'cos I like streaming APIs). I did something similar here (you can ignore the xml side - just subclass the SimpleDataReader).

Answer 6

Here's a good example of SqlBulkCopy in action:

http://blogs.msdn.com/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx

Answer 7

Create a XML document that contains all the items to be inserted. Then inside of a stored procedure, use the TSQL xml support (OPENXML) to read all the data from the XML document and insert it into your tables with hopefully one insert statement for each table.

However if you are only inserting data into a single table and don’t need any database side logic, why not use SqlBulkCopy?

Answer 8

Definitely check this question. The topvoted answer (no, it's not mine:p) presents a very elegant solution to this exact problem.

Answer 9

I construct the list as an xml string and pass it to the stored proc. In SQL 2005, it has enhanced xml functionalities to parse the xml and do a bulk insert.

check this post: Passing lists to SQL Server 2005 with XML Parameters

Answer 10

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