SQLServer Transactions in .NET

Les Kendall discusses why we need Database Transactions and gives simple examples for Microsoft SQLServer in C# and VB.NET.

What we are trying to achieve: All or Nothing

For an example, lets say we have a simple ordering system. When a new customer wants to place an order we need to do several things:

• Add the customers name and address to the Customers table
• Create a sales entry in the Invoice table
• Add the individual items to the InvoiceItems table
• Decrement the count in the ProductStock table.

That all sounds easy, and it is – unless we have a problem. Say we add the customer ok, and then create the invoice and invoice items ok, but when we come to update the Stock table we have a error.

Now we have a serious problem. We’ve got an order created and invoiced, but by not updating the Stock count we are creating future problems for our business. The next order we take may be accepted and invoiced, but we may not have the items in stock to cover the order.

In real-life cases, the problems could be significantly worse and pose a real threat to the company. We may bill the customer but not send out the goods. Your Business won’t survive long if you do things like that.

Other problems may arise from interaction of other Users or writing data that would violate the rules of the database, damaging the integrity and consistency of the data. So if you are updating multiple tables at the same time, you must use a ‘transaction’.

A Transaction is like grouping all your SQL into a batch that works as a single unit. You create a Transaction on the database, run your SQL commands and then either COMMIT the data (if OK) or ROLLBACK the entire Transaction (if even a single error occurs).

Conform to A.C.I.D.

In database design terms, you should adhere to ‘ACID’ for transactions, which stands for:

• Atomic. This is the All or Nothing Rule. You either write ALL your data or NONE of it. You can’t do anything in between.
• Consistency. The database is defined with rules and constraints to which ALL data must conform. If any of it doesn’t, NONE of the data is written.
• Isolation. If you start a transaction and then another user starts a transaction on the same data, your transaction will run in isolation and it’s entirety before the other transaction starts, or vice-versa. This prevents other users seeing your data in mid-transaction and using data that may not be final.
• Durability. This means the database management system (DBMS) guarantees that once the data is committed the changes are permanent and will not be lost.

SQLServer Transactions with .NET

Microsoft have incorporated Transactions into the database classes of .NET. They’re easy to implement as long as you understand that while you are in a Transaction some data may not be available as it will be locked by the transaction (remember the Isolation rule above). Here are some typical code examples in C# and VB.NET. (I used .NET 2.0 here but the code should be ok for higher versions).

Example in C# (using OleDb connection)

// These should be in the top of the file:
// using System.Configuration
// using System
// using System.Data.OleDb;

// Build the transaction command
string connectionString = “”;
OleDbConnection connection = null;
OleDbCommand command = null;

connectionString = ConfigurationManager.AppSettings[ “… Your connection string …” ].ToString();
connection = new OleDbConnection( connectionString );

// Start Transaction
connection.Open();

command = connection.CreateCommand();
OleDbTransaction transaction;

// Start a local transaction
transaction = connection.BeginTransaction();

// Must assign both transaction object and connection
// to Command object for a pending local transaction.
command.Connection = connection;
command.Transaction = transaction;

try {

// run the updates
command.CommandText = “… SQL Statement 1 …;
command.ExecuteNonQuery();

command.CommandText = “… SQL Statement 2 …;
command.ExecuteNonQuery();

command.CommandText = “… SQL Statement 3 …;
command.ExecuteNonQuery();

// Commit transaction
command.Transaction.Commit();

} catch ( Exception ex ) {

// Rollback Transaction
command.Transaction.Rollback();

throw new Exception( “Error: ROLLED BACK, No Changes made to Database. ” + ex.Message );

} finally {

command.Connection.Close();

}

Example in VB.NET (using SQL Connection)

‘ These should be at the top of your file
‘ Imports System.Data
‘ Imports System.Configuration
‘ Imports System.Data.SqlClient

Dim connection As New SqlConnection(ConfigurationManager.AppSettings(“… Your Connection String …”))
connection.Open()

Dim command As SqlCommand = connection.CreateCommand()
Dim transaction As SqlTransaction

‘ Start a local transaction
transaction = connection.BeginTransaction(“MarkPaidTransaction”)

‘ Must assign both transaction object and connection
‘ to Command object for a pending local transaction.
command.Connection = connection
command.Transaction = transaction

Try

command.CommandText = “… SQL Statement 1 …”
command.ExecuteNonQuery()

command.CommandText = “… SQL Statement 2 …”
command.ExecuteNonQuery()

command.CommandText = “… SQL Statement 3 …”
command.ExecuteNonQuery()

‘ Commit transaction
transaction.Commit()

Catch ex As Exception

‘ Roll back transaction
transaction.Rollback()

Throw New Exception(“Error ROLLED BACK: No Changes written to database. ” & ex.Message)

Finally
connection.Close()

End Try