Click here to Skip to main content
15,867,308 members
Articles / Mobile Apps
Article

Easier Database Transactions - Extending the Using Statement to Perform Automatic Database Transactions

Rate me:
Please Sign up or sign in to vote.
4.47/5 (21 votes)
25 Oct 20042 min read 119K   978   55   11
Any user who works with database updates uses transactions. Transactions in ADO.NET are done using a transaction object, and a try..catch, but there is an easier, one might even say a more C# way to handle database transactions.

Overview

Any user who works with database updates uses transactions. Transactions in ADO.NET are done using a transaction object, and a try..catch, but there is an easier, one might even say a more C# way to handle database transactions.

Visual Basic .NET Users

Because this technique relies on the using statement which is C# specific, this technique cannot be applied in Visual Basic .NET. However, rumors are that Visual Studio .NET 2005 will include a similar statement for Visual Basic.

Transactions the Normal Way

Here is an example of a database transaction the normal way. This is a condensed version of the example on MSDN.

C#
using (OleDbTransaction xTx = _DB.BeginTransaction()) {
    try {
        OleDbCommand xCmd = _DB.CreateCommand();
        xCmd.Transaction = xTx;
        xCmd.CommandText = "Insert into Person (Name, Telephone)" + 
                           " values ('Normal One', '(412) 555-1212')";
        xCmd.ExecuteNonQuery();
        if (chckThrow.Checked) {
            throw new Exception("Test Exception");
        }
        xCmd.CommandText = "Insert into Person (Name, Telephone)" + 
                           " values ('Normal Two', '(423) 555-1212')";
        xCmd.ExecuteNonQuery();
        xTx.Commit();
    }
    catch (Exception xException) {
        xTx.Rollback();
        throw xException;
    }
}

Transactions the Easy Way

With my method, the code does the same, but becomes a bit shorter, cleaner, and even a bit more C# like.

C#
using (Transaction xTx = new Transaction(_DB)) {
    OleDbCommand xCmd = _DB.CreateCommand();
    ((IDbCommand)xCmd).Transaction = xTx.DBTransaction;
    xCmd.CommandText = "Insert into Person (Name, Telephone)" + 
                       " values ('Object One', '(412) 555-1212')";
    xCmd.ExecuteNonQuery();
    if (chckThrow.Checked) {
        throw new Exception("Test Exception");
    }
    xCmd.CommandText = "Insert into Person (Name, Telephone)" + 
                       " values ('Object Two', '(423) 555-1212')";    
    xCmd.ExecuteNonQuery();
    xTx.Commit();
}

Demo Project

The demo project demonstrates this and allows simulated exceptions. The demo project uses the included Access database file for simplicity. However, the included class works with any ADO.NET data source.

How it Works

The using statement in C# causes the dispose to be called when the block is exited. By implementing a Dispose, and checking to see if the transaction has been committed, the transaction class can then determine whether or not to call rollback. There is no need to rethrow any exception that may be in progress, because C# automatically continues the exception path as part of the handling of the using block.

Transaction Class Source

C#
public class Transaction : IDisposable {

    private IDbConnection _DB;
    private IDbTransaction _DBTransaction;

    public IDbTransaction DBTransaction {
      get { return _DBTransaction; }
    }

    public Transaction(IDbConnection aDB) : this(aDB, true) {
    }

    public Transaction(IDbConnection aDB, bool aHandle) {
      if (aHandle) {
        _DB = aDB;
        _DBTransaction = _DB.BeginTransaction();
      }
    }

    public void Commit() {
      if (_DB != null) {
        _DBTransaction.Commit();
        _DBTransaction.Dispose();
        _DB = null;
      }
    }

    public void Dispose() {
      if (_DB != null) {
        _DBTransaction.Rollback();
        _DBTransaction.Dispose();
        _DB = null;
      }
    }
}

Conclusion

This class purely adds syntactic sugar to your code and makes performing transactions a little bit easier. The functionality is the same. But when working with transactions and having to manually create this structure each time, the shorter version is a lot easier to type, as well as read later when working in the code again.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Cyprus Cyprus
Chad Z. Hower, a.k.a. Kudzu
"Programming is an art form that fights back"

I am a former Microsoft Regional DPE (MEA) covering 85 countries, former Microsoft Regional Director, and 10 Year Microsoft MVP.

I have lived in Bulgaria, Canada, Cyprus, Switzerland, France, Jordan, Russia, Turkey, The Caribbean, and USA.

Creator of Indy, IntraWeb, COSMOS, X#, CrossTalk, and more.

Comments and Discussions

 
GeneralDifferent implementation of the transaction [modified] Pin
PIEBALDconsult17-Mar-08 11:26
mvePIEBALDconsult17-Mar-08 11:26 
GeneralRe: Different implementation of the transaction Pin
Chad Z. Hower aka Kudzu17-Mar-08 13:31
Chad Z. Hower aka Kudzu17-Mar-08 13:31 
QuestionClose? Pin
Anonymous31-Oct-04 12:39
Anonymous31-Oct-04 12:39 
AnswerRe: Close? Pin
Chad Z. Hower aka Kudzu31-Oct-04 14:12
Chad Z. Hower aka Kudzu31-Oct-04 14:12 
AnswerRe: Close? Pin
Alvaro Mendez27-May-05 9:50
Alvaro Mendez27-May-05 9:50 
GeneralNo need to wrap to shorten the code Pin
Candan Akyol26-Oct-04 9:46
sussCandan Akyol26-Oct-04 9:46 
ADO.NET transactions can work exactly the same way as your transaction class. the IDbTransaction interface (which is the interface for all ADO.NET provider transactions) implement IDisposable, and AFAIK (at least for SqlTransaction), when disposed, they are rolled back if not commited. The following snippet is a pure ADO.NET code that makes it "easier":

<br />
SqlConnection connection = new SqlConnection("server=(local);integrated security=sspi;");<br />
using (SqlTransaction transaction = connection.BeginTransaction())<br />
{<br />
	//  do some work here with commands attached to the transaction<br />
	transaction.Commit();<br />
}<br />
<br />
// no need to catch exceptions and rollback, transaction will be disposed in an exception case and will be rolled back automatically<br />

GeneralRe: No need to wrap to shorten the code Pin
Chad Z. Hower aka Kudzu27-Oct-04 2:18
Chad Z. Hower aka Kudzu27-Oct-04 2:18 
GeneralThe pattern is almost right... Pin
casperOne26-Oct-04 3:06
casperOne26-Oct-04 3:06 
GeneralRe: The pattern is almost right... Pin
Chad Z. Hower aka Kudzu27-Oct-04 2:42
Chad Z. Hower aka Kudzu27-Oct-04 2:42 
QuestionIs shorter always better? Pin
Mark Focas25-Oct-04 12:13
Mark Focas25-Oct-04 12:13 
AnswerRe: Is shorter always better? Pin
Chad Z. Hower aka Kudzu25-Oct-04 12:25
Chad Z. Hower aka Kudzu25-Oct-04 12:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.