Using Sql Server 2005 Snapshot isolation level with ado.net 2.0

Sql Server 2005 now supports non-blocking read operations with the Snapshot isolation level. What does this mean for ADO.NET 2.0?

Not much really, all of the hard work is done in the server and all we have to do is expose it. We already have a System.Data.IsolationLevel Enum and all we have done is add a Snapshot value. You can now call BeginTransaction(IsolationLevel.Snapshot) and this would be the end of the story if the Snapshot isolation level behaved the same way as all of the other isolation levels.

It does not.

Well, it is not much of a punch line really, but this is hardly a murder mystery. A quick look at the Snapshot documentation https://www.microsoft.com/technet/prodtechnol/sql/2005/SQL05B.mspx will show under “Allowing Snapshot Isolation” that “Administrators must set a database option to allow snapshot isolation. This database option may not take effect immediately; ”

What this really means is that you can _only_ use IsolationLevel.Snapshot on a Sql Server 2005 database that has been set up for Snapshot isolation level. This can be done by an administrator by executing the following tsql statement:

ALTER DATABASE <database name> SET ALLOW_SNAPSHOT_ISOLATION ON.

Important: If you try to start a Snapshot isolation level on a database that is not set up correctly we will not throw an exception on BeginTransaction, you will only get an error when the transaction touches the actual database (when you try to do an update insert delete)

What about existing ado.net applications where you want to modify ReadCommitted isolation level to Snapshot, do you have to go through your code? No need, the ALTER DATABASE statement has been upgraded to allow you to get snapshots benefits for free:

ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON