Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Using SQLBulkCopy to copy any .NET Object to a SQL Server Table

In my SQLBulkCopy article, I mentioned that you can quickly copy data from anything that implements IDataReader to a SQL Server table using SQLBulkCopy (new in .NET 2.0).  In this SQLTeam forum post, Jesse Hersch (jezemine) tells us that SQLBulkCopy only actually uses 3 methods of the interface.  This means that it is very easy to quickly create your own custom class which implements this interface to bulk copy pretty much any type of object collection or array to a SQL Server table.  He even provides code for a simple abstract SqlBulkCopyReader class that you can inherit from to implement only the necessary parts of the interface.

So, if you need to persist large amounts of data from your .net applications directly to SQL Server, and that data is not stored in DataTables, be sure to consider this option.  Thanks, Jesse !

Legacy Comments


Ian Ringrose
2007-05-23
SqlBulkCopy timesout when copying specifc numbers of rows
We have a program that uses SqlBulkCopy, it fails with some numbers of rows, e.g. it does not work with 191 rows, but it does work with 192 rows! (and 190 rows). There is a report about it on the connect website, - http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=275275

Any we doing anything wrong in our code?

Adam Machanic
2007-05-23
re: Using SQLBulkCopy to persist any .NET Object
Ian,

I tried to validate this and was unable to (see the validation notes on the ticket). What version of SQL Server is this? And what are you doing differently than what I did? Are you using a table lock? Does your table have a key? There are a lot of potential variables that were not included in the ticket. This is probably resulting from a very specific combination of things...

Jesse
2007-05-31
re: Using SQLBulkCopy to persist any .NET Object
You're welcome, Jeff!