Using TransactionScope for handling transactions

Implementing transactions from C#/VB code today might seam easier than ever before, but the path to easier and effective transaction handling has many traps on the way. Microsoft made the life of developers seemingly easier by implementing the TransactionScope class in the .net framework 2.0. I say seemingly easier because you sometimes might find your self baffled by the way your code will behave.

The TransactionScope class uses MS Distributed Transaction Coordinator to manage transactions, so before you start using the TransactionScope, make sure you have DTC running on the Web and Database server and that you have granted enough security permission for the DTC to be able to communicate on thoes machines.

So to get started with TransactionScope, here is a simple code example that seams perfectly OK.

using(TransactionScope outerScope = new TransactionScope())
{
    // Execute query 1

    using(TransactionScope innerScope = new TransactionScope())
    {
        try
        {
            // Execute query 2
        }
        catch (Exception)
        {
        }

        innerScope.Complete();
    }

    outerScope.Complete();
}

Now if query 2 which is inside of the try/catch block, rasies an error you will catch the exception in the try/catch block and handle it, but here is a supprise the application will throw an ObjectDisposedException on line 15 when you try to complete the transaction. This is because the DTC has already caught the exception and although you have handled it, the TransactionScope objects where already disposed by the .Net code and the transaction has been rolled back. Notice that I have said “objects” this is because both of the TransactionScope object have been disposed as they are a part of the same transaction. This is just one example where you might seam surprised that the code is not behaving as you would expect.

The TransactionScope implements something that is referred to as an “Ambient transaction”. This is the transaction in which all database queries are executed. When I say all queries I mean all queries that are executed between the creation of the TransactionScope object and the calling of the Complete() method, including all method calls and their method calls and so on, that are executed between those two lines. That means that you don’t need to handle the transaction in every method that participates in the transaction unless you want to.

You can control the manner in which a method participates in a transaction quite easily. Here is a piece of code to demonstrate:

using(TransactionScope outerScope = new TransactionScope())
{
    // Execute query 1

    using(TransactionScope innerScope =
          new TransactionScope(TransactionScopeOption.Required))
    {
        // Execute query 2
        innerScope.Complete();
    }

    using (TransactionScope innerScope =
           new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        // Execute query 3
        innerScope.Complete();
    }

    using (TransactionScope innerScope =
           new TransactionScope(TransactionScopeOption.Suppress))
    {
        // Execute query 4
        innerScope.Complete();
    }

    outerScope.Complete();
}

This demonstrates the use of the TransactionScopeOption Enumeration to control the way some piece of code will participate in a transaction. The enumeration consist of three options Required, RequiresNew and Suppress. The default options for the TransactionScope constructor is “Required” which states that an ambient transaction is required for this operations, if non exists the transaction will be created. The options “Suppress” states thata the this TransactionScope will not participate in the transaction and therefore all database queries are executed without a transaction. The option of “RequiresNew” states that a new ambient transaction will allways be created and become the root transaction.

There are further options that can be modified when using the TransactionScope class by using a different constructor TransactionScope Constructor (TransactionScopeOption, TransactionOptions) that takes the TransactionOptions struct as a parameter here is an code example.

TransactionOptions options = new TransactionOptions();
options.Timeout = TimeSpan.FromMinutes(1);
options.IsolationLevel = IsolationLevel.Serializable;

using(TransactionScope scope =
      new TransactionScope(TransactionScopeOption.Required, options))
{
    scope.Complete();
}

The TransactionOptions struct has 2 properties Timeout and IsolationLevel. The first is self explanatory the timeout prior to completition of the transaction. The second one is more important, I would say my self crucial to effective handling of transactions. The IsolationLevel Enumeration consist of several levels of isolation: Serializable, RepeatableRead, ReadCommitted, ReadUncommitted, Snapshot, Chaos and Unspecified. With the “Serializable” isolation level being the strictest and “ReadUncommitted” begin the least strict form of isolation.

Serializable means that all transactions occur in a completely isolated fashion; i.e., as if all transactions in the system had executed serially, one after the other. That means no other transaction can be run until the one running is completed. This type of isolation should be used very carefully since it can cause massive timeouts etc. if used with long lasting transactions.

RepeatableRead is a step down from Serializable isolation level, where multiple transaction can run at the same time, but all data read during the transaction can not be modified (exclusive row/page locks) and volatile data cannot be read.

ReadCommitted is a further step down in isolation where the transaction allows for reading and modifying of data that has been used in a select statement, but volatile data cannot be read or changed by other queries.

ReadUncommitted is the least strict isolation level where the transactions can see uncommitted changes performed by other transactions, allowing for dirty reads.

More on isolation levels on Wikipedia.

That sums up the basic use of the TransactionScope class with more to come 🙂

Putting C# into SQL

In my latest project we have come across a need to get results from a web service inside SQL Server. First basic reason why we wanted to have such an integration of web services is so that we could use them transparently in Microsoft Reporting Services. That of course is not the only reason we also wanted to use the same results as an input to some of our stored procedures and to do that we need to be able to get the results from the web service through a stored procedure or a table valued function.

The process it self is quite simple if you know where to begin. The beginning is in Sql servers implementation of CLR, with the use of CLR you can easily show those DBA’s how real programing is done :). Unfortunately for the DBA’s this is the only way this can be done so they need to trust us “developers” :). Seriously this is not such a problem to implement.

Pop open up Visual Studio and create a new “Sql Server Project” in the language of your preference (VB or C#) it’s all the same, but I will use C#. To start us off we need to configure the project first, so open “Project Properties” go to the “Build” section and find the option “Generate serialization assembly” set the option from auto to on. You have to do this because Sql server clr does not allow dynamic xml serialization, I guess it a security thing. Now when you build your project you will end up with two dlls one for you code “project.dll” and the other one for the serialized xml “project.serialization.dll”.

Now add your web service to the project just like in any other project trough the “Add web reference” menu, after you have done sou let’s add a new item to the project, add a stored procedure item to the project. Here is some sample code for the stored procedure.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void WebServiceTest()
{
	// Initialize web service, adjust it for your service
	using (WebService ws = new WebService())
	{
		// Create a template record for the result set
		SqlDataRecord record = new SqlDataRecord(
			// The strings are the names of the columns of the result set
			// first field of type int
			metaData[0] = new SqlMetaData("field1", SqlDbType.Int),
			// second field of type varchar, equivalent of varchar(max)
			metaData[2] = new SqlMetaData("field2", SqlDbType.VarChar, -1),
			// third field of type decimal equivalent of decimal(12,4)
			metaData[4] = new SqlMetaData("field3", SqlDbType.Decimal, 12, 4));

		// Mark the begining of the result-set.
		SqlContext.Pipe.SendResultsStart(record);

		// Send rows back to the client.
		// Get the results from the web service, query a method on your web service
		foreach (Result item in ws.GetResult())
		{
			// set the value for field1
			record.SetInt32("field1", item.Value1);
			// set the value for field2
			record.SetString("field2", item.Value2);
			// set the value for field3
			record.SetDecimal("field3", item.Value3);

			// Send the row back to the client.
			SqlContext.Pipe.SendResultsRow(record);
		}

		// Mark the end of the result-set.
		SqlContext.Pipe.SendResultsEnd();
	}
}

Let’s look at this code. On line 4 is the initialization of the web service you will have to adjust it to your web service. Then you have to initialize a template record for your result set as is done on line 8 trough line 15. We initialize there columns of your result set column one is named “field1” and has a type of int, column two is named “field2” and has a type of varchar with length -1 or varchar(max), column three is named “field3” and has a type of decimal with length 12 and precision 4 or decimal(12,4). On line 18 we start the return of our result set and tell sql that we will be returning items based on record template we have just created. On lines 22 through 33 we iterate through our result set, set the values and use the Sql pipe to send the results. On line 36 we end the sending of results. That is it for the code part, so now we need to register the assembly in the sql server.First you have to set the database trustworthy option to on.

ALTER DATABASE DatabaseName SET TRUSTWORTHY ON

If we do not set this option to on we can’t register our assemblies as UNSAFE, and if we cant do that Sql clr wont allow us to connect to the web service. I think that you could use the EXTERNAL_ACCESS option but I have not tried that.

CREATE ASSEMBLY [AssemblyName]
FROM'C:\AssemblyPath\AssemblyName.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY [AssembyName]
FROM'C:\AssemblyPath\AssemblyName.Serialization.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE PROCEDURE [SchemaName].[WebServiceTest]
AS
EXTERNAL NAME [AssemblyName].Namespace.WebServiceTest

That is it now you can test out your new sql clr procedure.

Start of a slow begining

I am quite of a casual gamer, you know that type that always complains and always wants more and more of features etc. I understand game developers have a hard time of developing games and adjusting them to please as much gamers as possible. Since I am a software developer my self, and I always have to bend over for my clients, whose count fortunately is not in the millions rather just a dozen of those super smart “managers”, you could say that i have it easier then the game developers.

I have often thought about writing my own game, to correct all that stuff that i found lacking in the games i played. I found often that the games had a good start, but at a certain point it seams like the development stopped, and the features that could have been implemented have not been.

Until now I did not think that one man, without no previous knowledge about game development, directx, graphics and etc. could do, I am a back end, enterprise solution developer, so drawing is not something that I am good at. But now after a saw a few demonstrations by Microsoft, on WinDays, TechEd and community conferences XNA Studio could solve a lot of my problems.

So off to install XNA. First to download the installer, some 80 MB download free from microsoft, and lets try it. But then i find out, I dont have a habit of reading those readmes and so on, that you need Visual Studio Express C# SP1 installed. So I figure this has to be an install bug, since I have Visual Studio Team System SP1 installed on my laptop. But <span style=”font-weight: bold;”>no </span>Microsoft decided that for now it would only support the Express version of Visual Studio for use with XNA Studio. So i download the C# Express version, and install it the same with SP1. Next i find out that the Express version does not support my favorite add in ReShaper, which i find as an essential tool for any serious development. But luckily Microsoft is considering running XNA Studio on other version of VS in the future.

So to recap, to start with XNA you need:
1. Visual Studio Express C# (no exception, there is no hack to run XNA on other versions of VS)
2. Visual Studio Express SP1
3. XNA Studio 1.0 refresh

The OS on my laptop is Vista, and XNA Game Studio does not by its spec support Vista, but fearless as I am, I did it anyway, so far I did not have any problems.

Loaded the SpaceWar example and started to try to figure it out 🙂 keep you posted