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?
Two reasons I can think of:
inserts, but occasional
inserts intermixed with
updates. Microsoft themselves state that a normal
insertis more efficient for that, on the
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
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.