Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Database Helper Class Library to Ease Database Operation

, 14 Apr 2007 CPOL
Database Helper Class Library to Ease Database Operation
article_demo.zip
DBHelperQuickRelationshipSamples
DBHelperQuickRelationshipSamples
App.ico
DBHelperQuickRelationshipSamples.csproj.user
DBHelperQuickRelationshipSamples.suo
DBHelperQuickSelectSamples
DBHelperQuickSelectSamples
DBHelperQuickSelectSamples.csproj.user
DBHelperQuickSelectSamples.suo
DBHelperQuickHelperSamples
DBHelperQuickHelperSamples
App.ico
DBHelperQuickHelperSamples.csproj.user
DBHelperQuickHelperSamples.suo
DBHelperQuickInsUpdDelSamples
DBHelperQuickInsUpdDelSamples
DBHelperQuickInsUpdDelSamples.csproj.user
DBHelperQuickInsUpdDelSamples.suo
article_src.zip
Microsoft.ApplicationBlocks.ExceptionManagement.Interfaces
Microsoft.ApplicationBlocks.ExceptionManagement.Interfaces.csproj.user
Microsoft.ApplicationBlocks.ExceptionManagement.suo
DbHelper
DBHelper.chm
DBHelper.csproj.user
DbHelper.ndoc
DBHelper.suo
Microsoft.ApplicationBlocks.ExceptionManagement
ExceptionManagerText.xsx
Microsoft.ApplicationBlocks.ExceptionManagement.csproj.user
Microsoft.ApplicationBlocks.suo
///////////////////////////////////////////////////////////////////////////
// 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
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

falconsoon

Malaysia Malaysia
Had worked as analyst programmer for 4 years. Now helping in family business but still involved actively in .Net development whenever there is a free time.

| Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 14 Apr 2007
Article Copyright 2007 by falconsoon
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid