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

Database Helper Class Library to Ease Database Operation

, 14 Apr 2007 CPOL
Database Helper Class Library to Ease Database Operation
article_demo.zip
DBHelperQuickRelationshipSamples
DBHelperQuickRelationshipSamples
App.ico
DBHelperQuickRelationshipSamples.csproj.user
DBHelperQuickRelationshipSamples.suo
DBHelperQuickSelectSamples
DBHelperQuickSelectSamples
DBHelperQuickSelectSamples.csproj.user
DBHelperQuickSelectSamples.suo
DBHelperQuickHelperSamples
DBHelperQuickHelperSamples
App.ico
DBHelperQuickHelperSamples.csproj.user
DBHelperQuickHelperSamples.suo
DBHelperQuickInsUpdDelSamples
DBHelperQuickInsUpdDelSamples
DBHelperQuickInsUpdDelSamples.csproj.user
DBHelperQuickInsUpdDelSamples.suo
article_src.zip
Microsoft.ApplicationBlocks.ExceptionManagement.Interfaces
Microsoft.ApplicationBlocks.ExceptionManagement.Interfaces.csproj.user
Microsoft.ApplicationBlocks.ExceptionManagement.suo
DbHelper
DBHelper.chm
DBHelper.csproj.user
DbHelper.ndoc
DBHelper.suo
Microsoft.ApplicationBlocks.ExceptionManagement
ExceptionManagerText.xsx
Microsoft.ApplicationBlocks.ExceptionManagement.csproj.user
Microsoft.ApplicationBlocks.suo
///////////////////////////////////////////////////////////////////////////
// Copyright 2003-2005 Falcon Soon
//
// Author: Soon Chun Boon
// Date: 26 May 2004
// Description: 
// Class that provides functions to leverage a static cache of procedure parameters, 
// and the ability to discover parameters for stored procedures at run-time.
// This class is taken from Microsoft Data Access Application Block version 2.
///////////////////////////////////////////////////////////////////////////

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


namespace DBHelper.SqlClient
{
    /// <summary>
    /// SqlHelperParameterCache provides functions to leverage a static cache of procedure 
    /// parameters, and the ability to discover parameters for stored procedures at run-time.
    /// </summary>
    public sealed class SqlHelperParameterCache
    {
        #region private methods, variables, and constructors

        /// <summary>
        /// Since this class provides only static methods, make the default constructor private to prevent 
        /// instances from being created with "new SqlHelperParameterCache()".
        /// </summary>
        private SqlHelperParameterCache() {}

        private static Hashtable mhstParamCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// Resolve at run time the appropriate set of <see cref="System.Data.SqlClient.SqlParameter"/>s 
        /// for a stored procedure.
        /// </summary>
        /// <param name="cnn">A valid <see cref="System.Data.SqlClient.SqlConnection"/> object.</param>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="bIncludeReturnValueParameter">Whether or not to include return value parameter.</param>
        /// <returns>The parameter array discovered.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cnn</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        private static SqlParameter[] DiscoverSpParameterSet(SqlConnection cnn, string strSpName, bool bIncludeReturnValueParameter)
        {
            if (cnn == null) throw new ArgumentNullException("cnn");
            if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            SqlCommand cmd = new SqlCommand(strSpName, cnn);
            cmd.CommandType = CommandType.StoredProcedure;

            cnn.Open();
            SqlCommandBuilder.DeriveParameters(cmd);
            cnn.Close();

            if (!bIncludeReturnValueParameter) 
            {
                cmd.Parameters.RemoveAt(0);
            }
                
            SqlParameter[] aparDiscovered = new SqlParameter[cmd.Parameters.Count];

            cmd.Parameters.CopyTo(aparDiscovered, 0);

            // Init the parameters with a DBNull value
            foreach (SqlParameter par in aparDiscovered)
            {
                par.Value = DBNull.Value;
            }
            return (aparDiscovered);
        }

        /// <summary>
        /// Deep copy of a <see cref="System.Data.SqlClient.SqlParameter"/> array.
        /// </summary>
        /// <param name="aparOriginal">Original parameter array to been cloned.</param>
        /// <returns>A deep copy of the original parameter array</returns>
        internal static SqlParameter[] CloneParameters(SqlParameter[] aparOriginal)
        {
            SqlParameter[] aparCloned = new SqlParameter[aparOriginal.Length];

            for (int i = 0, j = aparOriginal.Length; i < j; i++)
            {
                aparCloned[i] = (SqlParameter)((ICloneable)aparOriginal[i]).Clone();
            }

            return (aparCloned);
        }

        #endregion private methods, variables, and constructors

        #region caching functions

        /// <summary>
        /// Add parameter array to the cache.
        /// </summary>
        /// <param name="strCnnString">A valid connection string for a 
        /// <see cref="System.Data.SqlClient.SqlConnection"/>.</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command</param>
        /// <param name="apar">An array of SqlParamters to be cached</param>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strCnnString</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>strCommandText</i> parameter is not provided.
        /// </exception>
        public static void CacheParameterSet(string strCnnString, string strCommandText, params SqlParameter[] apar)
        {
            if(strCnnString == null || strCnnString.Length == 0) throw new ArgumentNullException("strCnnString");
            if(strCommandText == null || strCommandText.Length == 0) throw new ArgumentNullException("strCommandText");

            string strHashKey = strCnnString + ":" + strCommandText;

            mhstParamCache[strHashKey] = apar;
        }

        /// <summary>
        /// Retrieve a parameter array from the cache.
        /// </summary>
        /// <param name="strCnnString">A valid connection string for a 
        /// <see cref="System.Data.SqlClient.SqlConnection"/>.</param>
        /// <param name="strCommandText">The stored procedure name or T-SQL command.</param>
        /// <returns>An array of <see cref="System.Data.SqlClient.SqlParameter"/>s. is the parameters
        /// are cached otherwise Null is returned.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strCnnString</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>strCommandText</i> parameter is not provided.
        /// </exception>
        public static SqlParameter[] GetCachedParameterSet(string strCnnString, string strCommandText)
        {
            if(strCnnString == null || strCnnString.Length == 0) throw new ArgumentNullException("strCnnString");
            if(strCommandText == null || strCommandText.Length == 0) throw new ArgumentNullException("strCommandText");

            string strHashKey = strCnnString + ":" + strCommandText;

            SqlParameter[] aparCached = mhstParamCache[strHashKey] as SqlParameter[];
            if (aparCached == null)
            {			
                return null;
            }
            else
            {
                return CloneParameters(aparCached);
            }
        }

        #endregion caching functions

        #region Stored Procedure Parameter Discovery Functions

        /// <summary>
        /// Retrieves the set of <see cref="System.Data.SqlClient.SqlParameter"/>s appropriate 
        /// for the stored procedure.
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a 
        /// cache for future requests.<br/>
        /// By default, return value parameter is not included.
        /// </remarks>
        /// <param name="strCnnString">A valid connection string for a 
        /// <see cref="System.Data.SqlClient.SqlConnection"/>.</param>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <returns>An array of <see cref="System.Data.SqlClient.SqlParameter"/>s.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strCnnString</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        public static SqlParameter[] GetSpParameterSet(string strCnnString, string strSpName)
        {
            return GetSpParameterSet(strCnnString, strSpName, false);
        }

        /// <summary>
        /// Retrieves the set of <see cref="System.Data.SqlClient.SqlParameter"/>s 
        /// appropriate for the stored procedure.
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a 
        /// cache for future requests.
        /// </remarks>
        /// <param name="strCnnString">A valid connection string for a 
        /// <see cref="System.Data.SqlClient.SqlConnection"/>.</param>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="bIncludeReturnValueParameter">A bool value indicating whether the return value 
        /// parameter should be included in the results.</param>
        /// <returns>An array of <see cref="System.Data.SqlClient.SqlParameter"/>s.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strCnnString</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        public static SqlParameter[] GetSpParameterSet(string strCnnString, string strSpName, bool bIncludeReturnValueParameter)
        {
            if(strCnnString == null || strCnnString.Length == 0) throw new ArgumentNullException("strCnnString");
            if(strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            using(SqlConnection cnn = new SqlConnection(strCnnString))
            {
                return GetSpParameterSetInternal(cnn, strSpName, bIncludeReturnValueParameter);
            }
        }

        /// <summary>
        /// Retrieves the set of <see cref="System.Data.SqlClient.SqlParameter"/>s appropriate 
        /// for the stored procedure.
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a 
        /// cache for future requests.<br/>
        /// By default, return value parameter is not provided.
        /// </remarks>
        /// <param name="cnn">A valid <see cref="System.Data.SqlClient.SqlConnection"/> object.</param>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <returns>An array of <see cref="System.Data.SqlClient.SqlParameter"/>s.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cnn</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        internal static SqlParameter[] GetSpParameterSet(SqlConnection cnn, string strSpName)
        {
            return GetSpParameterSet(cnn, strSpName, false);
        }

        /// <summary>
        /// Retrieves the set of <see cref="System.Data.SqlClient.SqlParameter"/>s appropriate 
        /// for the stored procedure.
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a 
        /// cache for future requests.
        /// </remarks>
        /// <param name="cnn">A valid <see cref="System.Data.SqlClient.SqlConnection"/> object.</param>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="bIncludeReturnValueParameter">A bool value indicating whether the return value 
        /// parameter should be included in the results.</param>
        /// <returns>An array of <see cref="System.Data.SqlClient.SqlParameter"/>s.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cnn</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        internal static SqlParameter[] GetSpParameterSet(SqlConnection cnn, string strSpName, bool bIncludeReturnValueParameter)
        {
            if(cnn == null) throw new ArgumentNullException("cnn");
            if(strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            using (SqlConnection cnnCloned = (SqlConnection)((ICloneable)cnn).Clone())
            {
                return GetSpParameterSetInternal(cnnCloned, strSpName, bIncludeReturnValueParameter);
            }
        }

        /// <summary>
        /// Retrieves the set of <see cref="System.Data.SqlClient.SqlParameter"/>s appropriate 
        /// for the stored procedure.
        /// </summary>
        /// <remarks>
        /// This method will query from cache first if the requested parameters are exists. If not,
        /// it will query from database and stores it in cache for future request.
        /// </remarks>
        /// <param name="cnn">A valid <see cref="System.Data.SqlClient.SqlConnection"/> object.</param>
        /// <param name="strSpName">The name of the stored procedure.</param>
        /// <param name="bIncludeReturnValueParameter">A bool value indicating whether the return value 
        /// parameter should be included in the results.</param>
        /// <returns>An array of <see cref="System.Data.SqlClient.SqlParameter"/>s.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>cnn</i> parameter is not provided,<br/>
        /// or<br/>
        /// <i>strSpName</i> parameter is not provided.
        /// </exception>
        private static SqlParameter[] GetSpParameterSetInternal(SqlConnection cnn, string strSpName, bool bIncludeReturnValueParameter)
        {
            if(cnn == null) throw new ArgumentNullException("cnn");
            if(strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName");

            string strHashKey = cnn.ConnectionString + ":" + strSpName + (bIncludeReturnValueParameter ? ":include ReturnValue Parameter":"");

            SqlParameter[] aparCached;
        	
            aparCached = mhstParamCache[strHashKey] as SqlParameter[];
            if (aparCached == null)
            {	
                SqlParameter[] aparDiscovered = DiscoverSpParameterSet(cnn, strSpName, bIncludeReturnValueParameter);
                mhstParamCache[strHashKey] = aparDiscovered;
                aparCached = aparDiscovered;
            }
        	
            return CloneParameters(aparCached);
        }
        
        #endregion Stored Procedure Parameter Discovery Functions

    }

}

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