Click here to Skip to main content
Click here to Skip to main content

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

, 25 Oct 2004
Rate this:
Please Sign up or sign in to vote.
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.

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.

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

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

Share

About the Author

Chad Z. Hower, a.k.a. Kudzu
"Programming is an art form that fights back"
www.KudzuWorld.com
 
Formerly the Regional Developer Adviser (DPE) for Microsoft Middle East and Africa, he was responsible for 85 countries spanning 4 continents and 10 time zones. Now Chad is a Microsoft MVP.
 
Chad is the chair of several popular open source projects including Indy and Cosmos (C# Open Source Managed Operating System).
 
Chad is the author of the book Indy in Depth and has contributed to several other books on network communications and general programming.
 
Chad has lived in Canada, Cyprus, Switzerland, France, Jordan, Russia, Turkey, and the United States. Chad has visited more than 60 countries, visiting most of them several times.

Comments and Discussions

 
GeneralDifferent implementation of the transaction [modified] PinmemberPIEBALDconsult17-Mar-08 12:26 
GeneralRe: Different implementation of the transaction PinmemberChad Z. Hower aka Kudzu17-Mar-08 14:31 
QuestionClose? PinsussAnonymous31-Oct-04 13:39 
AnswerRe: Close? PinmemberChad Z. Hower aka Kudzu31-Oct-04 15:12 
AnswerRe: Close? PinmemberAlvaro Mendez27-May-05 10:50 
GeneralNo need to wrap to shorten the code PinsussCandan Akyol26-Oct-04 10:46 
GeneralRe: No need to wrap to shorten the code PinmemberChad Z. Hower aka Kudzu27-Oct-04 3:18 
GeneralThe pattern is almost right... PinmembercasperOne26-Oct-04 4:06 
GeneralRe: The pattern is almost right... PinmemberChad Z. Hower aka Kudzu27-Oct-04 3:42 
QuestionIs shorter always better? PinmemberMark Focas25-Oct-04 13:13 
AnswerRe: Is shorter always better? PinmemberChad Z. Hower aka Kudzu25-Oct-04 13:25 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 25 Oct 2004
Article Copyright 2004 by Chad Z. Hower aka Kudzu
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid