9

I have done some research for "The bast way to insert huge data into DB with C#" then a lot of people just suggested me using SqlBulkCopy. After I tried it out and it really amazed me. Undoubtedly, SqlBulkCopy is very very fast. It seems that SqlBulkCopy is a perfect way to insert data (especially huge data). But why dont we use it at all times. Is there any drawback of using SqlBulkCopy?

Edison Chuang
  • 2,911
  • 4
  • 26
  • 28

2 Answers2

13

SqlBulkCopy does exist for Oracle v11 as well, but it's provided by the Oracle .NET assemblies you get when you install Oracle Client. The SqlBulkCopy class is basically implemented one by one, by the provider of the target database engine.

One HUGE drawback, though - there is absolutely no error reporting. If, for example, you've updated data in a DataSet, are flushing it back tothe DB with an adapter, and there's a key violation (or any other failure), the culprit DataRows will have .HasErrors set to true, and you can add that to your exception message when it's raised.

With SqlBulkCopy, you just get the type of the error and that's it. Good luck debugging it.

David Catriel
  • 365
  • 7
  • 15
  • 2
    +1 Completely agree that debugging is an issue with BulkCopy. One approach I have is to "deconstruct" failed BulkCopy commands and inserting row by row in a finally block. That way I can pinpoint the offending DataRow as part of my Error reporting. – Totero Jan 16 '13 at 12:03
  • 1
    I figured out that for error reporting there is a way to get the individual failures back, but it does involve resubmitting the bulk copy in pages of one record and then catching and throwing all the exceptions (along with the culprit rows). It's not the most efficient, but it only happens when an error occured, so it's not too bad. See here for complete article: http://www.codeproject.com/Articles/387465/Retrieving-failed-records-after-an-SqlBulkCopy-exc – David Catriel Jan 17 '13 at 13:06
10

Two reasons I can think of:

  1. As far as I know, it's only available for Microsoft SQL Server
  2. In a lot of normal workloads, you don't do bulk inserts, but occasional inserts intermixed with selects and updates. Microsoft themselves state that a normal insert is more efficient for that, on the SqlBulkCopy MSDN page.

Note that if you want a SqlBulkCopy to be equivalent to a normal insert, at the very least you'll have to pass it the SqlBulkCopyOptions.CheckConstraints parameter.

Peter Majeed
  • 5,304
  • 2
  • 32
  • 57
Michiel Buddingh
  • 5,783
  • 1
  • 21
  • 32
  • Yeah, SqlBulkCopy can only be used with MS SQL Server. This is also only one of drawback I know. Sometime it is not really big deal. For example: Our customers only use MS SQL Server and we only build applications for them with several classes that belong to System.Data.SqlClient namespace. – Edison Chuang Jun 12 '09 at 16:56
  • Why is `SqlBulkCopyOptions.CheckConstraints` false by default? - it's so counter-intuitive! – Barry Kaye Dec 22 '11 at 12:43
  • 3
    @BarryKaye: No, it's not. The name is `Bulk Copy` so you should be copying data for which the constraints are already be known to be correct, if not then you don't know what you are doing... :) – Tamara Wijsman Feb 06 '12 at 07:08