Running with Code Like with scissors, only more dangerous

2Apr/090

Your Own Transactions with LINQ-to-SQL

Posted by Rob

I’m working on porting an existing forum-based community from SMF to a new .NET-based forum platform that I’m authoring.  I’m excited about it; I love SMF, but it doesn’t have what I want and frankly, it’s a scary beast to try to tackle.  I’d considered using some kind of bridge between it and my code, but I knew I wanted deep integration of the forums with the new community site, and I wanted the community site in .NET.  So I made the decision to write an importer to talk between MySQL and my SQL Server-based solution.  I chose LINQ-to-SQL as my O/R mapper because, quite frankly, I find it much easier and more elegant to work with; so far as I know, I’m not the only one who thinks so.

Because of the nature of the data that I’m importing, I needed to run several SubmitChanges() calls to get the data into the database.  But I wanted to make sure that these submissions only worked if they ALL worked.  So I needed a transaction external to the normal LINQ-to-SQL in-memory object mapper.  Unfortunately, when I began a transaction using the underlying Connection property of the DataContext, I was met with an error:

System.InvalidOperationException: SqlConnection does not support parallel transactions.
   at System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName)
   at System.Data.SqlClient.SqlInternalConnection.BeginTransaction(IsolationLevel iso)
   at System.Data.SqlClient.SqlConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)

The solution was simple: DataContext has a Transaction property!  By setting this to the transaction that I was beginning, I was able to run the complete import in a single transaction:

dc.Connection.Open();
using (DbTransaction transaction = dc.Connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
    dc.Transaction = transaction;
    try
    {
        // do databasey things
        dc.SubmitChanges();

        transaction.Commit();
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        Console.WriteLine("Exception caught; transaction rolled back.");
        Console.WriteLine(ex.ToString());
    }
}

It took about 2 minutes to import 37,000 or so messages, plus all users, categories, forums, private messages, and polls from SMF.  The app ends up taking something in the neighborhood of 120mb of memory (I need to keep objects around to reference them for their children, since I assign new IDs), but it’s still a small one-off price to pay.

Tagged as: , , No Comments
19May/080

Using C# Enumerations as LINQ-to-SQL Entity Properties

Posted by Rob

Have you ever created a database object and said "OK, this column is going to correspond to this enumeration"?  If you're obsessive like me, you might have even gone so far as to create column restrictions for the valid range of values, and created a special SQL type that doesn't really do anything except give you peace of mind.

Well, I've got about three such fields on a couple entities on a recent project.  Since I wanted those properties to go into C# enumerations, I tried the natural thing: I typed the enumeration's type name into the "Type" property.

Setting a column's Type property.

Unfortunately, doing this didn't work.  In fact, it seemed to break Visual Studio; updates stopped propagating to my LINQ-to-SQL classes, and in fact since I had done this before a single save, I didn't get any entity classes. 

It turns out that Matt Davis found the answer to the problem: qualify the enumeration's type name with the global:: namespace qualifier if it doesn't live in a namespace (for instance, code within an ASP.NET App_Code folder).

The global:: qualifier is important.

Once I added the qualifier to my type names, saving the DBML file correctly updated the LINQ-to-SQL classes, and I was off and running!

Tagged as: , , No Comments