Database Helper Class Library to Ease Database Operation

///////////////////////////////////////////////////////////////////////////
// Copyright 2003-2005 Falcon Soon
//
// Author: Soon Chun Boon
// Date: 25 May 2004
// Description: 
// Class that provides various convenient methods specific to SqlClient.
///////////////////////////////////////////////////////////////////////////
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace DBHelper.SqlClient
{
    /// <summary>
    /// A class that provides various database helper methods. All methods
    /// provided are static and specific to SqlClient.
    /// </summary>
	internal sealed class SqlUtils
	{
        /// <summary>
        /// Prevents this class from been instanced.
        /// </summary>
		private SqlUtils() {}

        /// <summary>
        /// Attaches array of <see cref="System.Data.SqlClient.SqlParameter"/>s to a 
        /// <see cref="System.Data.SqlClient.SqlCommand"/>.
        /// </summary>
        /// <remarks>
        /// This method will assign a value of <see cref="System.DBNull"/> to any parameter with a direction of
        /// <see cref="System.Data.ParameterDirection.Input"/> or 
        /// <see cref="System.Data.ParameterDirection.InputOutput"/> and a value of null.  
        /// 
        /// This behavior will prevent default values from being used, but
        /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
        /// where the user provided no input value.
        /// </remarks>
        /// <param name="cmd">The command to which the parameters will be added.</param>
        /// <param name="apar">An array of SqlParameters to be added to command.</param>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cmd</i> parameter is not provided.
        /// </exception>
        public static void AttachParameters(SqlCommand cmd, SqlParameter[] apar)
        {
            if( cmd == null ) throw new ArgumentNullException("cmd");
            if( apar != null )
            {
                foreach (SqlParameter par in apar)
                {
                    if(par != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((par.Direction == ParameterDirection.InputOutput || 
                            par.Direction == ParameterDirection.Input ) && 
                            (par.Value == null))
                        {
                            par.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(par);
                    }
                }
            }
        }

        /// <summary>
        /// Detaches array of <see cref="System.Data.SqlClient.SqlParameter"/>s from a 
        /// <see cref="System.Data.SqlClient.SqlCommand"/>.
        /// </summary>
        /// <param name="cmd">The command to which the parameters will be removed.</param>
        /// <param name="apar">An array of SqlParameters to be removed from command.</param>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cmd</i> parameter is not provided.
        /// </exception>
        public static void DetachParameters(SqlCommand cmd, SqlParameter[] apar)
        {
            if( cmd == null ) throw new ArgumentNullException("cmd");
            if( apar != null )
            {
                foreach (SqlParameter par in apar)
                {
                    if(par != null)
                    {
                        cmd.Parameters.Remove(par);
                    }
                }
            }
        }

        /// <summary>
        /// Assigns row column values to an array of <see cref="System.Data.SqlClient.SqlParameter"/>s.
        /// </summary>
        /// <param name="apar">Array of SqlParameters to be assigned values.</param>
        /// <param name="row">The row used to hold the parameter values.</param>
        /// <exception cref="System.Exception">
        /// Missing parameter name in provided array of parameters.
        /// </exception>
        public static void AssignParameterValues(SqlParameter[] apar, DataRow row)
        {
            if ((apar == null) || (row == null)) 
            {
                // Do nothing if we get no data
                return;
            }

            int i = 0;
            // Set the parameters values
            foreach(SqlParameter par in apar)
            {
                // Check the parameter name
                if(par.ParameterName == null || par.ParameterName.Length <= 1 )
                    throw new Exception(
                        string.Format( 
                        "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.", 
                        i, par.ParameterName ) );
                if (row.Table.Columns.IndexOf(par.ParameterName.Substring(1)) != -1)
                    par.Value = row[par.ParameterName.Substring(1)];
                i++;
            }
        }

        /// <summary>
        /// Assigns an array of values to an array of <see cref="System.Data.SqlClient.SqlParameter"/>s.
        /// </summary>
        /// <remarks>
        /// This method assign value to parameters ordinally.<br/>
        /// <p>
        /// This method will assign value to <see cref="System.Data.ParameterDirection.Input"/> and
        /// <see cref="System.Data.ParameterDirection.InputOutput"/> parameters only. If provided value
        /// is Null, <see cref="System.DBNull"/> will be assigned to parameter.
        /// </p>
        /// </remarks>
        /// <param name="apar">Array of SqlParameters to be assigned values.</param>
        /// <param name="aobjValues">Array of objects holding the values to be assigned.</param>
        /// <exception cref="System.ArgumentException">
        /// Total of provided parameters' value are not matched with total of Input and InputOutput parameters.
        /// </exception>
        public static void AssignParameterValues(SqlParameter[] apar, object[] aobjValues)
        {
            ArrayList alInputOutputParameter = new ArrayList();

            if ((apar == null) || (aobjValues == null)) 
            {
                // Do nothing if we get no data
                return;
            }

            foreach (SqlParameter par in apar)
            {
                if (par.Direction == ParameterDirection.Input || par.Direction == ParameterDirection.InputOutput)
                {
                    alInputOutputParameter.Add(par);
                }
            }
            // We must have the same number of values as we pave parameters to put them in
            if (alInputOutputParameter.Count != aobjValues.Length)
            {
                throw new ArgumentException("Input or InputOutput Parameter count does not match Parameter Value count.");
            }

            // Iterate through the SqlParameters, assigning the values from the corresponding position in the 
            // value array
            for (int i = 0, j = alInputOutputParameter.Count; i < j; i++)
            {
                SqlParameter par = (SqlParameter)alInputOutputParameter[i];
                
                if (par.Direction == ParameterDirection.Input || par.Direction == ParameterDirection.InputOutput)
                {
                    // If the current array value derives from IDbDataParameter, then assign its Value property
                    if (aobjValues[i] is IDbDataParameter)
                    {
                        IDbDataParameter paramInstance = (IDbDataParameter)aobjValues[i];
                        if( paramInstance.Value == null )
                        {
                            par.Value = DBNull.Value; 
                        }
                        else
                        {
                            par.Value = paramInstance.Value;
                        }
                    }
                    else if (aobjValues[i] == null)
                    {
                        par.Value = DBNull.Value;
                    }
                    else
                    {
                        par.Value = aobjValues[i];
                    }
                }
            }
        }

        /// <summary>
        /// Opens (if necessary) and assigns a connection, transaction, command type and parameters 
        /// to the provided command.
        /// </summary>
        /// <param name="cmd">The SqlCommand to be prepared.</param>
        /// <param name="cnn">A valid SqlConnection, on which to execute this cmd.</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="apar">An array of SqlParameters to be associated with the command or 'null' if 
        /// no parameters are required.</param>
        /// <param name="bMustCloseConnection">True if the connection was opened by the method, 
        /// otherwise is False.</param>
        /// <exception cref="System.ArgumentException">
        /// <i>cmd</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>strCommandText</i> parameter is not provided,<br/>
        /// or<br/>
        /// Rollbacked or Commited transaction is provided.
        /// </exception>
        public static void PrepareCommand(SqlCommand cmd, SqlConnection cnn, SqlTransaction tra,
            CommandType enuCommandType, string strCommandText, SqlParameter[] apar,
            out bool bMustCloseConnection )
        {
            if(cmd == null) throw new ArgumentNullException("cmd");
            if(strCommandText == null || strCommandText.Length == 0) 
                throw new ArgumentNullException("strCommandText");

            // If the provided cnn is not open, we will open it
            if (cnn.State != ConnectionState.Open)
            {
                bMustCloseConnection = true;
                cnn.Open();
            }
            else
            {
                bMustCloseConnection = false;
            }

            // Associate the cnn with the cmd
            cmd.Connection = cnn;

            // If we were provided a tra, assign it
            if (tra != null)
            {
                if( tra.Connection == null ) throw new ArgumentException( "The tra was rollbacked or commited, please provide an open tra.", "tra" );
                cmd.Transaction = tra;
            }

            // Set the cmd text (stored procedure name or SQL statement)
            cmd.CommandText = strCommandText;
            // Set the cmd type
            cmd.CommandType = enuCommandType;

            // Attach the cmd parameters if they are provided
            if (apar != null)
            {
                AttachParameters(cmd, apar);
            }
            return;
        }

        /// <summary>
        /// Retrieves return value from stored procedure.
        /// </summary>
        /// <param name="cmd">A valid SqlCommand which executes the stored procedure.</param>
        /// <returns>The return value or 0 if no return value is exists.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cmd</i> parameter is not provided.
        /// </exception>
        public static int GetStoredProcedureReturnValue(SqlCommand cmd)
        {
            const string RET_VAL_PAR = "@RETURN_VALUE";

            int iRetVal = 0;
            SqlParameterCollection colPar;

            if (cmd == null) throw new ArgumentNullException("cmd");

            colPar = cmd.Parameters;
            if (colPar.Contains(RET_VAL_PAR))
            {
                iRetVal = (int)colPar[RET_VAL_PAR].Value;
            }

            return (iRetVal);
        }

        /// <summary>
        /// Retrieves value from output parameters.
        /// </summary>
        /// <param name="cmd">A valid SqlCommand which executes the T-Sql statement or stored procedure.</param>
        /// <returns>A Hashtable consists of output parameters' value or zero length Hashtable if
        /// no output parameters are exist.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cmd</i> parameter is not provided.
        /// </exception>
        public static Hashtable GetOutputParameterValues(SqlCommand cmd)
        {
            if (cmd == null) throw new ArgumentNullException("cmd");

            Hashtable hstOutput = new Hashtable();

            foreach (SqlParameter par in cmd.Parameters)
            {
                if (par.Direction == ParameterDirection.Output ||
                    par.Direction == ParameterDirection.InputOutput)
                {
                    hstOutput.Add(par.ParameterName, par.Value);
                }
            }

            return (hstOutput);
        }
	}
}