Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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 !

Print | posted on Tuesday, May 22, 2007 2:40 PM | Filed Under [ .NET (C# / VB) Links Imports/Exports ]

Feedback

Gravatar

# 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?
5/23/2007 5:11 AM | Ian Ringrose
Gravatar

# 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...
5/23/2007 10:07 AM | Adam Machanic
Gravatar

# re: Using SQLBulkCopy to persist any .NET Object

You're welcome, Jeff!
5/31/2007 10:54 PM | Jesse
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET