A Beginner's Tutorial for Understanding Transactions and TransactionScope in ADO.NET

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public class DataAccess
{
    string connectionString = null;

    public DataAccess(string conString)
    {
        connectionString = conString;
    }

    // This function will return the results of select query in a datatable.
    // The caller will not have to deal with connections and commands.
    public DataTable ExecuteQuery(string CommandName, CommandType cmdType, SqlParameter[] param)
    {
        DataTable table = null;

        using (SqlConnection con = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = cmdType;
                cmd.CommandText = CommandName;
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }

                try
                {
                    con.Open();

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        table = new DataTable();
                        da.Fill(table);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
        return table;
    }
    
    // This version of ExecuteNonQuery will work with connection and transaction
    // which are passed from outside. The callers will have to pass these
    // if they need to use ADO.NET Transactions.
    // Note: Before calling this the caller will have to open the connection and begin the transaction
    //       Also, in case of failure the rollback of transaction and disposal of connection is upto caller.
    public bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] param, SqlConnection con, SqlTransaction trans)
    {
        bool result = false;

        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandType = cmdType;
            cmd.CommandText = CommandName;
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                result = cmd.ExecuteNonQuery() >= 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        return result;
    }

    // This version of ExecuteNonQuery will work without having to pass connection and transaction
    // from outside. The callers will have to use TransactionScope if they need to use this with
    // transactions.
    public bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] param)
    {
        bool result = false;

        using (SqlConnection con = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = cmdType;
                cmd.CommandText = CommandName;
                cmd.Parameters.AddRange(param);

                try
                {
                    con.Open();

                    result = cmd.ExecuteNonQuery() >= 1;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        return result;
    }   
}