Click here to Skip to main content
11,482,103 members (64,257 online)
Click here to Skip to main content
Add your own
alternative version

Database Helper Class Library to Ease Database Operation

, 14 Apr 2007 CPOL 56.8K 2.1K 56
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: 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);
        }
	}
}

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 | Terms of Use | Mobile
Web03 | 2.8.150520.1 | Last Updated 14 Apr 2007
Article Copyright 2007 by falconsoon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid