Database Helper Class Library to Ease Database Operation

///////////////////////////////////////////////////////////////////////////
// Copyright 2003-2005 Falcon Soon
//
// Author: Soon Chun Boon
// Date: 12 Oct 2003
// Description: 
// Class which consists of various common database helper methods.
///////////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using System.Text.RegularExpressions;

namespace DBHelper
{
	/// <summary>
	/// Provides a class that provides various database helper methods.
	/// </summary>
	/// <remarks>
	/// All methods provided are static.
	/// </remarks>
	public sealed class DBCommon
	{
        /// <summary>
        /// Prevents <see cref="DBHelper.DBCommon"/> class from been instanced.
        /// </summary>
        private DBCommon() {}
		
        /// <summary>
        /// Returns a new field name within square bracket.
        /// </summary>
        /// <param name="strFieldName">Field name.</param>
        /// <returns>A new field name within square bracker.</returns>
        public static string DBField(string strFieldName)
        {
            return ("[" + strFieldName + "]");
        }

        /// <summary>
        /// Replaces any single quote within the field value with 2 single quotes,
        /// and embedes the new value within 2 single quotes. 
        /// </summary>
        /// <param name="strFieldValue">Field value.</param>
        /// <returns>A new field value.</returns>
        public static string DBString(string strFieldValue)
        {
            string strNewFieldValue = null;

            if (strFieldValue != null)
            {
                strNewFieldValue = "'" + strFieldValue.Replace("'", "''") + "'";
            }

            return (strNewFieldValue);
        }

        /// <summary>
        /// Constructs a filter expression based on template and data provided.
        /// The filter expression can be used to select rows in DataTable instance or
        /// as a select criteria in direct database operation.
        /// </summary>
        /// <param name="strTemplate">Criteria template, e.g. col1 = {0} and col2 > {1}.</param>
        /// <param name="lstData">List of data used to form the select criteria.</param>
        /// <returns>A select criteria with the replaced data provided.</returns>
        /// <exception cref="System.ArgumentException">
        /// The provided data total is not comply with provided template.
        /// </exception>
        /// <remarks>
        /// Format for criteria template is: <br/>
        /// {colomn name} {comparison symbol} {number} {logical symbol}, etc. E.g. <br/>
        /// col1 = {0} and col2 = {2} <br/>
        /// * Take note that number in curly brace after comparison symbol can be any number. <br/><br/>
        /// 
        /// If data provided is null:
        /// <ul>
        /// <li>comparison = will change to "is null".</li>
        /// <li>comparison &lt;&gt; will change to "is not null".</li>
        /// <li>other comparison will remain original with the data change to "null".</li>
        /// </ul>
        /// <br/>
        /// If data provided is not null, the comparison will remain original with the data quoted within
        /// single quote and any single quotes been replaced with 2 double single quotes.
        /// </remarks>
        /// <example>
        /// This example shows how to construct filter expression:
        /// <code>
        /// ArrayList alData = new ArrayList();
        /// 
        /// alData.Add("test'2");
        /// alData.Add(null);
        ///
        /// string strFilter = DBCommon.ConstructFilterExpression("col1 = {0} and col2 = {2}", alData);
        /// Console.WriteLine(strFilter);
        /// </code>
        /// </example>
        public static string ConstructFilterExpression(string strTemplate, IList lstData)
        {
            const string MATCH_PATTERN = @"(?<express>(?<compare>=|<>|>|>=|<|<=)[ ]*\{[0-9]+\})";
            
            Regex reFilter;
            MatchCollection mc;
            Match m;
            StringBuilder strbReplace;
            
            reFilter = new Regex(MATCH_PATTERN, RegexOptions.IgnoreCase);
            mc = reFilter.Matches(strTemplate);
            
            if (mc.Count != lstData.Count)
            {
                throw new ArgumentException("lstData elements' total does not comply with strTemplate");
            }

            strbReplace = new StringBuilder(strTemplate);
            for (int i = mc.Count - 1; i >= 0; i--)
            {
                m = mc[i];
                if (m.Groups.Count == 3)
                {
                    Capture cExpress = m.Groups["express"].Captures[0];
                    string strOld = cExpress.Value;
                    string strNew;
                    string strCompare = m.Groups["compare"].Captures[0].Value;
                    Object objData = lstData[i];

                    if (objData == null || objData.Equals(DBNull.Value))
                    {
                        if (strCompare == "=")
                        {
                            strNew = " is null";
                        }
                        else if (strCompare == "<>")
                        {
                            strNew = " is not null";
                        }
                        else
                        {
                            strNew = strCompare + "null";
                        }
                    }
                    else
                    {
                        strNew = strCompare + DBString(objData.ToString());
                    }

                    strbReplace.Replace(strOld, strNew, cExpress.Index, strOld.Length);
                }
            }

            return (strbReplace.ToString());
        }

        /// <summary>
        /// To create a deep copy of the passed-in SqlParameter instance.
        /// </summary>
        /// <param name="parameter">SqlParameter instance to deep copy.</param>
        /// <returns>A new instance which is deep copy of passed-in SqlParameter instance.</returns>
        public static SqlParameter DeepCopySqlParameter(SqlParameter parameter)
        {
            SqlParameter parameterToReturn;

            parameterToReturn = new SqlParameter(parameter.ParameterName, parameter.SqlDbType, parameter.Size,
                parameter.Direction, parameter.IsNullable, parameter.Precision, parameter.Scale,
                parameter.SourceColumn, parameter.SourceVersion, parameter.Value);

            return (parameterToReturn);
        }
	}
}