Is an ADO.NET 2.0 Transaction on SQL Server 2005 automatically rolled back on exception?

Question

If we begin a transaction, then do some db action, say insert, and an exception occurs, is the transaction automatically rolled back if I don't call transaction.rollback() in the catch/exception handler?

Even if the transaction is rolled back, does that result in a memory leak, i.e. the transaction object and the connection object hang around in memory in their original state until the garbage collector kicks in?

Answer 1

The transaction will be rolled back if it is disposed without being committed. So as long as you do a using() to begin your transaction, or have a finally block which calls Dispose(), it will be automatically rolled back in the case of an exception.

If you do not dispose the transaction/connection, it will eventually be reclaimed by the garbage collector, but will hang around in memory until then. (In fact the managed SqlTransaction object will hang around in memory until the GC kicks in anyway; but disposal ensures early cleanup of the unmanaged transaction/connection resource, freeing up the server-side resources and freeing up the connection for reuse.)

Answer 2

CREATE TABLE [Friend]

( [ID] [int] IDENTITY(1,1) NOT NULL, [FullName] varchar NOT NULL, [Phone] varchar NULL, )

CLIENT

namespace Client

{ class Program { static void Main(string[] args) { DaContract Impl = new Impl(); Impl.AddFriend(new Friend("Someone", "100")); Impl.AddFriend(new Friend("Someone else")); Console.ReadLine(); } } }

SERVER

using System;

using MyLib.DataAccess; using System.Data;

namespace TestTransactionAndConnectionStateOnException { public class Friend { public string FullName; public string Phone;

    public Friend(string fullName): this(fullName, null) {}
    public Friend(string fullName, string phone)
    {
        this.FullName = fullName;
        this.Phone = phone;
    }
}

public interface DaContract
{
    int AddFriend( Friend f );

    int UpdatePhone(string fullName, string phone);
}

public class Impl: DaContract
{
    Mediator _m;
    public Impl() { this._m = new Mediator(); }

    public int AddFriend( Friend f )
    {
        int ret = 0;

        try
        {
            ret = this._m.AddFriend( f );
        }
        catch(Exception ex)
        {
            HandleException(ex);
        }

        return ret;
    }

    public int UpdatePhone(string fullName, string phone)
    {
        int ret = 0;

        try
        {
            ret = this._m.UpdatePhone(fullName, phone);
        }
        catch(Exception ex)
        {
            HandleException(ex);
        }

        return ret;
    }

    public void HandleException(Exception ex)
    {
        /* see the transaction state */

        /* see connection state */

        /* do nothing and let the client call another method to initiate a new
         * transaction and a new connection */

    }
}

public class Mediator
{
    private string _connectionString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=MyFriends";
    private Manager _m = new Manager();

    public int AddFriend( Friend f )
    {
        int ret = 0;
        using (ISession session = SessionFactory.Create(SessionType.Ado, this._connectionString))
        {
            session.BeginTransaction();
            ret = this._m.AddFriend(f, session);
            session.CommitTransaction();
        }

        return ret;
    }

    public int UpdatePhone(string fullName, string phone)
    {
        int ret = 0;
        using (ISession session = SessionFactory.Create(SessionType.Ado, this._connectionString))
        {
            session.BeginTransaction();
            ret = this._m.UpdateFriend(fullName, phone, session);
            session.CommitTransaction();
        }

        return ret;
    }
}

public class Manager
{
    public int AddFriend(Friend f, ISession session) { return Handler.Instance.AddFriend(f, session); }
    public int UpdateFriend(string fullName, string phone, ISession session) { return Handler.Instance.UpdatePhone(fullName, phone, session); }
}

public class Handler
{
    private static Handler _handler = null;
    private Handler() {}
    public static Handler Instance
    { 
        get
        {
            if (_handler == null)
                _handler = new Handler();

            return _handler;
        }
    }

    public int AddFriend( Friend f, ISession session )
    {
        /* check session, transaction and connection states here */
        Console.WriteLine(session.Connection.State.ToString());

        int ret = 0;

        /* Add the friend */
        IDbCommand command = session.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = string.Format("INSERT INTO Friend(FullName, Phone) values('{0}', '{1}')", f.FullName, f.Phone);
        ret = command.ExecuteNonQuery();

        /* throw an exception just for the heck of it (don't dispose off ISession yet) */
        if (string.Compare(f.FullName, "Someone", true) == 0)
            throw new Exception("Fake exception. Friend value can't be 'Someone'");

        return ret;
    }

    public int UpdatePhone(string fullName, string phone, ISession session )
    {
        return 0;
    }

}

}

I couldn't post the code in the comments section due to the word limit and because it screwed up all the formatting.