Transactions with TableAdapters, a lazy man's approach

RyanW — Thu, 30 Mar 2006 20:14:00 GMT

If you've used TableAdapters, you'll know that their ability to deal with transactions leaves a little to be desired. Little? Did I say "little"? I mean "a lot".

Sahil Malik has a good slew of advice for people wanting to use transactions with TableAdapters. Most people will probably just wrap everything up in a TransactionScope and be done with it, which is fine if you're not running a high-traffic site. In doing this, you'll run into the annoyance of the transaction being promoted to the DTC, which is an expensive bit of "bling" to tango with.

For the people that don't like the whole promotion to DTC thing, they'll probably extend their TableAdapter's partial class and add a BeginTransaction method similar to what Sahil proposes.

For me, who's looking at a bajillion TableAdapters, with quite a few of those needing to operate within the scope of a transaction, I instead choose to play the Hacky card and just set the transaction on the TableAdapter's commands through a little bit of reflection. Some may throw your hands up in the air on this. I, however, love it, as it fits my purposes like a latex glove.

The code follows for my so-called TableAdapterHelper.

using System; using System.Data; using System.Data.SqlClient; using System.Reflection;public class TableAdapterHelper { public static SqlTransaction BeginTransaction(object tableAdapter) { return BeginTransaction(tableAdapter, IsolationLevel.ReadUncommitted); }

public static SqlTransaction BeginTransaction(object tableAdapter, IsolationLevel isolationLevel) { // get the table adapter's type Type type = tableAdapter.GetType();

// get the connection on the adapter SqlConnection connection = GetConnection(tableAdapter);

// make sure connection is open to start the transaction if (connection.State == ConnectionState.Closed) connection.Open();

// start a transaction on the connection SqlTransaction transaction = connection.BeginTransaction(isolationLevel);

// set the transaction on the table adapter SetTransaction(tableAdapter, transaction);

return transaction; }

/// <summary> /// Gets the connection from the specified table adapter. /// </summary> private static SqlConnection GetConnection(object tableAdapter) { Type type = tableAdapter.GetType(); PropertyInfo connectionProperty = type.GetProperty("Connection", BindingFlags.NonPublic | BindingFlags.Instance); SqlConnection connection = (SqlConnection)connectionProperty.GetValue(tableAdapter, null); return connection; }

/// <summary> /// Sets the connection on the specified table adapter. /// </summary> private static void SetConnection(object tableAdapter, SqlConnection connection) { Type type = tableAdapter.GetType(); PropertyInfo connectionProperty = type.GetProperty("Connection", BindingFlags.NonPublic | BindingFlags.Instance); connectionProperty.SetValue(tableAdapter, connection, null); }

/// <summary> /// Enlists the table adapter in a transaction. /// </summary> public static void SetTransaction(object tableAdapter, SqlTransaction transaction) { // get the table adapter's type Type type = tableAdapter.GetType();

// set the transaction on each command in the adapter PropertyInfo commandsProperty = type.GetProperty("CommandCollection", BindingFlags.NonPublic | BindingFlags.Instance); SqlCommand[] commands = (SqlCommand[])commandsProperty.GetValue(tableAdapter, null); foreach (SqlCommand command in commands) command.Transaction = transaction;

// set the connection on the table adapter SetConnection(tableAdapter, transaction.Connection); } }

Here's some example usage of the helper methods above. In this example, we have some work that spans two different adapters that we need to enclose in a transaction.

SqlTransaction transaction = null; try { using (FooTableAdapter fooAdapter = new FooTableAdapter()) { transaction = TableAdapterHelper.BeginTransaction(fooAdapter); fooAdapter.DoSomething(); } using (BarTableAdapter barAdapter = new BarTableAdapter()) { TableAdapterHelper.SetTransaction(barAdapter, transaction); barAdapter.DoSomething(); } transaction.Commit(); } catch { transaction.Rollback(); throw; } finally { transaction.Dispose(); }

So, what are the disadvantages here? We're using reflection so there's no compile-time checking nor is there any strong-typing action going on. Using reflection is obviously slower execution-wise than extending your TableAdapter's class. Also, the code-gen implementation of your TableAdapter class could theoretically change if VS2005 gets patched in the future and would therefore cause your TableAdapterHelper to break.

What are the advantages? Compile-time checking, schmompile-schmime checking. Reflection is eons faster than a promotion to DTC, and this approach is certainly faster than extending your TableAdapter classes by hand. And, the code-gen implementation of your TableAdapter class is unlikely to change.

Modify data before it's inserted in SQL Server 2000/2005

RyanW — Fri, 03 Feb 2006 05:48:00 GMT

I had a scenario where I needed to massage some data in this one particular column before it got inserted into the database.

I usually like to implement this kind of logic alongside its business brethren, leaving as much business brouhaha out of the raw database as possible, but in this case I was straddling two apps. One old one, one new one, both using the same database. Problem was, I didn't want to pull down the old code from the Vault, recompile, and post a newish-old version. Long story short, I implemented an INSTEAD OF trigger on the table to massage the data before it got inserted. Boom, data massage for both apps.

/* This trigger makes sure that the data being inserted into the BarColumn column starts with a dash. If it doesn't, it will prepend one. */

CREATE TRIGGER FooTable_Insert ON dbo.FooTable INSTEAD OF INSERT AS DECLARE @BarColumn varchar(50) SET @BarColumn = (SELECT BarColumn FROM INSERTED) IF (LEFT(@BarColumn, 1) <> '-') BEGIN SET @BarColumn = '-' + @BarColumn SELECT * INTO #Inserted FROM Inserted UPDATE #Inserted SET BarColumn = @BarColumn INSERT INTO FooTable SELECT * FROM #Inserted END ELSE INSERT INTO FooTable SELECT * FROM Inserted

Highly performant, caching image resizer?

RyanW — Thu, 21 Oct 2004 18:03:00 GMT

I'm in the ocean in a life ring, somebody please pull me out!

We have a client whose website gets a lot of traffic. Lots of product images. Lots of image resizing going on.

What I need is a highly performant, .NET-based image resizer. It needs to cache the resized images to disk so it doesn't have to resize every time the page loads. Optionally, it'd be ideal if it supported varying image resizing algorithms. And it'd be great if I could adjust the quality level.

For the past three years, I've used United Binary's AutoImageSize component. When it works, it's fantastic. But, it crashes like nobody's business. And nothing hurts my business like a component that crashes like nobody's business.

Anyone have any ideas?

(Besides rolling my own using the uber-easy .NET framework. I don't have time.)

Ryan Whitaker

Transactions with TableAdapters, a lazy man's approach

Modify data before it's inserted in SQL Server 2000/2005

Highly performant, caching image resizer?