5

I've got some code which uses SqlBulkCopy. And now we're refactoring our code to use Enterprise Library database functions instead of standard ones. The question is how can I instantiate SqlBulkCopy? It accepts SqlConnection, and I only have DbConnection.

var bulkCopy = new SqlBulkCopy(connection)  // here connection is SqlConnection
{
    BatchSize = Settings.Default.BulkInsertBatchSize,
    NotifyAfter = 200,
    DestinationTableName = "Contacts"
};
pnuts
  • 58,317
  • 11
  • 87
  • 139
HiveHicks
  • 2,294
  • 5
  • 28
  • 42

1 Answers1

8

Really easy, we use it like that and it works perfectly :

using (DbConnection connection = db.CreateConnection())
{
    connection.Open();
    //blah blah

    //we use SqlBulkCopy that is not in the Microsoft Data Access Layer Block.
    using (SqlBulkCopy copy = new SqlBulkCopy((SqlConnection) connection, SqlBulkCopyOptions.Default, null))
    {
        //init & write blah blah
    }
}

The solution is to cast the connection : (SqlConnection) connection

Julien N
  • 3,880
  • 4
  • 28
  • 46
  • 1
    Oh, that's really easy. I thought about it, but haven't tried. Have you tried using TransactionScope with SqlBulkCopy? I've got something like: using (new TransactionScope()) { using (var connection = db.CreateConnection()) { using (var bulkCopy = new SqlBulkCopy((SqlConnection)connection) { /* blabla */ } } } and it throws System.Transactions.TransactionManagerCommunicationException saying that MSDTC is off, although I turned it on. – HiveHicks Jul 29 '10 at 11:54
  • Nope sorry. Never used TransactionScope. We use the transaction parameter of the SqlBulkCopy parameter (the last parameter of the constructor, `null` in the above example) – Julien N Jul 29 '10 at 12:00
  • If you just need a transaction for the BulkCopy, you can change the second parameter from `SqlBulkCopyOptions.Default` to `SqlBulkCopyOptions.UseInternalTransaction`. See http://msdn.microsoft.com/en-us/library/tchktcdk%28VS.80%29.aspx – Julien N Jul 29 '10 at 12:03