Database Helper Class Library to Ease Database Operation

///////////////////////////////////////////////////////////////////////////
// Copyright 2003-2005 Falcon Soon
//
// Author: Soon Chun Boon
// Date: 12 Oct 2003
// Description: 
// Connection Provider class for SQL Server Database connection sharing  
// This class is taken from codes generated by LLBLGen v1.21.2003.712
///////////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace DBHelper.SqlClient
{
	/// <summary>
	/// Provides a <see cref="System.Data.SqlClient.SqlConnection"/> object which can 
	/// be shared among data-access tier objects to provide a way to do ADO.NET transaction 
	/// coding without the hassling with <see cref="System.Data.SqlClient.SqlConnection"/> objects
	/// on a high level.
	/// </summary>
	public class SqlConnectionProvider : IConnectionProvider
	{
		#region Class Member Declarations
			private	SqlConnection		mcnnDBConnection;
			private	bool				mbIsTransactionPending, mbIsDisposed;
			private	SqlTransaction		mtraCurrentTransaction;
			private	ArrayList			malSavePoints;
		#endregion

        /// <summary>
        /// Initializes a new instance of <see cref="DBHelper.SqlClient.SqlConnectionProvider"/>.
        /// </summary>
		public SqlConnectionProvider()
		{
			// Init the class
			InitClass();
		}


		/// <summary>
		/// Implements the IDispose' method Dispose.
		/// </summary>
		public void Dispose()
		{
			Dispose(true);
			GC.SuppressFinalize(this);
		}


		/// <summary>
		/// Implements the Dispose functionality.
		/// </summary>
		protected virtual void Dispose(bool bIsDisposing)
		{
			// Check to see if Dispose has already been called.
			if(!mbIsDisposed)
			{
				if(bIsDisposing)
				{
					// Dispose managed resources.
					if(mtraCurrentTransaction != null)
					{
						mtraCurrentTransaction.Dispose();
						mtraCurrentTransaction = null;
					}
					if(mcnnDBConnection != null)
					{
						// closing the connection will abort (rollback) any pending transactions
						mcnnDBConnection.Close();
						mcnnDBConnection.Dispose();
						mcnnDBConnection = null;
					}
				}
			}
			mbIsDisposed = true;
		}


		/// <summary>
		/// Initializes class members.
		/// </summary>
		private void InitClass()
		{
			// create all the objects and initialize other members.
			mcnnDBConnection = new SqlConnection();
			mbIsDisposed = false;
			mtraCurrentTransaction = null;
			mbIsTransactionPending = false;
			malSavePoints = new ArrayList();
		}


		/// <summary>
		/// Opens the connection object.
		/// </summary>
		/// <returns>True, if succeeded, otherwise an exception is thrown.</returns>
		/// <exception cref="System.InvalidOperationException">
        /// Cannot open a connection without specifying a data source or server.<br/>
        /// or<br/>
        /// The connection is already open.<br/>
		/// </exception>
		/// <exception cref="System.Data.SqlClient.SqlException">
		/// A connection-level error occurred while opening the connection.
		/// </exception>
		public bool OpenConnection()
		{
			try
			{
				if((mcnnDBConnection.State & ConnectionState.Open) > 0)
				{
					// it's already open.
					throw new InvalidOperationException("Connection is already open.");
				}
				mcnnDBConnection.Open();
				mbIsTransactionPending = false;
				return (true);
			}
			catch(Exception ex)
			{
				// bubble exception
				throw ex;
			}
		}

        
        /// <summary>
        /// Starts a new ADO.NET transaction using the open connection object of this class.
        /// </summary>
        /// <returns>True, if transaction is started correctly, otherwise an exception is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// Transaction is already pending.<br/>
        /// or<br/>
        /// Connection is not open.
        /// </exception>
        public bool BeginTransaction()
        {
            try
            {
                if(mbIsTransactionPending)
                {
                    // no nested transactions allowed.
                    throw new InvalidOperationException("Already transaction pending. Nesting not allowed");
                }
                if((mcnnDBConnection.State & ConnectionState.Open) == 0)
                {
                    // no open connection
                    throw new InvalidOperationException("Connection is not open.");
                }
                // begin the transaction and store the transaction object.
                mtraCurrentTransaction = mcnnDBConnection.BeginTransaction();
                mbIsTransactionPending = true;
                malSavePoints.Clear();
                return (true);
            }
            catch(Exception ex)
            {
                // bubble error
                throw ex;
            }
        }

        /// <summary>
        /// Starts a new ADO.NET transaction using the open connection object of this class
        /// with the specified transaction locking behaviour.
        /// </summary>
        /// <param name="enuIso">Transaction locking behaviour for the connection.</param>
        /// <returns>True, if transaction is started correctly, otherwise an exception is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// Transaction is already pending.<br/>
        /// or<br/>
        /// Connection is not open.
        /// </exception>
        public bool BeginTransaction(IsolationLevel enuIso)
        {
            try
            {
                if(mbIsTransactionPending)
                {
                    // no nested transactions allowed.
                    throw new InvalidOperationException("Already transaction pending. Nesting not allowed");
                }
                if((mcnnDBConnection.State & ConnectionState.Open) == 0)
                {
                    // no open connection
                    throw new InvalidOperationException("Connection is not open.");
                }
                // begin the transaction and store the transaction object.
                mtraCurrentTransaction = mcnnDBConnection.BeginTransaction(enuIso);
                mbIsTransactionPending = true;
                malSavePoints.Clear();
                return (true);
            }
            catch(Exception ex)
            {
                // bubble error
                throw ex;
            }
        }

        /// <summary>
        /// Starts a new ADO.NET transaction using the open connection object of this class
        /// with the specified transaction name.
        /// </summary>
        /// <param name="strTransactionName">Name of the transaction to start.</param>
        /// <returns>True, if transaction is started correctly, otherwise an exception is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// Transaction is already pending.<br/>
        /// or<br/>
        /// Connection is not open.
        /// </exception>
        public bool BeginTransaction(string strTransactionName)
        {
            try
            {
                if(mbIsTransactionPending)
                {
                    // no nested transactions allowed.
                    throw new InvalidOperationException("Already transaction pending. Nesting not allowed");
                }
                if((mcnnDBConnection.State & ConnectionState.Open) == 0)
                {
                    // no open connection
                    throw new InvalidOperationException("Connection is not open.");
                }
                // begin the transaction and store the transaction object.
                mtraCurrentTransaction = mcnnDBConnection.BeginTransaction(strTransactionName);
                mbIsTransactionPending = true;
                malSavePoints.Clear();
                return (true);
            }
            catch(Exception ex)
            {
                // bubble error
                throw ex;
            }
        }

		/// <summary>
		/// Starts a new ADO.NET transaction using the open connection object of this class
		/// with the specified transaction locking behaviour and transaction name.
		/// </summary>
		/// <param name="enuIso">Transaction locking behaviour for the connection.</param>
		/// <param name="strTransactionName">Name of the transaction to start.</param>
		/// <returns>True, if transaction is started correctly, otherwise an exception is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// Transaction is already pending.<br/>
        /// or<br/>
        /// Connection is not open.
        /// </exception>
		public bool BeginTransaction(IsolationLevel enuIso, string strTransactionName)
		{
			try
			{
				if(mbIsTransactionPending)
				{
					// no nested transactions allowed.
					throw new InvalidOperationException("Already transaction pending. Nesting not allowed");
				}
				if((mcnnDBConnection.State & ConnectionState.Open) == 0)
				{
					// no open connection
					throw new InvalidOperationException("Connection is not open.");
				}
				// begin the transaction and store the transaction object.
				mtraCurrentTransaction = mcnnDBConnection.BeginTransaction(enuIso, strTransactionName);
				mbIsTransactionPending = true;
				malSavePoints.Clear();
				return (true);
			}
			catch(Exception ex)
			{
				// bubble error
				throw ex;
			}
		}


		/// <summary>
		/// Commits a pending transaction on the open connection object of this class.
		/// </summary>
		/// <returns>True, if commit was succesful, or an exception is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// No transaction pending.<br/>
        /// or<br/>
        /// Connection is not open.
        /// </exception>
        /// <exception cref="System.Exception">
        /// An error occurred while trying to commit the transaction.
        /// </exception>
		public bool CommitTransaction()
		{
			try
			{
				if(!mbIsTransactionPending)
				{
					// no transaction pending
					throw new InvalidOperationException("No transaction pending.");
				}
				if((mcnnDBConnection.State & ConnectionState.Open) == 0)
				{
					// no open connection
					throw new InvalidOperationException("Connection is not open.");
				}
				// commit the transaction
				mtraCurrentTransaction.Commit();
				mbIsTransactionPending = false;
				mtraCurrentTransaction.Dispose();
				mtraCurrentTransaction = null;
				malSavePoints.Clear();
				return (true);
			}
			catch(Exception ex)
			{
				// bubble error
				throw ex;
			}
		}

        
        /// <summary>
        /// Rolls back a pending transaction on the open connection object of this class.
        /// </summary>
        /// <returns>True, if rollback was succesful, or an exception is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// No transaction pending.<br/>
        /// or<br/>
        /// Connection is not open.
        /// </exception>
        /// <exception cref="System.Exception">
        /// An error occurred while trying to rollback the transaction.
        /// </exception>
        public bool RollbackTransaction()
        {
            try
            {
                if(!mbIsTransactionPending)
                {
                    // no transaction pending
                    throw new InvalidOperationException("No transaction pending.");
                }
                if((mcnnDBConnection.State & ConnectionState.Open) == 0)
                {
                    // no open connection
                    throw new InvalidOperationException("Connection is not open.");
                }
                // rollback the transaction
                mtraCurrentTransaction.Rollback();

                mbIsTransactionPending = false;
                mtraCurrentTransaction.Dispose();
                mtraCurrentTransaction = null;
                malSavePoints.Clear();

                return (true);
            }
            catch(Exception ex)
            {
                // bubble error
                throw ex;
            }
        }


		/// <summary>
		/// Rolls back a pending transaction on the open connection object of this class, 
		/// or rolls back to the savepoint with the given name. Savepoints are created with 
		/// <see cref="DBHelper.SqlClient.SqlConnectionProvider.SaveTransaction"/>.
		/// </summary>
		/// <param name="strTransactionToRollback">Name of transaction to roll back. 
		/// Can be name of savepoint</param>
		/// <returns>True, if rollback was succesful, or an exception is thrown.</returns>
		/// <exception cref="System.ArgumentException">
		/// No transaction name is specified.
		/// </exception>
        /// <exception cref="System.InvalidOperationException">
        /// No transaction pending.<br/>
        /// or<br/>
        /// Connection is not open.
        /// </exception>
        /// <exception cref="System.Exception">
        /// An error occurred while trying to rollback the transaction.
        /// </exception>
		public bool RollbackTransaction(string strTransactionToRollback)
		{
			try
			{
				if(!mbIsTransactionPending)
				{
					// no transaction pending
					throw new InvalidOperationException("No transaction pending.");
				}
				if((mcnnDBConnection.State & ConnectionState.Open) == 0)
				{
					// no open connection
					throw new InvalidOperationException("Connection is not open.");
				}
				// rollback the transaction
				mtraCurrentTransaction.Rollback(strTransactionToRollback);
				// if this wasn't a savepoint, we've rolled back the complete transaction, so we
				// can clean it up.
                if(!malSavePoints.Contains(strTransactionToRollback))
                {
                    // it's not a savepoint
                    mbIsTransactionPending = false;
                    mtraCurrentTransaction.Dispose();
                    mtraCurrentTransaction = null;
                    malSavePoints.Clear();
                }
                else
                {
                    malSavePoints.Remove(strTransactionToRollback);
                }
				return (true);
			}
			catch(Exception ex)
			{
				// bubble error
				throw ex;
			}
		}


		/// <summary>
		/// Saves a pending transaction on the open connection object of this class to a 'savepoint'
		/// with the given name.
		/// When a rollback is issued, the caller can rollback to this savepoint or 
		/// roll back the complete transaction.
		/// </summary>
		/// <param name="strSavePointName">Name of the savepoint to store the current transaction under.</param>
		/// <returns>True, if save was succesful, or an exception is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// No transaction pending.<br/>
        /// or<br/>
        /// Connection is not open.
        /// </exception>
        /// <exception cref="System.Exception">
        /// An error occurred while trying to save the transaction.
        /// </exception>
		public bool SaveTransaction(string strSavePointName)
		{
			try
			{
				if(!mbIsTransactionPending)
				{
					// no transaction pending
					throw new InvalidOperationException("No transaction pending.");
				}
				if((mcnnDBConnection.State & ConnectionState.Open) == 0)
				{
					// no open connection
					throw new InvalidOperationException("Connection is not open.");
				}
				// save the transaction
				mtraCurrentTransaction.Save(strSavePointName);
				// Store the savepoint in the list.
				malSavePoints.Add(strSavePointName);
				return (true);
			}
			catch(Exception ex)
			{
				// bubble error
				throw ex;
			}
		}


        /// <summary>
        /// Closes the open connection. A pending transaction is aborted. 
        /// </summary>
        /// <returns>True, if close was succesful, False if connection was already closed, 
        /// or an exception is thrown when an error occurs</returns>
        /// <exception cref="System.Data.SqlClient.SqlException">
        /// A connection-level error occurred while closing the connection.
        /// </exception>
        public bool CloseConnection()
        {
            return (CloseConnection(false));
        }


		/// <summary>
		/// Closes the open connection. Depending on <i>bCommitPendingTransactions</i>, a pending
		/// transaction is commited, or aborted. 
		/// </summary>
		/// <param name="bCommitPendingTransaction">Flag for what to do when a transaction is still pending. 
		/// True will commit the current transaction, false will abort (rollback) the complete 
		/// current transaction.</param>
		/// <returns>True, if close was succesful, False if connection was already closed, or 
		/// an exception is thrown when an error occurs</returns>
        /// <exception cref="System.Data.SqlClient.SqlException">
        /// A connection-level error occurred while closing the connection.
        /// </exception>
		public bool CloseConnection(bool bCommitPendingTransaction)
		{
			try
			{
				if((mcnnDBConnection.State & ConnectionState.Open) == 0)
				{
					// no open connection
					return (false);
				}
				if(mbIsTransactionPending)
				{
					if(bCommitPendingTransaction)
					{
						// commit the pending transaction
						mtraCurrentTransaction.Commit();
					}
					else
					{
						// rollback the pending transaction
						mtraCurrentTransaction.Rollback();
					}
					mbIsTransactionPending = false;
					mtraCurrentTransaction.Dispose();
					mtraCurrentTransaction = null;
					malSavePoints.Clear();
				}
				// close the connection
				mcnnDBConnection.Close();
				return (true);
			}
			catch(Exception ex)
			{
				// bubble error
				throw ex;
			}
		}


		#region Class Property Declarations
        /// <summary>
        /// Returns current transaction.
        /// </summary>
		public SqlTransaction CurrentTransaction
		{
			get
			{
				return (mtraCurrentTransaction);
			}
		}

        /// <summary>
        /// Returns a flag specifies whether transaction is pending.
        /// </summary>
		public bool IsTransactionPending
		{
			get
			{
				return (mbIsTransactionPending);
			}
		}

        /// <summary>
        /// Returns connection object.
        /// </summary>
		public IDbConnection DBConnection
		{
			get
			{
				return (mcnnDBConnection);
			}
		}

        /// <summary>
        /// Sets the connection string.
        /// </summary>
		public string ConnectionString
		{
			set
			{
				mcnnDBConnection.ConnectionString = value;
			}
		}
		#endregion
	}
}