how to integrate existing database transaction code

Oct 12, 2010 at 4:29 PM

Hi Chinhdo,

I have some code, that already uses transactions for database operations. Like the following (massively simplified):

using (SqlConnection connection = new SqlConnection(conf.DatabaseConnectionString))
{
	connection.Open();

	// SNAPSHOT ISOLATION MUST BE ENABLED ON DATABASE!
	SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Snapshot);
	
	SqlCommand command = connection.CreateCommand();
	command.Transaction = transaction;
	
	command.CommandType = CommandType.Text;
	command.CommandText = "SELECT id FROM foo WHERE a = b";
	
	// try-catch surrounded...
	object data = command.ExecuteScalar();

  command.CommandText = "INSERT INTO foo (x, y) VALUES (1, 2)";
  
	// try-catch surrounded...
	command.ExecuteNonQuery();
	
	transaction.Commit();
}

Now I need to do some file operations in the same transaction, committing all at once. I understand your 5-minutes example, but it doesn't show me how to change my code. You see, I already got a transaction using a certain isolation level snapshot. How can I achieve this using the TransactionScope? Many thanks in advance!

Cheers,

HoK

Oct 13, 2010 at 11:24 AM

Hi,

I found the solution by my own. To get a transaction with snapshot isolation, there is TransactionOptions.IsolationLevel. The code looks like this now (simplified):

ChinhDo.Transactions.IFileManager fileManager = new ChinhDo.Transactions.TxFileManager();

try
{
	TransactionOptions txOptions = new TransactionOptions();

	// SNAPSHOT ISOLATION MUST BE ENABLED ON DATABASE!
	txOptions.IsolationLevel = System.Transactions.IsolationLevel.Snapshot;

	// Create a transaction scope and do all within
	using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, txOptions))
	{
		using (SqlConnection connection = new SqlConnection(connectionString))
		{
			connection.Open();

			SqlCommand command = connection.CreateCommand();
			command.Transaction = transaction;

			command.CommandType = CommandType.Text;
			command.CommandText = "SELECT id FROM foo WHERE a = b";

			// try-catch surrounded...
			object data = command.ExecuteScalar();

			fileManager.Snapshot(outFileName);
			// some writes to 'outFileName'

			command.CommandText = "INSERT INTO foo (x, y) VALUES (1, 2)";
			
			// try-catch surrounded...
			command.ExecuteNonQuery();
		}

		// If an exception has been thrown, Complete will not 
		// be called and the transaction is rolled back.
		scope.Complete();
	}
}
catch (Exception e)
{
	// error handling
}

It works! Great stuff, Chinhdo.

HoK

Coordinator
Nov 11, 2010 at 12:20 AM

Glad to hear that it's working. I was out of the country on vacation so I wasn't able to reply sooner. Chinh