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: 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);
        }
	}
}

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