How to use SqlBulkCopy and track Bulk Inserts with Glimpse

Question

In our app we use the SqlBulkCopy class to facilitate bulk loading of our database. Recently, I've tried to add Glimpse to our solution but the code fails with an invalid cast:

System.InvalidCastException: Unable to cast object of type 'Glimpse.Ado.AlternateType.GlimpseDbConnection' to type 'System.Data.SqlClient.SqlConnection'

This is because Glimpse ADO is using a wrapped SqlConnection to make its magic possible. Unfortunately, SqlBulkCopy requires a SqlConnection so I need to cast the DbConnection.

Is there no out-of-the-box way to profile bulk insertions? The only work-around I came across with so far is:

(SqlConnection)((GlimpseDbConnection)dbConnection).InnerConnection

It's ugly since it requires referencing GlimpseDbConnection explicitly and probably it also requires adding custom time-line events to get some tracing. Is there no Glimpse add-on that solves this?

There is not one that I'm aware of. – nikmd23 Apr 25 '16 at 15:40 — nikmd23, Apr 25 '16 at 15:40

score 0 · Answer 1 · answered Nov 08 '16 at 17:49

I'm using this approach (with EntityFramework's DbContext) and it seems to work:

DbContext Db = /* ... */;

var glimpseDbConnection = Db.Database.Connection as GlimpseDbConnection;
var sqlConnection = glimpseDbConnection != null ? (SqlConnection)glimpseDbConnection.InnerConnection : (SqlConnection)Db.Database.Connection;

var glimpseDbTransaction = Db.Database.CurrentTransaction.UnderlyingTransaction as GlimpseDbTransaction;
var sqlTransaction = glimpseDbTransaction != null ? (SqlTransaction)glimpseDbTransaction.InnerTransaction : (SqlTransaction)Db.Database.CurrentTransaction.UnderlyingTransaction;

using (var bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction))
{
    var dataTable = /* ... */;
    bulkCopy.BatchSize = 2000;
    bulkCopy.BulkCopyTimeout = TimeSpan.FromMinutes(5).Seconds;
    bulkCopy.DestinationTableName = /* ... */;
    bulkCopy.WriteToServer(dataTable);
}

How to use SqlBulkCopy and track Bulk Inserts with Glimpse

1 Answers1