Hello, How can i use the ADO.NET 2.0 Feature SqlBulkCopy using LLBGen Pro.

Check the following thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7352

I'm not sure if it's possible to build this into the framework or not, but I would see SqlBulkCopy as a hugely beneficial feature.

I tested with Entity Saves, Stored procs queued up in a UnitofWork object and then using SqlBulkCopy (transforming an entitycollection to a datatable), and the SqlBulkCopy was MUCH faster.

I'm not sure what it does behind the scenes, but I believe the SQL text that gets passed to the database is significantly smaller than even an SP call.

I can post some metrics later this week if anyone is interested.

Phil

Hi Phil, Thanks a lot for your post. couuld you please provide me with the sample code how you used it and also the statistics which you were mentioning. That would be of great help. gspk

gspk wrote:

Hi Phil, Thanks a lot for your post. couuld you please provide me with the sample code how you used it and also the statistics which you were mentioning. That would be of great help. gspk

I don't know where I got the link from, but I based my code on this article. I use roughly the same code, but I hardcoded all my column creation for the datatable and the mapping between Entity fields and datatable columns:

http://davidhayden.com/blog/dave/archive/2006/03/08/2877.aspx

There are some gotchas to doing it this way--I had to hardcode much of the EntityCollection => DataTable code. I started by trying to do it the way that is described in this post:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3861&HighLight=1

But it seems that datatables can't use nullable types. There might be a way around this that I don't know about.

It also seems that the columns used in the SqlBulkCopy need to be in the exact order that they are in the database, or it won't work. You also can't skip columns. Again, there might be ways of working around this, but I just wanted to get it working for a Proof of Concept project that I am working on.

I will see if I can generate some metrics. Keep in mind that any numbers will all be relative--I don't have a great testing environment.

HTH,

Phil

I ran a few basic tests, using me stored proc vs. SqlBulkCopy. It's hard to get consistent results because the network I use to get to the SQL Server does not have great bandwidth, and others may be using it as well.

I believe the SP is faster than an LLBL Entity Save, simply because the amount of query text being sent it smaller.

For just over 5300 records inserted, the stored proc takes between 35 and 75 seconds and the SqlBulkCopy takes between 1 and 3 seconds.

Again, you should take the actual numbers with a grain of salt, but it's clearly much faster.

I may try to put together sample code using Northwind at some point.

Phil

bulkcopy is much faster as it uses a low-level row insertion routine in the rdbms api, it completely bypasses the sql interpreter.