Using NHibernate transaction in SqlBulkCopy

问题:

I'm storing some data using NHibernate, and I need to insert huge amount of data as a part of this action - i.e. in the same transaction. Code looks like this:

using (ISession session = NHibernateHelper.OpenSession())
using (ITransaction transaction = session.BeginTransaction())
{
    session.SaveOrUpdate(something);
    // ...


    SqlBulkCopy bulkCopy = new SqlBulkCopy(
    (SqlConnection)session.Connection,
    SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers,
    ???transaction???
    );
    //...

    transaction.Commit();
}

I know that I could use TransactionScope or do it otherwise. But I insist on this pattern. Let's pretend that for the sake of independent DB access (if I extract and inject arbitrary bulk insert operation). Is there a way how to get SqlTransaction instance out of NHibernate.ITransaction?

Thanks

回答1:

Unsurprisingly, Ayende tackled this one as well, but it's pretty grody.

The gist of it is that you know you can enlist normal ADO.NET IDbCommand instances in the NHibernate transaction, like so:

var cmd = new SqlCommand ();
if (session.Transaction != null && session.Transaction.IsActive)
    session.Transaction.Enlist (cmd);

But SqlBulkCopy isn't an IDbCommand, and that particular constructor requires a SqlTransaction (so you've already skipped the boat on provider-independence anyways). So cheat -- your example might look something like this:

using (var session = NHibernateHelper.OpenSession ())
using (var transaction = session.BeginTransaction ()) {
    using (var cmd = new SqlCommand ()) {
        transaction.Enlist (cmd);

        var bulk = new SqlBulkCopy ((SqlConnection)session.Connection,
                                    SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers,
                                    (SqlTransaction)cmd.Transaction);
    }
    // ...
    transaction.Commit ();
}

You'll undoubtedly want some error-checking, safe casts, etc. in there. I'm not aware of a more modern/less scary way to do this, unfortunately (even to get an IDbTransaction from an ITransaction).

回答2:

Check this post from Ayene:
http://ayende.com/Blog/archive/2009/08/22/nhibernate-perf-tricks.aspx

He shows how you can do that using both options either NHibernate StatelessSession or SqlBulkCopy. It shows a sample code like this:

    var dt = new DataTable("Users");
    dt.Columns.Add(new DataColumn("Id", typeof(int)));
    dt.Columns.Add(new DataColumn("Password", typeof(byte[])));
    dt.Columns.Add(new DataColumn("Username"));
    dt.Columns.Add(new DataColumn("Email"));
    dt.Columns.Add(new DataColumn("CreatedAt", typeof(DateTime)));
    dt.Columns.Add(new DataColumn("Bio"));

    for (int i = 0; i < count; i++)
    {
        var row = dt.NewRow();
        row["Id"] = i;
        row["Password"] = Guid.NewGuid().ToByteArray();
        row["Username"] ="User " + i;
        row["Email"] = i + "@example.org";
        row["CreatedAt"] =DateTime.Now;
        row["Bio"] =  new string('*', 128);
        dt.Rows.Add(row);
    }

using (var connection =
 ((ISessionFactoryImplementor)sessionFactory).ConnectionProvider.GetConnection())
{
    var s = (SqlConnection)connection;
    var copy = new SqlBulkCopy(s);
    copy.BulkCopyTimeout = 10000;
    copy.DestinationTableName = "Users";
    foreach (DataColumn column in dt.Columns)
    {
        copy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
    }
    copy.WriteToServer(dt);
}