CODEWITHSUNDEEP
c#.netentity-framework
dibs487
dibs487

Reputation: 1364

How can I use SqlBulkCopy from Entity Framework database connection in unknown state

I have a static helper method for bulk inserting Entity Framework objects. It works and my unit tests pass but it looks wrong and I can't help but think there must be a better way to do it.

public static void BulkInsert<T>(Entities db, IList<T> list)
{
    SqlTransaction transaction =  db.Database.CurrentTransaction != null ? db.Database.CurrentTransaction.UnderlyingTransaction as SqlTransaction : null;

    if (db.Database.Connection.State != ConnectionState.Open)
    {
        db.Database.Connection.Open();
    }

    using (var bulkCopy = new SqlBulkCopy((db.Database.Connection) as SqlConnection, SqlBulkCopyOptions.Default, transaction))
    {
        //fill a datatable and write to server, this bit works
    }
}

It could be called inside a DbContextTransaction and/or the DbContext.Database might have done something already so the connection might be open. If you don't explicitly open the connection before creating the bulk copy you get an error:

System.InvalidOperationException: WriteToServer requires an open and available Connection

which is odd, I'd have thought that sqlBulkCopy would have opened the connection if it had to.

So my question is am I going about this the right way?

Upvotes: 2

Views: 6179

Answers (1)

Jim
Jim

Reputation: 16022

I have used your approach successfully myself.
My feeling is that you should close it again if you opened it.

I would additionally let SqlBulkCopy handle the transaction itself. I don't think enlisting SqlBulkCopy into any ambient transaction is a great idea unless your particular scenario absolutely demands it.

public static void BulkInsert<T>(PandaDataContext db, IList<T> list)
{
    var mustOpen = db.Database.Connection.State != ConnectionState.Open;
    try
    {
        if (mustOpen)
            db.Database.Connection.Open();
        using (var bulkCopy = new SqlBulkCopy((db.Database.Connection) as SqlConnection, SqlBulkCopyOptions.Default))
        {
        }
    }
    finally
    {
        if (mustOpen)
            db.Database.Connection.Close();
    }
}

Upvotes: 4

Related Questions