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
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: 31 May 2004
// Description: 
// Class that provides functions that encapsulate high performance, scalable 
// best practices for common uses of SqlClient.
// This class is created based on SqlHelper.cs from Microsoft Data Access 
// Application Block version 2.
///////////////////////////////////////////////////////////////////////////

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

namespace DBHelper.SqlClient
{
	/// <summary>
	/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for 
	/// common uses of SqlClient.
	/// </summary>
	/// <example>
	/// <code>
    /// objSqlHelper.MainConnectionProvider = mobjCnnProvider;
    /// mobjCnnProvider.OpenConnection();
    /// ds = objSqlHelper.ExecuteDataSet("GetOrders", 24, 36);
    /// mobjCnnProvider.CloseConnection();  
    /// iRetVal = objSqlHelper.ReturnValue;
    /// hstOutput = objSqlHelper.OutputValue;
    /// iAffected = objSqlHelper.RowsAffected;
	/// </code>
	/// </example>
    public sealed class SqlHelper : SqlDBInteractionBase
    {
        #region Class Member Declarations
        private int miRetVal;
        private Hashtable mhstOutput;
        #endregion Class Member Declarations

        #region ExecuteNonQuery

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns no resultset) 
        /// against the specified <see cref="System.Data.SqlClient.SqlConnection"/> and specified 
        /// <see cref="System.Data.SqlClient.SqlTransaction"/> (if provided) using the 
        /// provided parameters.
        /// </summary>
        /// <param name="cnn">A valid SqlConnection</param>
        /// <param name="tra">A valid SqlTransaction</param>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <param name="aCommandPar">An array of SqlParamters used to execute the command.</param>
        /// <returns>An int representing the number of rows affected by the command.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cnn</i> parameter is not provided.
        /// </exception>
        /// <example>
        /// <code>
        /// int result = ExecuteNonQuery(cnn, tra, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </code>
        /// </example>
        private int ExecuteNonQuery(SqlConnection cnn, SqlTransaction tra, CommandType enuCommandType, 
            string strCommandText, params SqlParameter[] aCommandPar)
        {	
            if (cnn == null) throw new ArgumentNullException("cnn");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool bMustCloseConnection = false;
            int iAffectedRows;

            try
            {
                SqlUtils.PrepareCommand(cmd, cnn, tra, enuCommandType, strCommandText, aCommandPar, out bMustCloseConnection);
    		
                // Finally, execute the command
                iAffectedRows  = cmd.ExecuteNonQuery();
                
                miRetVal = SqlUtils.GetStoredProcedureReturnValue(cmd);
                mhstOutput = SqlUtils.GetOutputParameterValues(cmd);
            }
            finally
            {
                // Detach the SqlParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

                if (bMustCloseConnection)
                {
                    cnn.Close();
                }
            }

            return (iAffectedRows);
        }

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns no resultset 
        /// and takes no parameters). 
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <returns>An int representing the number of rows affected by the command.</returns>
        /// <example>
        /// <code>
        /// int result = ExecuteNonQuery(CommandType.StoredProcedure, "PublishOrders");
        /// </code>
        /// </example>
        public int ExecuteNonQuery(CommandType enuCommandType, string strCommandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return (ExecuteNonQuery(enuCommandType, strCommandText, (SqlParameter[])null));
        }

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns no resultset) 
        /// using the provided parameters. 
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <param name="aCommandPar">An array of SqlParamters used to execute the command.</param>
        /// <returns>An int representing the number of rows affected by the command.</returns>
        /// <example>
        /// <code>
        /// int result = ExecuteNonQuery(CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </code>
        /// </example>
        public int ExecuteNonQuery(CommandType enuCommandType, string strCommandText, params SqlParameter[] aCommandPar)
        {
            SqlTransaction tra = null;

            miErrorCode = (int)DBError.AllOk;
            miRowsAffected = 0;
            miRetVal = 0;
            mhstOutput = null;

            try
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                }
                else
                {
                    if (mobjMainConnectionProvider.IsTransactionPending)
                    {
                        tra = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }

                miRowsAffected = ExecuteNonQuery(mcnnMainConnection, tra, enuCommandType, strCommandText, aCommandPar);

                return (miRowsAffected);
            }
            catch (Exception ex)
            {
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
            finally
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
            }
        }
        
        /// <summary>
        /// Execute a stored procedure via a <see cref="System.Data.SqlClient.SqlCommand"/> 
        /// (that returns no resultset)using the provided parameter values. This method 
        /// will query the database to discover the parameters for the stored procedure 
        /// (the first time each stored procedure is called), and assign the values based 
        /// on parameter order.
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="aobjParValues">An array of objects to be assigned as the input values 
        /// of the stored procedure.</param>
        /// <returns>An int representing the number of rows affected by the command.</returns>
        /// <example>
        /// <code>
        /// int result = ExecuteNonQuery("PublishOrders", 24, 36);
        /// </code>
        /// </example>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Total of provided parameters' value is not same with total of input/inputouput parameters.
        /// </exception>
        public int ExecuteNonQuery(string strSpName, params object[] aobjParValues)
        {
            SqlParameter[] aCommandPar;

			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName, true);

            // If we receive parameter values, we need to figure out where they go
            if ((aobjParValues != null) && (aobjParValues.Length > 0)) 
            {              
                // Assign the provided values to these parameters based on parameter order
                SqlUtils.AssignParameterValues(aCommandPar, aobjParValues);              
            }

            // Call the overload that takes an array of SqlParameters
            return (ExecuteNonQuery(CommandType.StoredProcedure, strSpName, aCommandPar));
        }

        #endregion ExecuteNonQuery

        #region ExecuteDataset

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a resultset) 
        /// against the specified <see cref="System.Data.SqlClient.SqlConnection"/> and specified 
        /// <see cref="System.Data.SqlClient.SqlTransaction"/> (if provided) using the 
        /// provided parameters.
        /// </summary>
        /// <param name="cnn">A valid SqlConnection.</param>
        /// <param name="tra">A valid SqlTransaction.</param>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <param name="aCommandPar">An array of SqlParamters used to execute the command.</param>
        /// <returns>A dataset containing the resultset generated by the command.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cnn</i> parameter is not provided.
        /// </exception>
        /// <example>
        /// <code>
        /// DataSet ds = ExecuteDataset(cnn, tra, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </code>
        /// </example>
        private DataSet ExecuteDataset(SqlConnection cnn, SqlTransaction tra, CommandType enuCommandType, 
            string strCommandText, params SqlParameter[] aCommandPar)
        {
            if (cnn == null) throw new ArgumentNullException("cnn");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool bMustCloseConnection = false;
            DataSet ds;

            try
            {
                SqlUtils.PrepareCommand(cmd, cnn, tra, enuCommandType, strCommandText, aCommandPar, out bMustCloseConnection);
    			
                // Create the DataAdapter & DataSet
                using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                {
                    ds = new DataSet();

                    // Fill the DataSet using default values for DataTable names, etc
                    adp.Fill(ds);

                    miRetVal = SqlUtils.GetStoredProcedureReturnValue(cmd);
                    mhstOutput = SqlUtils.GetOutputParameterValues(cmd);

                    // Return the dataset
                    return (ds);
                }	
            }
            finally
            {
                // Detach the SqlParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

                if (bMustCloseConnection)
                {
                    cnn.Close();
                }
            }
        }

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a resultset 
        /// and takes no parameters). 
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <returns>A dataset containing the resultset generated by the command.</returns>
        /// <example>
        /// <code>
        /// DataSet ds = ExecuteDataset(CommandType.StoredProcedure, "GetOrders");
        /// </code>
        /// </example>
        public DataSet ExecuteDataset(CommandType enuCommandType, string strCommandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteDataset(enuCommandType, strCommandText, (SqlParameter[])null);
        }
		
        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a resultset) 
        /// using the provided parameters. 
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <param name="aCommandPar">An array of SqlParamters used to execute the command.</param>
        /// <returns>A dataset containing the resultset generated by the command.</returns>
        /// <example>
        /// <code>
        /// DataSet ds = ExecuteDataset(CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </code>
        /// </example>
        public DataSet ExecuteDataset(CommandType enuCommandType, string strCommandText, params SqlParameter[] aCommandPar)
        {
            SqlTransaction tra = null;

            miErrorCode = (int)DBError.AllOk;
            miRowsAffected = 0;
            miRetVal = 0;
            mhstOutput = null;
            DataSet ds;

            try
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                }
                else
                {
                    if (mobjMainConnectionProvider.IsTransactionPending)
                    {
                        tra = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }

                ds = ExecuteDataset(mcnnMainConnection, tra, enuCommandType, strCommandText, aCommandPar);

                return (ds);
            }
            catch (Exception ex)
            {
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
            finally
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
            }
        }
        		
        /// <summary>
        /// Execute a stored procedure via a <see cref="System.Data.SqlClient.SqlCommand"/> 
        /// (that returns a resultset) using the provided parameter values. This method will 
        /// query the database to discover the parameters for the stored procedure (the first 
        /// time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="aobjParValues">An array of objects to be assigned 
        /// as the input values of the stored procedure.</param>
        /// <returns>A dataset containing the resultset generated by the command.</returns>
        /// <example>
        /// <code>
        /// DataSet ds = ExecuteDataset("GetOrders", 24, 36);
        /// </code>
        /// </example>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Total of provided parameters' value is not same with total of input/inputouput parameters.
        /// </exception>
        public DataSet ExecuteDataset(string strSpName, params object[] aobjParValues)
        {
            SqlParameter[] aCommandPar;

			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName, true);

            // If we receive parameter values, we need to figure out where they go
            if ((aobjParValues != null) && (aobjParValues.Length > 0)) 
            {              
                // Assign the provided values to these parameters based on parameter order
                SqlUtils.AssignParameterValues(aCommandPar, aobjParValues);              
            }

            // Call the overload that takes an array of SqlParameters
            return (ExecuteDataset(CommandType.StoredProcedure, strSpName, aCommandPar));
        }
        
        #endregion ExecuteDataset
		
        #region ExecuteReader

        /// <summary>
        /// This enum is used to indicate whether the connection was provided by the caller, 
        /// or created by SqlHelper, so that we can set the appropriate CommandBehavior 
        /// when calling ExecuteReader().
        /// </summary>
        private enum SqlConnectionOwnership	
        {
            /// <summary>Connection is owned and managed by SqlHelper.</summary>
            Internal, 
            /// <summary>Connection is owned and managed by the caller.</summary>
            External
        }

        /// <summary>
        /// Create and prepare a <see cref="System.Data.SqlClient.SqlCommand"/>, and call 
        /// ExecuteReader with the appropriate CommandBehavior.
        /// </summary>
        /// <remarks>
        /// If we created and opened the connection, we want the connection to be closed 
        /// when the DataReader is closed.<br/>
        /// 
        /// If the caller provided the connection, we want to leave it to them to manage.
        /// </remarks>
        /// <param name="cnn">A valid SqlConnection, on which to execute this command.</param>
        /// <param name="tra">A valid SqlTransaction, or 'null'.</param>
        /// <param name="enuCommandType">The enuCommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <param name="aCommandPar">An array of SqlParameters to be associated with the command or 
        /// 'null' if no parameters are required.</param>
        /// <param name="enuCnnOwnership">Indicates whether the <i>cnn</i> parameter was provided by the 
        /// caller, or created by SqlHelper.</param>
        /// <returns><see cref="System.Data.SqlClient.SqlDataReader"/> containing the results 
        /// of the command.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cnn</i> parameter is not provided.
        /// </exception>
        /// <example>
        /// <code>
        /// SqlDataReader dtr = ExecuteReader(cnn, tra, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24), SqlConnectionOwnership.Internal);
        /// </code>
        /// </example>
        private SqlDataReader ExecuteReader(SqlConnection cnn, SqlTransaction tra, CommandType enuCommandType, string strCommandText, SqlParameter[] aCommandPar, SqlConnectionOwnership enuCnnOwnership)
        {	
			if (cnn == null) throw new ArgumentNullException("cnn");

			bool bMustCloseConnection = false;
            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
			try
			{
				SqlUtils.PrepareCommand(cmd, cnn, tra, enuCommandType, strCommandText, aCommandPar, out bMustCloseConnection);
			
				// Create a reader
				SqlDataReader dtr;

				// Call ExecuteReader with the appropriate CommandBehavior
				if (enuCnnOwnership == SqlConnectionOwnership.External)
				{
					dtr = cmd.ExecuteReader();
				}
				else
				{
					dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				}
						
				return (dtr);
			}
			finally
			{
                // Detach the SqlParameters from the command object, so they can be used again.
                // HACK: There is a problem here, the output parameter values are fetched 
                // when the reader is closed, so if the parameters are detached from the command
                // then the SqlReader can't set its values. 
                // When this happen, the parameters can't be used again in other command.
                bool bCanClear = true;
                foreach(SqlParameter commandParameter in cmd.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                    {
                        bCanClear = false;
                    }
                }
            
                if (bCanClear)
                {
                    cmd.Parameters.Clear();
                }

                // If provided connection is external and not opened, returned SqlDataReader is corrupted
                // as the connection would be closed before the SqlDataReader is used.
                if (bMustCloseConnection)
                {
                    cnn.Close();
                }
			}
        }
        
        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a resultset 
        /// and takes no parameters). 
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="enuCommandType">The enuCommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <returns>A <see cref="System.Data.SqlClient.SqlDataReader"/>containing 
        /// the resultset generated by the command.</returns>
        /// <example>
        /// <code>
        /// SqlDataReader dtr = ExecuteReader(CommandType.StoredProcedure, "GetOrders");
        /// </code>
        /// </example>
        public SqlDataReader ExecuteReader(CommandType enuCommandType, string strCommandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteReader(enuCommandType, strCommandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a resultset) 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="enuCommandType">The enuCommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <param name="aCommandPar">An array of SqlParamters used to execute the command.</param>
        /// <returns>A <see cref="System.Data.SqlClient.SqlDataReader"/> containing the resultset 
        /// generated by the command.</returns>
        /// <example>
        /// <code>
        /// SqlDataReader dtr = ExecuteReader(CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </code>
        /// </example>
        public SqlDataReader ExecuteReader(CommandType enuCommandType, string strCommandText, params SqlParameter[] aCommandPar)
        {
            SqlTransaction tra = null;

            miErrorCode = (int)DBError.AllOk;
            miRowsAffected = 0;
            miRetVal = 0;
            mhstOutput = null;
            SqlConnectionOwnership enuCnnOwnership;

            try
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    enuCnnOwnership = SqlConnectionOwnership.Internal;
                    mcnnMainConnection.Open();
                }
                else
                {
                    enuCnnOwnership = SqlConnectionOwnership.External;
                    if (mobjMainConnectionProvider.IsTransactionPending)
                    {
                        tra = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }

                // Pass through the call to the private overload
                return (ExecuteReader(mcnnMainConnection, tra, enuCommandType, strCommandText, 
                    aCommandPar, enuCnnOwnership));
            }
            catch (Exception ex)
            {
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
                throw ex;
            }
        }

        /// <summary>
        /// Execute a stored procedure via a <see cref="System.Data.SqlClient.SqlCommand"/> 
        /// (that returns a resultset) using the provided parameter values.
        /// This method will query the database to discover the parameters for the stored procedure 
        /// (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection. Besides, this method provides no access to output parameters 
        /// or the stored procedure's return value parameter.
        /// </remarks>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="aobjParValues">An array of objects to be assigned as the input values of the
        ///  stored procedure.</param>
        /// <returns>A <see cref="System.Data.SqlClient.SqlDataReader"/> containing the resultset 
        /// generated by the command.</returns>
        /// <example>
        /// <code>
        /// SqlDataReader dtr = ExecuteReader("GetOrders", 24, 36);
        /// </code>
        /// </example>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Total of provided parameters' value is not same with total of input/inputouput parameters.
        /// </exception>
        public SqlDataReader ExecuteReader(string strSpName, params object[] aobjParValues)
        {
			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            // If we receive parameter values, we need to figure out where they go
            if ((aobjParValues != null) && (aobjParValues.Length > 0)) 
            {
                SqlParameter[] aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName);

                SqlUtils.AssignParameterValues(aCommandPar, aobjParValues);

                return ExecuteReader(CommandType.StoredProcedure, strSpName, aCommandPar);
            }
            else 
            {
				// Otherwise we can just call the SP without params
                return ExecuteReader(CommandType.StoredProcedure, strSpName);
            }
        }

        #endregion ExecuteReader

        #region ExecuteScalar
		
        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a 1x1 resultset) 
        /// against the specified <see cref="System.Data.SqlClient.SqlConnection"/> 
        /// and specified <see cref="System.Data.SqlClient.SqlTransaction"/> (if provided) 
        /// using the provided parameters.
        /// </summary>
        /// <param name="cnn">A valid SqlConnection.</param>
        /// <param name="tra">A valid SqlTransaction.</param>
        /// <param name="enuCommandType">The enuCommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <param name="aCommandPar">An array of SqlParamters used to execute the command.</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cnn</i> parameter is not provided.
        /// </exception>
        /// <example>
        /// <code>
        /// int orderCount = (int)ExecuteScalar(cnn, tra, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
        /// </code>
        /// </example>
        private object ExecuteScalar(SqlConnection cnn, SqlTransaction tra, CommandType enuCommandType, 
            string strCommandText, params SqlParameter[] aCommandPar)
        {
            if (cnn == null) throw new ArgumentNullException("cnn");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool bMustCloseConnection = false;

            try
            {
                SqlUtils.PrepareCommand(cmd, cnn, tra, enuCommandType, strCommandText, aCommandPar, out bMustCloseConnection);
    			
                // Execute the command & return the results
                object objRetVal = cmd.ExecuteScalar();
                miRetVal = SqlUtils.GetStoredProcedureReturnValue(cmd);
                mhstOutput = SqlUtils.GetOutputParameterValues(cmd);

                return (objRetVal);
            }
            finally
            {
                // Detach the SqlParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

                if (bMustCloseConnection)
                {
                    cnn.Close();
                }
            }		
        }

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a 1x1 resultset 
        /// and takes no parameters). 
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="enuCommandType">The enuCommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command.</returns>
        /// <example>
        /// <code>
        /// int orderCount = (int)ExecuteScalar(CommandType.StoredProcedure, "GetOrderCount");
        /// </code>
        /// </example> 
        public object ExecuteScalar(CommandType enuCommandType, string strCommandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return (ExecuteScalar(enuCommandType, strCommandText, (SqlParameter[])null));
        }       

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a 1x1 resultset) 
        /// using the provided parameters. 
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <param name="aCommandPar">An array of SqlParamters used to execute the command.</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command.</returns>
        /// <example>
        /// <code>
        /// int orderCount = (int)ExecuteScalar(CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
        /// </code>
        /// </example>
        public object ExecuteScalar(CommandType enuCommandType, string strCommandText, params SqlParameter[] aCommandPar)
        {
            SqlTransaction tra = null;

            miErrorCode = (int)DBError.AllOk;
            miRowsAffected = 0;
            miRetVal = 0;
            mhstOutput = null;
            object objRetVal;

            try
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                }
                else
                {
                    if (mobjMainConnectionProvider.IsTransactionPending)
                    {
                        tra = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }

                objRetVal = ExecuteScalar(mcnnMainConnection, tra, enuCommandType, strCommandText, aCommandPar);

                return (objRetVal);
            }
            catch (Exception ex)
            {
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
            finally
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
            }
        }

        /// <summary>
        /// Execute a stored procedure via a <see cref="System.Data.SqlClient.SqlCommand"/>
        /// (that returns a 1x1 resultset) using the provided parameter values.
        /// This method will query the database to discover the parameters for the stored procedure 
        /// (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="aobjParValues">An array of objects to be assigned as the input values 
        /// of the stored procedure.</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command.</returns>
        /// <example>
        /// <code>
        /// int orderCount = (int)ExecuteScalar("GetOrderCount", 24, 36);
        /// </code>
        /// </example>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Total of provided parameters' value is not same with total of input/inputouput parameters.
        /// </exception>
        public object ExecuteScalar(string strSpName, params object[] aobjParValues)
        {
            SqlParameter[] aCommandPar;

			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName, true);

            // If we receive parameter values, we need to figure out where they go
            if ((aobjParValues != null) && (aobjParValues.Length > 0)) 
            {              
                // Assign the provided values to these parameters based on parameter order
                SqlUtils.AssignParameterValues(aCommandPar, aobjParValues);              
            }

            return (ExecuteScalar(CommandType.StoredProcedure, strSpName, aCommandPar));
        }
        
        #endregion ExecuteScalar	

        #region ExecuteXmlReader

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a resultset) 
        /// against the specified <see cref="System.Data.SqlClient.SqlConnection "/>
        /// and specified <see cref="System.Data.SqlClient.SqlTransaction"/> (if provided)
        /// using the provided parameters.
        /// </summary>
        /// <param name="cnn">A valid SqlConnection.</param>
        /// <param name="tra">A valid SqlTransaction.</param>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command using "FOR XML AUTO".</param>
        /// <param name="aCommandPar">An array of SqlParamters used to execute the command.</param>
        /// <returns>An <see cref="System.Xml.XmlReader"/>XmlReader containing the resultset 
        /// generated by the command.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cnn</i> parameter is not provided.
        /// </exception>
        /// <example>
        /// <code>
        /// XmlReader xmlr = ExecuteXmlReader(cnn, tra, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </code>
        /// </example>
        private XmlReader ExecuteXmlReader(SqlConnection cnn, SqlTransaction tra, CommandType enuCommandType, 
            string strCommandText, params SqlParameter[] aCommandPar)
        {
            if (cnn == null) throw new ArgumentNullException("cnn");

            bool bMustCloseConnection = false;
            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            try
            {
                SqlUtils.PrepareCommand(cmd, cnn, tra, enuCommandType, strCommandText, aCommandPar, out bMustCloseConnection );
			
                // Create the DataAdapter & DataSet
                XmlReader xmlrRetVal = cmd.ExecuteXmlReader();
			
                return (xmlrRetVal);
            }
            finally
            {	
                // Detach the SqlParameters from the command object, so they can be used again.
                // HACK: There is a problem here, the output parameter values are fetched 
                // when the reader is closed, so if the parameters are detached from the command
                // then the SqlReader can't set its values. 
                // When this happen, the parameters can't be used again in other command.
                bool bCanClear = true;
                foreach(SqlParameter commandParameter in cmd.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                    {
                        bCanClear = false;
                    }
                }
            
                if (bCanClear)
                {
                    cmd.Parameters.Clear();
                }

                // If provided connection is external and not opened, returned SqlDataReader is corrupted
                // as the connection would be closed before the SqlDataReader is used.
                if (bMustCloseConnection)
                {
                    cnn.Close();
                }
            }
        }

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a resultset 
        /// and takes no parameters). 
        /// </summary>
        /// <remarks>
        /// This method can be called by using <see cref="System.Data.SqlClient.SqlConnection"/> 
        /// provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> only, i.e. external
        /// connection.
        /// </remarks>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command using "FOR XML AUTO".</param>
        /// <returns>An <see cref="System.Xml.XmlReader"/> containing the resultset generated 
        /// by the command.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// This method is called with internal connection.
        /// </exception>
        /// <example>
        /// <code>
        /// XmlReader xmlr = ExecuteXmlReader(CommandType.StoredProcedure, "GetOrders");
        /// </code>
        /// </example>
        public XmlReader ExecuteXmlReader(CommandType enuCommandType, string strCommandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return (ExecuteXmlReader(enuCommandType, strCommandText, (SqlParameter[])null));
        }

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a resultset) 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// This method can be called by using <see cref="System.Data.SqlClient.SqlConnection"/> 
        /// provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> only, i.e. external
        /// connection.
        /// </remarks>
        /// <param name="enuCommandType">The enuCommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <param name="aCommandPar">An array of SqlParamters used to execute the command.</param>
        /// <returns>An <see cref="System.Xml.XmlReader"/>containing the resultset generated 
        /// by the command.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// This method is called with internal connection.
        /// </exception>
        /// <example>
        /// <code>
        /// XmlReader xmlr = ExecuteXmlReader(CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </code>
        /// </example>
        public XmlReader ExecuteXmlReader(CommandType enuCommandType, string strCommandText, params SqlParameter[] aCommandPar)
        {
            SqlTransaction tra = null;

            miErrorCode = (int)DBError.AllOk;
            miRowsAffected = 0;
            miRetVal = 0;
            mhstOutput = null;

            try
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    // invalid if internal connection is used
                    throw new InvalidOperationException("ExecuteXmlReader can be called only when SqlConnectionProvider is used");
                }
                else
                {
                    if (mobjMainConnectionProvider.IsTransactionPending)
                    {
                        tra = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }

                // Pass through the call to the private overload using an externally owned connection
                return (ExecuteXmlReader(mcnnMainConnection, tra, enuCommandType, strCommandText, aCommandPar));
            }
            catch (Exception ex)
            {
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
        }

        /// <summary>
        /// Execute a stored procedure via a <see cref="System.Data.SqlClient.SqlCommand"/>
        /// (that returns a resultset) using the provided parameter values.
        /// This method will query the database to discover the parameters for the stored procedure 
        /// (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method can be called by using <see cref="System.Data.SqlClient.SqlConnection"/> 
        /// provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> only, i.e. external
        /// connection. Besides, this method provides no access to output parameters or the stored 
        /// procedure's return value parameter.
        /// </remarks>
        /// <param name="strSpName">The name of the stored procedure using "FOR XML AUTO".</param>
        /// <param name="aobjParValues">An array of objects to be assigned as the input values 
        /// of the stored procedure</param>
        /// <returns>An <see cref="System.Xml.XmlReader"/> containing the resultset generated 
        /// by the command</returns>
        /// <example>
        /// <code>
        /// XmlReader xmlr = ExecuteXmlReader("GetOrders", 24, 36);
        /// </code>
        /// </example>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Total of provided parameters' value is not same with total of input/inputouput parameters.
        /// </exception>
        /// <exception cref="System.InvalidOperationException">
        /// This method is called with internal connection.
        /// </exception>
        public XmlReader ExecuteXmlReader(string strSpName, params object[] aobjParValues)
        {
			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            // If we receive parameter values, we need to figure out where they go
            if ((aobjParValues != null) && (aobjParValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName);

                // Assign the provided values to these parameters based on parameter order
                SqlUtils.AssignParameterValues(aCommandPar, aobjParValues);

                // Call the overload that takes an array of SqlParameters
                return (ExecuteXmlReader(CommandType.StoredProcedure, strSpName, aCommandPar));
            }
            else
            {
                // Otherwise we can just call the SP without params
                return (ExecuteXmlReader(CommandType.StoredProcedure, strSpName));
            }
        }
        
        #endregion ExecuteXmlReader

        #region FillDataset
        
        /// <summary>
        /// Private helper method that execute a <see cref="System.Data.SqlClient.SqlCommand"/>
        /// (that returns a resultset) against the specified <see cref="System.Data.SqlClient.SqlConnection"/>
        /// and specified <see cref="System.Data.SqlClient.SqlTransaction"/>(if provided) using the 
        /// provided parameters.
        /// </summary>
        /// <param name="cnn">A valid SqlConnection.</param>
        /// <param name="tra">A valid SqlTransaction.</param>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL cmd.</param>
        /// <param name="ds">A dataset which will contain the resultset generated by the command.</param>
        /// <param name="astrTableName">This array will be used to create table mappings allowing the 
        /// <see cref="System.Data.DataTable"/>s to be referenced
        /// by a user defined name (probably the actual table name).
        /// </param>
        /// <param name="aCommandPar">An array of SqlParamters used to execute the command.</param>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cnn</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>ds</i> parameter is not provided.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Missing table names in <i>astrTableName</i> parameter.
        /// </exception>
        /// <example>
        /// <code>
        /// FillDataset(cnn, tra, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
        /// </code>
        /// </example>
        private void FillDataset(SqlConnection cnn, SqlTransaction tra, CommandType enuCommandType, 
            string strCommandText, DataSet ds, string[] astrTableName, params SqlParameter[] aCommandPar)
        {
            const string TABLE_NAME = "Table";

            if (cnn == null) throw new ArgumentNullException("cnn");
            if (ds == null) throw new ArgumentNullException("ds");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool bMustCloseConnection = false;

            try
            {
                SqlUtils.PrepareCommand(cmd, cnn, tra, enuCommandType, strCommandText, aCommandPar, out bMustCloseConnection);
    			
                // Create the DataAdapter & DataSet
                using(SqlDataAdapter adp = new SqlDataAdapter(cmd))
                {
                
                    // Add the table mappings specified by the user
                    if (astrTableName != null && astrTableName.Length > 0)
                    {
                        string strTableName = TABLE_NAME;
                        for (int iIndex=0; iIndex < astrTableName.Length; iIndex++)
                        {
                            if (astrTableName[iIndex] == null || astrTableName[iIndex].Length == 0) 
                            {
                                throw new ArgumentException("The astrTableName parameter must contain a list of tables, a value was provided as null or empty string.", "astrTableName" );
                            }
                            adp.TableMappings.Add(strTableName, astrTableName[iIndex]);
                            strTableName = TABLE_NAME + (iIndex + 1).ToString();
                        }
                    }
                
                    // Fill the DataSet using default values for DataTable names, etc
                    adp.Fill(ds);
                    miRetVal = SqlUtils.GetStoredProcedureReturnValue(cmd);
                    mhstOutput = SqlUtils.GetOutputParameterValues(cmd);
                }
            }
            finally
            {
                // Detach the SqlParameters from the cmd object, so they can be used again
                cmd.Parameters.Clear();

                if (bMustCloseConnection)
                {
                    cnn.Close();
                }
            }
        }

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a resultset 
        /// and takes no parameters). 
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <param name="ds">A dataset which will contain the resultset generated by the command.</param>
        /// <param name="astrTableName">This array will be used to create table mappings allowing the 
        /// <see cref="System.Data.DataTable"/>s to be referenced by a user defined name 
        /// (probably the actual table name).
        /// </param>
        /// <exception cref="System.ArgumentNullException">
        /// <i>ds</i> parameter is not provided.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Missing table names in <i>astrTableName</i> parameter.
        /// </exception>
        /// <example>
        /// <code>
        /// FillDataset(CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
        /// </code>
        /// </example>
        public void FillDataset(CommandType enuCommandType, string strCommandText, DataSet ds, string[] astrTableName)
        {
            FillDataset(enuCommandType, strCommandText, ds, astrTableName, null);
        }

        /// <summary>
        /// Execute a <see cref="System.Data.SqlClient.SqlCommand"/> (that returns a resultset) 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="enuCommandType">The CommandType (stored procedure, text, etc.).</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <param name="ds">A dataset which will contain the resultset generated by the command.</param>
        /// <param name="astrTableName">This array will be used to create table mappings allowing the 
        /// <see cref="System.Data.DataTable"/>s to be referenced by a user defined name 
        /// (probably the actual table name).
        /// </param>
        /// <param name="aCommandPar">An array of SqlParamters used to execute the command.</param>
        /// <exception cref="System.ArgumentNullException">
        /// <i>ds</i> parameter is not provided.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Missing table names in <i>astrTableName</i> parameter.
        /// </exception>
        /// <example>
        /// <code>
        /// FillDataset(CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
        /// </code>
        /// </example>
        public void FillDataset(CommandType enuCommandType, string strCommandText, DataSet ds, 
            string[] astrTableName, params SqlParameter[] aCommandPar)
        {
            SqlTransaction tra = null;

            miErrorCode = (int)DBError.AllOk;
            miRowsAffected = 0;
            miRetVal = 0;
            mhstOutput = null;

            try
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                }
                else
                {
                    if (mobjMainConnectionProvider.IsTransactionPending)
                    {
                        tra = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }

                // Pass through the call to the private overload
                FillDataset(mcnnMainConnection, tra, enuCommandType, strCommandText, ds, 
                    astrTableName, aCommandPar);
            }
            catch (Exception ex)
            {
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }               
                throw ex;
            }
            finally
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
            }
        }

        /// <summary>
        /// Execute a stored procedure via a <see cref="System.Data.SqlClient.SqlCommand"/>
        /// (that returns a resultset) using the provided parameter values.
        /// This method will query the database to discover the parameters for the stored procedure 
        /// (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="ds">A dataset wich will contain the resultset generated by the command.</param>
        /// <param name="astrTableName">This array will be used to create table mappings allowing the 
        /// <see cref="System.Data.DataTable"/>s to be referenced by a user defined name 
        /// (probably the actual table name)
        /// </param>
        /// <param name="aobjParValues">An array of objects to be assigned as the input values of the 
        /// stored procedure</param>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strSpName</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>ds</i> parameter is not provided.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Missing table names in <i>astrTableName</i> parameter.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Total of provided parameters' value is not same with total of input/inputouput parameters.
        /// </exception>
        /// <example>
        /// <code>
        /// FillDataset("GetOrders", ds, new string[] {"orders"}, 24, 36);
        /// </code>
        /// </example>
        public void FillDataset(string strSpName, DataSet ds, string[] astrTableName, params object[] aobjParValues)
        {
            if (ds == null) throw new ArgumentNullException("ds");
			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            // If we receive parameter values, we need to figure out where they go
            if ((aobjParValues != null) && (aobjParValues.Length > 0)) 
            {              
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName, true);

                // Assign the provided values to these parameters based on parameter order
                SqlUtils.AssignParameterValues(aCommandPar, aobjParValues);

                FillDataset(CommandType.StoredProcedure, strSpName, ds, astrTableName, aCommandPar);
            }
            else
            {
                // Otherwise we can just call the SP without params
                FillDataset(CommandType.StoredProcedure, strSpName, ds, astrTableName);
            }
        }

        #endregion
        
        #region UpdateDataset

        /// <summary>
        /// Executes the respective command for each inserted, updated, or deleted row in the 
        /// <see cref="System.Data.DataSet"/>.
        /// </summary>
        /// <remarks>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="cmdInsert">A valid transact-SQL statement or stored procedure to insert 
        /// new records into the data source.</param>
        /// <param name="cmdDelete">A valid transact-SQL statement or stored procedure to delete 
        /// records from the data source.</param>
        /// <param name="cmdUpdate">A valid transact-SQL statement or stored procedure used to update 
        /// records in the data source.</param>
        /// <param name="ds">The DataSet used to update the data source.</param>
        /// <param name="strTableName">The DataTable used to update the data source.</param>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cmdInsert</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>cmdDelete</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>cmdUpdate</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>strTableName</i> parameter is not provided.<br/>
        /// </exception>
        /// <example>
        /// <code>
        /// UpdateDataset(cmdInsert, cmdDelete, cmdUpdate, ds, "Order");
        /// </code>
        /// </example>
        public void UpdateDataset(SqlCommand cmdInsert, SqlCommand cmdDelete, SqlCommand cmdUpdate, DataSet ds, string strTableName)
        {
			if (cmdInsert == null) throw new ArgumentNullException("cmdInsert");
			if (cmdDelete == null) throw new ArgumentNullException("cmdDelete");
			if (cmdUpdate == null) throw new ArgumentNullException("cmdUpdate");
			if (strTableName == null || strTableName.Length == 0) throw new ArgumentNullException("strTableName"); 

            SqlTransaction tra = null;

            miErrorCode = (int)DBError.AllOk;
            miRowsAffected = 0;

            try
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                }
                else
                {
                    if (mobjMainConnectionProvider.IsTransactionPending)
                    {
                        tra = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }

                // Create a SqlDataAdapter, and dispose of it after we are done
                using (SqlDataAdapter adp = new SqlDataAdapter())
                {
                    // Set the data adapter commands
                    cmdUpdate.Connection = mcnnMainConnection;
                    cmdUpdate.Transaction = tra;
                    cmdInsert.Connection = mcnnMainConnection;
                    cmdInsert.Transaction = tra;
                    cmdDelete.Connection = mcnnMainConnection;
                    cmdDelete.Transaction = tra;

                    adp.UpdateCommand = cmdUpdate;
                    adp.InsertCommand = cmdInsert;
                    adp.DeleteCommand = cmdDelete;
                    
                    // Update the dataset changes in the data source
                    adp.Update(ds, strTableName); 

                    // Commit all the changes made to the DataSet
                    ds.AcceptChanges();
                }
            }
            catch (Exception ex)
            {
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
            finally
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
            }
        }

        #endregion

        #region CreateCommand

        /// <summary>
        /// Simplify the creation of a Sql command object by allowing
        /// a stored procedure and optional parameters to be provided
        /// </summary>
        /// This method can be called by using <see cref="System.Data.SqlClient.SqlConnection"/> 
        /// provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> only, i.e. external
        /// connection.
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="astrSouceColumn">An array of string to be assigned as the source columns of the 
        /// stored procedure parameters (whether input or output).</param>
        /// <returns>A valid SqlCommand object.</returns>
        /// <exception cref="System.ArgumentException">
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        /// <exception cref="System.InvalidOperationException">
        /// This method is called with internal connection.
        /// </exception>
        /// <example>
        /// <code>
        /// SqlCommand command = CreateCommand("AddCustomer", "CustomerID", "CustomerName");
        /// </code>
        /// </example>
        public SqlCommand CreateCommand(string strSpName, params string[] astrSouceColumn) 
        {
			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            SqlTransaction tra = null;

            miErrorCode = (int)DBError.AllOk;

            try
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    // invalid if internal connection is used
                    throw new InvalidOperationException("CreateCommand can be called only when SqlConnectionProvider is used");
                }
                else if (mobjMainConnectionProvider.IsTransactionPending)
                {
                    tra = mobjMainConnectionProvider.CurrentTransaction;
                }

                // Create a SqlCommand
                SqlCommand cmd = new SqlCommand(strSpName, mcnnMainConnection);
                cmd.Transaction = tra;
                cmd.CommandType = CommandType.StoredProcedure;

                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName, true);

                // If we receive source columns, we need to figure out where they go
                if ((astrSouceColumn != null) && (astrSouceColumn.Length > 0)) 
                {
                    // Assign the provided source columns to these parameters based on parameter order
                    for (int iIndex=0; iIndex < astrSouceColumn.Length; iIndex++)
                    {
                        aCommandPar[iIndex + 1].SourceColumn = astrSouceColumn[iIndex];
                    }                  
                }

                // Attach the discovered parameters to the SqlCommand object
                SqlUtils.AttachParameters(cmd, aCommandPar);

                return (cmd);
            }
            catch (Exception ex)
            {
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
        }

        #endregion

        #region ExecuteNonQueryTypedParams

        /// <summary>
        /// Execute a stored procedure via a <see cref="System.Data.SqlClient.SqlCommand"/> 
        /// (that returns no resultset) using the <see cref="System.Data.DataRow"/> column values 
        /// as the stored procedure's parameters values.  
        /// This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign 
        /// the values based on row values.
        /// </summary>
        /// <remarks>
        /// Columns' name in data row must same with store procedure parameters' name exactly. <br/>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="row">The row used to hold the stored procedure's parameter values.</param>
        /// <returns>An int representing the number of rows affected by the command.</returns>
        /// <example>
        /// <code>
        /// int result = ExecuteNonQueryTypedParams("PublishOrders", rowData);
        /// </code>
        /// </example>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        public int ExecuteNonQueryTypedParams(string strSpName, DataRow row)
        {
			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName, true);

            // If the row has values, the store procedure parameters must be initialized
            if (row != null && row.ItemArray.Length > 0)
            {
                // Set the parameters values
                SqlUtils.AssignParameterValues(aCommandPar, row);
            }
            
            return (ExecuteNonQuery(CommandType.StoredProcedure, strSpName, aCommandPar));
        }
        
        #endregion

        #region ExecuteDatasetTypedParams
        
        /// <summary>
        /// Execute a stored procedure via a <see cref="System.Data.SqlClient.SqlCommand"/> 
        /// (that returns a resultset) using the <see cref="System.Data.DataRow"/> column values 
        /// as the store procedure's parameters values.
        /// This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign 
        /// the values based on row values.
        /// </summary>
        /// <remarks>
        /// Columns' name in data row must same with store procedure parameters' name exactly. <br/>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="row">The row used to hold the stored procedure's parameter values.</param>
        /// <returns>A dataset containing the resultset generated by the command.</returns>
        /// <example>
        /// <code>
        /// DataSet ds = ExecuteDatasetTypedParams("GetOrders", rowData);
        /// </code>
        /// </example>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        public DataSet ExecuteDatasetTypedParams(String strSpName, DataRow row)
        {
			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName, true);

            // If the row has values, the store procedure parameters must be initialized
            if (row != null && row.ItemArray.Length > 0)
            {
                // Set the parameters values
                SqlUtils.AssignParameterValues(aCommandPar, row);
            }
        
            return (ExecuteDataset(CommandType.StoredProcedure, strSpName, aCommandPar));    
        }

        #endregion

        #region ExecuteReaderTypedParams
                       
        /// <summary>
        /// Execute a stored procedure via a <see cref="System.Data.SqlClient.SqlCommand"/> 
        /// (that returns a resultset) using the <see cref="System.Data.DataRow"/> column values 
        /// as the stored procedure's parameters values.
        /// This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign 
        /// the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// Columns' name in data row must same with store procedure parameters' name exactly. <br/>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection. Besides, this method provides no access to output parameters 
        /// or the stored procedure's return value parameter.
        /// </remarks>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="row">The row used to hold the stored procedure's parameter values.</param>
        /// <returns>A <see cref="System.Data.SqlClient.SqlDataReader"/> containing the resultset 
        /// generated by the command.</returns>
        /// <example>
        /// <code>
        /// SqlDataReader dtr = ExecuteReaderTypedParams("GetOrders", rowData);
        /// </code>
        /// </example>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        public SqlDataReader ExecuteReaderTypedParams(String strSpName, DataRow row)
        {
			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            // If the row has values, the store procedure parameters must be initialized
            if( row != null && row.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName);
                
                // Set the parameters values
                SqlUtils.AssignParameterValues(aCommandPar, row);
                
                return (ExecuteReader(CommandType.StoredProcedure, strSpName, aCommandPar));
            }
            else
            {
                return (ExecuteReader(CommandType.StoredProcedure, strSpName));
            }
        }
        
        #endregion

        #region ExecuteScalarTypedParams
        
        /// <summary>
        /// Execute a stored procedure via a <see cref="System.Data.SqlClient.SqlCommand"/> 
        /// (that returns a 1x1 resultset) using the <see cref="System.Data.DataRow"/> column 
        /// values as the stored procedure's parameters values.
        /// This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign 
        /// the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// Columns' name in data row must same with store procedure parameters' name exactly. <br/>
        /// This method uses connection provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> 
        /// object or internal connection.
        /// </remarks>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="row">The row used to hold the stored procedure's parameter values.</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command.</returns>
        /// <example>
        /// <code>
        /// int orderCount = (int)ExecuteScalarTypedParams("GetOrderCount", rowData);
        /// </code>
        /// </example>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        public object ExecuteScalarTypedParams(String strSpName, DataRow row)
        {
			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");
           
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName, true);

            // If the row has values, the store procedure parameters must be initialized
            if( row != null && row.ItemArray.Length > 0)
            {
                // Set the parameters values
                SqlUtils.AssignParameterValues(aCommandPar, row);
            }
            
            return (ExecuteScalar(CommandType.StoredProcedure, strSpName, aCommandPar));
        }
        
        #endregion

        #region ExecuteXmlReaderTypedParams
        /// <summary>
        /// Execute a stored procedure via a <see cref="System.Data.SqlClient.SqlCommand"/> 
        /// (that returns a resultset) using the <see cref="System.Data.DataRow"/> column values 
        /// as the stored procedure's parameters values.
        /// This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign 
        /// the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// Columns' name in data row must same with store procedure parameters' name exactly. <br/>
        /// This method can be called by using <see cref="System.Data.SqlClient.SqlConnection"/> 
        /// provided by <see cref="DBHelper.SqlClient.SqlConnectionProvider"/> only, i.e. external
        /// connection. Besides, this method provides no access to output parameters or the stored 
        /// procedure's return value parameter.
        /// </remarks>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="row">The row used to hold the stored procedure's parameter values.</param>
        /// <returns>An <see cref="System.Xml.XmlReader"/> containing the resultset generated 
        /// by the command.</returns>
        /// <example>
        /// <code>
        /// XmlReader xmlr = ExecuteXmlReaderTypedParams("GetOrders", rowData);
        /// </code>
        /// </example>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        public XmlReader ExecuteXmlReaderTypedParams(String strSpName, DataRow row)
        {
			if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            // If the row has values, the store procedure parameters must be initialized
            if( row != null && row.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] aCommandPar = SqlHelperParameterCache.GetSpParameterSet(mcnnMainConnection, strSpName);
                
                // Set the parameters values
                SqlUtils.AssignParameterValues(aCommandPar, row);
                
                return (ExecuteXmlReader(CommandType.StoredProcedure, strSpName, aCommandPar));
            }
            else
            {
                return (ExecuteXmlReader(CommandType.StoredProcedure, strSpName));
            }
        }
        
        #endregion

        #region Class Property Declarations

        /// <summary>
        /// Gets the return value after executing Sql command.
        /// </summary>
        public int ReturnValue
        {
            get
            {
                return (miRetVal);
            }
        }

        /// <summary>
        /// Gets the output value after executing Sql command, if provided.
        /// </summary>
        public Hashtable OutputValue
        {
            get
            {
                return (mhstOutput);
            }
        }
        
        #endregion Class Property Declarations
    }

}

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
Web01 | 2.8.140827.1 | Last Updated 14 Apr 2007
Article Copyright 2007 by falconsoon
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid