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: 02 Feb 2004
// Description: 
// Class that provides convenient way of perform basic database operations,
// i.e. Select, Insert, Update, and Delete on a database table.
///////////////////////////////////////////////////////////////////////////
using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Collections;
using System.Text;

namespace DBHelper.SqlClient
{
	/// <summary>
	/// Convenient data access class for a database table. It provides convenient ways
	/// of performs basic database operations, i.e. Select, Insert, Update, and Delete.
	/// </summary>
	/// <example>
	/// <code>
    /// mobjTableHelper = new SqlTableHelper("x_Test");
    /// mobjCnnProvider = new SqlConnectionProvider();
    /// mobjCnnProvider.ConnectionString = "Initial Catalog=SwComponent;Data Source=Falcon;UID=sa;Password=sa";
    /// mobjTableHelper.MainConnectionProvider = mobjCnnProvider;
    /// mobjTableHelper.FieldsToUpdate = "name";
    /// mobjTableHelper.Compile();
    /// DataTable tblData = mobjTableHelper.SelectTop(50, true);
    /// DataRow row = tblData.Rows[0];
    /// row.BeginEdit();
    /// row["Name"] = "Neo";
    /// mobjTableHelper.CriteriaType = DBCriteria.None;
    /// mobjCnnProvider.OpenConnection();
    /// mobjTableHelper.Update();
    /// mobjCnnProvider.CloseConnection();
    /// tblData.AcceptChanges();
    /// </code>
	/// </example>
	public class SqlTableHelper : SqlDBInteractionBase, ICommonDBAccess
	{
        #region Class Member Declarations
        private const string TABLE_UNIQUE_CONSTRAINT = "TableUniqueConstraint";

        private string mstrTableName;
        private Hashtable mhstField;
        private DataTable mtblData;

        private string mstrFieldsToSelect;
        private string mstrFieldsToUpdate;
        private string mstrSelectCriteria;
        private string mstrUpdateCriteria;
        private string mstrDeleteCriteria;
        private string mstrSort;
        private string mstrBaseSelectCommandText;
        private string mstrBaseUpdateCommandText;
        private string mstrBaseDeleteCommandText;
        private string mstrSelectIdentity;
        private Hashtable mhstBaseInsertInputParameter;
        private Hashtable mhstBaseInsertOutputParameter;
        
        private string mstrCriteriaKey;
        private SqlParameter[] maparCriteriaKey;
        private DBCriteria menuCriteriaType;

        private SqlCommand mcmnSelect;
        private SqlCommand mcmnInsert;
        private SqlCommand mcmnUpdate;
        private SqlCommand mcmnDelete;
        private SqlDataAdapter madpMain;

        private DBRelationCollection mcolParentRelation;
        private DBRelationCollection mcolChildRelation;

        private bool mbIsDisposed;
        #endregion

        /// <summary>
        /// Initializes a new instance of <see cref="DBHelper.SqlClient.SqlTableHelper"/> with
        /// specified table name.
        /// </summary>
        /// <param name="strTableName">The table name.</param>
        /// <exception cref="System.ArgumentNullException">
        /// <i>strTableName</i> parameter is not provided.
        /// </exception>
		public SqlTableHelper(string strTableName)
		{
            if (strTableName == null)
            {
                // Invalid value
                throw new ArgumentNullException("strTableName", "Null passed as value to this parameter which is not allowed.");
            }
            mbIsDisposed = false;

			mstrTableName = strTableName;
            menuCriteriaType = DBCriteria.None;
            mhstField = new Hashtable();
            mhstBaseInsertInputParameter = new Hashtable();
            mhstBaseInsertOutputParameter = new Hashtable();
		}

        /// <summary>
        /// Prepares the Select, Insert, Update, and Delete commands that provide convenient
        /// ways to perform database operations. This method also retrieves all fields' info
        /// and set the columns mapping type according to 
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToSelect"/>.
        /// </summary>
        /// <example>
        /// <code>
        /// objTableHelper.FieldsToSelect = "name";
        /// mobjTableHelper.FieldsToUpdate = "name";
        /// mobjTableHelper.Compile();
        /// </code>
        /// </example>
        public void Compile()
        {
            mstrSelectIdentity = "";

            RetrieveFieldsInfo();
            ConstructDataTableSchema();
            SetMappingType();
            
            PrepareCriteriaKey();
            PrepareSelectCommand();
            PrepareInsertCommand();
            PrepareUpdateCommand();
            PrepareDeleteCommand();
            PrepareDataAdapter();
        }

        /// <summary>
        /// Sets the mapping type of <see cref="System.Data.DataTable"/>'s columns according to
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToSelect"/>. If a column is not specified
        /// in <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToSelect"/>, its mapping type will be
        /// set to Hidden, otherwise the default is used.
        /// </summary>
        /// <remarks>
        /// Mapping type is set so that hidden mapping type will not be displayed when its datatable
        /// is binded to control.
        /// </remarks>
        private void SetMappingType()
        {
            string[] astrFieldToSelect = null;
            string strTempField;
            MappingType enuMapping;

            if (mstrFieldsToSelect != null && mstrFieldsToSelect != "")
            {
                astrFieldToSelect = mstrFieldsToSelect.Split(new Char[] {';'});
            }
            if (astrFieldToSelect != null && astrFieldToSelect.Length > 0)
            {
                enuMapping = mtblData.Columns[0].ColumnMapping;
                foreach (DataColumn col in mtblData.Columns)
                {
                    col.ColumnMapping = MappingType.Hidden;
                }

                foreach (string strField in astrFieldToSelect)
                {
                    strTempField = strField.Trim();
                    if (!mtblData.Columns.Contains(strTempField))
                    {
                        throw new ArgumentException("Invalid field name: " + strTempField, "FieldsToSelect");
                    }
                    mtblData.Columns[strTempField].ColumnMapping = enuMapping;
                }
            }
        }

        #region Database Schema Methods
        /// <summary>
        /// Retrieves the fields' information of the table represented by this class.
        /// </summary>
        /// <remarks>
        /// Throws exception if error occures.<br /><br />
        /// Uses the following query to retrieve <i>all</i> fields' information in one single
        /// request:<br /><br />
        /// <code>
        /// <pre>
        /// SELECT INFORMATION_SCHEMA.COLUMNS.*,
        /// (
        /// 	SELECT COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed')
        /// ) AS IsComputed,
        /// (
        /// 	SELECT COL_LENGTH(@sTableName, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME)
        /// ) AS ColumnLength,
        /// (
        /// 	SELECT COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity')
        /// ) AS IsIdentity,
        /// (
        /// 	SELECT COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol')
        /// ) AS IsRowGuidColumn,
        /// (
        /// 	ISNULL(	
        /// 	(
        /// 		SELECT 	1
        /// 		FROM	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        /// 		WHERE	TABLE_NAME=@sTableName AND TABLE_SCHEMA='dbo'
        /// 			AND COLUMN_NAME=INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
        /// 			AND EXISTS
        /// 			(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        /// 			WHERE TABLE_NAME=@sTableName AND TABLE_SCHEMA='dbo' AND CONSTRAINT_TYPE = 'PRIMARY KEY'
        /// 			AND CONSTRAINT_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME)
        /// 	), 0)
        /// ) AS IsPrimaryKey,
        /// (
        /// 	ISNULL(	
        /// 	(	
        /// 		SELECT 	1
        /// 		FROM	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        /// 		WHERE	TABLE_NAME=@sTableName AND TABLE_SCHEMA='dbo'
        /// 			AND COLUMN_NAME=INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
        /// 			AND EXISTS
        /// 			(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        /// 			WHERE TABLE_NAME=@sTableName AND TABLE_SCHEMA='dbo' AND CONSTRAINT_TYPE = 'FOREIGN KEY'
        /// 			AND CONSTRAINT_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME)
        /// 	), 0)
        /// ) AS IsForeignKey, 
        /// (
        /// 	ISNULL(	
        /// 	(	
        /// 		SELECT 	1
        /// 		FROM	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        /// 		WHERE	TABLE_NAME=@sTableName AND TABLE_SCHEMA='dbo'
        /// 			AND COLUMN_NAME=INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
        /// 			AND EXISTS
        /// 			(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        /// 			WHERE TABLE_NAME=@sTableName AND TABLE_SCHEMA='dbo' AND CONSTRAINT_TYPE = 'UNIQUE'
        /// 			AND CONSTRAINT_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME)
        /// 	), 0)
        /// ) AS HasUniqueConstraint
        /// FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @sTableName AND TABLE_SCHEMA='dbo'
        /// </pre>
        /// </code>
        /// </remarks>
        /// <exception cref="DBHelper.DataException.NoFieldsAvailable">
        /// No fields is available for the table.
        /// </exception>
        private void RetrieveFieldsInfo()
        {
            SqlDataAdapter	adpFieldRetrieval;
            SqlCommand		cmnFieldRetrieval;
            DataTable		tblFields = new DataTable();

            // we'll retrieve ALL fields' information with 1 single query. this query is quite long, but
            // doesn't use any systemspecific tables at all. 
            string strSQL = "SELECT INFORMATION_SCHEMA.COLUMNS.*,(SELECT COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed')" +
                ") AS IsComputed,(SELECT COL_LENGTH(@sTableName, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME)) AS ColumnLength,(SELECT " +
                "COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity')) AS IsIdentity,(SELECT " +
                "COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol')) AS IsRowGuidColumn,(ISNULL(" +
                "(SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE	WHERE TABLE_NAME=@sTableName AND TABLE_SCHEMA='dbo'	AND COLUMN_NAME=" + 
                "INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME	AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME=@sTableName " + 
                "AND TABLE_SCHEMA='dbo' AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME)" +
                "), 0)) AS IsPrimaryKey,(ISNULL((SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@sTableName AND TABLE_SCHEMA='dbo'" +
                "AND COLUMN_NAME=INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME=" +
                "@sTableName AND TABLE_SCHEMA='dbo' AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME)" +
                "), 0)) AS IsForeignKey, (ISNULL((SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@sTableName AND TABLE_SCHEMA='dbo'" +
                "AND COLUMN_NAME=INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME	AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE " +
                "TABLE_NAME=@sTableName AND TABLE_SCHEMA='dbo' AND CONSTRAINT_TYPE = 'UNIQUE' AND CONSTRAINT_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME)" +
                "), 0)) AS HasUniqueConstraint FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @sTableName AND TABLE_SCHEMA='dbo'";

            cmnFieldRetrieval = new SqlCommand(strSQL, mcnnMainConnection);
            adpFieldRetrieval = new SqlDataAdapter(cmnFieldRetrieval);
            try
            {
                if(mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                }
                else
                {
                    if(mobjMainConnectionProvider.IsTransactionPending)
                    {
                        cmnFieldRetrieval.Transaction = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }

                cmnFieldRetrieval.Parameters.Add(new SqlParameter("@sTableName", SqlDbType.VarChar, 250, 
                    ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, mstrTableName));
				
                // Fill the datatable
                adpFieldRetrieval.Fill(tblFields);
				
                // Create field objects
                mhstField.Clear();

                if (tblFields.Rows.Count == 0)
                {
                    throw new DataException.NoFieldsAvailable(mstrTableName);
                }

                // for each row, add a Field object to the array.
                for (int i = 0; i < tblFields.Rows.Count; i++)
                {
                    DataRow rowCurrent = tblFields.Rows[i];
				    Field objField = new Field(rowCurrent["COLUMN_NAME"].ToString());

                    mhstField[objField.FieldName.ToUpper()] = objField;
                    // fill properties.
                    objField.IsComputed = (Convert.ToInt32(rowCurrent["IsComputed"]) == 1);
                    objField.IsForeignKey = (Convert.ToInt32(rowCurrent["IsForeignKey"]) == 1);
                    objField.IsPrimaryKey = (Convert.ToInt32(rowCurrent["IsPrimaryKey"]) == 1);
                    objField.IsIdentity = (Convert.ToInt32(rowCurrent["IsIdentity"]) == 1);
                    objField.IsRowGUIDColumn = (Convert.ToInt32(rowCurrent["IsRowGuidColumn"]) == 1);
                    objField.HasUniqueConstraint = (Convert.ToInt32(rowCurrent["HasUniqueConstraint"]) == 1);
                    objField.DataType = rowCurrent["DATA_TYPE"].ToString();
                    objField.Length = Convert.ToInt32(rowCurrent["ColumnLength"]);
                    if(rowCurrent["CHARACTER_MAXIMUM_LENGTH"].ToString().Length > 0)
                    {
                        // has a value
                        int iCharacterMaxLength = Convert.ToInt32(rowCurrent["CHARACTER_MAXIMUM_LENGTH"]);
						
                        if((objField.DataType.ToLower(CultureInfo.InvariantCulture) == "nvarchar") || 
                            (objField.DataType.ToLower(CultureInfo.InvariantCulture) == "nchar"))
                        {
                            // set length to length mentioned in CHARACTER_MAXIMUM_LENGTH
                            objField.Length = iCharacterMaxLength;
                        }
                    }
                    objField.OrdinalPosition = Convert.ToInt32(rowCurrent["ORDINAL_POSITION"]);
                    if(rowCurrent["NUMERIC_PRECISION"].ToString().Length > 0)
                    {
                        objField.Precision = Convert.ToInt32(rowCurrent["NUMERIC_PRECISION"]);
                    }
                    if(rowCurrent["NUMERIC_SCALE"].ToString().Length > 0)
                    {
                        objField.Scale = Convert.ToInt32(rowCurrent["NUMERIC_SCALE"]);
                    }
                    objField.TypeToSqlType = DBTypeInfo.TypeToSqlType;
                    objField.TypeToSqlDbType = DBTypeInfo.TypeToSqlDbType;
                    objField.TypeToNETType = DBTypeInfo.TypeToNETType;

                    objField.IsNullable = (rowCurrent["IS_NULLABLE"].ToString().ToLower(CultureInfo.InvariantCulture) == "yes");
                    objField.DefaultValue = rowCurrent["COLUMN_DEFAULT"].ToString();
                }
            }
            catch(Exception ex)
            {
                // bubble error.
                throw new Exception("Error occured: " + ex.Message, ex);
            }
            finally
            {
                if(mbMainConnectionIsCreatedLocal)
                {
                    // Close connection.
                    mcnnMainConnection.Close();
                }
                adpFieldRetrieval.Dispose();
                adpFieldRetrieval = null;
            }
        }

        /// <summary>
        /// Constructs table schema.
        /// </summary>
        /// <remarks>
        /// A new <see cref="System.Data.DataTable"/> instance is created and columns are added accordingly 
        /// based on retrieved fields' info. The following <see cref="System.Data.DataColumn"/>'s properties 
        /// are configured:
        /// <UL>
        /// <LI>AutoIncrement. You must set AutoIncrementStep and AutoIncrementSeed separately.</LI>
        /// <LI>Unique</LI>
        /// </UL>
        /// <P><B>ConstructDataTableSchema</B> also configures the PrimaryKey and Constraints properties 
        /// according to the following rules:
        /// <UL>
        /// <LI>If one or more primary key columns are exist, they are used as the primary key columns 
        /// for the DataTable.</LI>
        /// <LI>If no primary key columns are exist but unique columns are, the unique columns are used as 
        /// the primary key if, and only if, all the unique columns are non-nullable. If any of the columns 
        /// are nullable, a UniqueConstraint is added to the ConstraintCollection, but the 
        /// PrimaryKey property is not set.</LI>
        /// <LI>If both primary key columns and unique columns are exists, the primary key columns are 
        /// used as the primary key columns for the DataTable.</LI>
        /// </UL>
        /// </P>
        /// </remarks>
        private void ConstructDataTableSchema()
        {
            ArrayList alPrimaryKey = new ArrayList(5);
            ArrayList alUniqueCol = new ArrayList(5);
            bool bUseUniqueColsAsPrimaryKey = true;

            mtblData = new DataTable(mstrTableName);

            foreach (Field objField in mhstField.Values)
            {
                DataColumn col = new DataColumn(objField.FieldName);
                col.DataType = Type.GetType(objField.GetFieldTypeAsNETType(), true, true);
                col.AutoIncrement = objField.IsIdentity;
                col.Unique = objField.HasUniqueConstraint;
                mtblData.Columns.Add(col);

                if (objField.IsPrimaryKey)
                {
                    alPrimaryKey.Add(col);
                }
                if (objField.HasUniqueConstraint)
                {
                    alUniqueCol.Add(col);
                    if (objField.IsNullable)
                    {
                        bUseUniqueColsAsPrimaryKey = false;
                    }
                }
            }

            if (alPrimaryKey.Count > 0)
            {
                mtblData.PrimaryKey = (DataColumn[])alPrimaryKey.ToArray(typeof(System.Data.DataColumn));
            }
            else if (alUniqueCol.Count > 0)
            {
                if (bUseUniqueColsAsPrimaryKey)
                {
                    mtblData.PrimaryKey = (DataColumn[])alUniqueCol.ToArray(typeof(System.Data.DataColumn));
                }
                else
                {
                    mtblData.Constraints.Add(TABLE_UNIQUE_CONSTRAINT, 
                        (DataColumn[])alUniqueCol.ToArray(typeof(System.Data.DataColumn)), false);
                }
            }
        }

        /// <summary>
        /// Gets the <see cref="DBHelper.Field"/> instance for specified field name.
        /// </summary>
        /// <remarks>
        /// Field name is not case sensitive.
        /// </remarks>
        /// <param name="strFieldName">The field name.</param>
        /// <returns>A <see cref="DBHelper.Field"/> instance.</returns>
        public Field GetField(string strFieldName)
        {
            Field objFieldToReturn = null;

            objFieldToReturn = (Field)mhstField[strFieldName.ToUpper()];

            return (objFieldToReturn);
        }

        /// <summary>
        /// Adds parent relationship with the specified parent <see cref="DBHelper.SqlClient.SqlTableHelper"/>
        /// instance and foreign keys.
        /// </summary>
        /// <remarks>
        /// This method will adds the parent relation to this instance's parent relation collection.
        /// The relation name format is:-<br/><br/>
        /// [ParentTableName]FK[ForeignTableName]|[ForeignFieldName]|[ForeignFieldName]..., e.g. <br/><br/>
        /// x_PrimaryFKx_Foreign|CustIC|<br/><br/>
		/// When adding parent relationship using this method, primary key or unique instance
		/// in child table will be used as parent key.
        /// </remarks>
        /// <param name="objParentTable">A SqlTableHelper instance.</param>
        /// <param name="astrForeignField">An array consists of foreign keys' field names.</param>
        /// <returns><see cref="DBHelper.DBRelation"/> intance that represents 
        /// the database relationship.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>astrForeignField</i> parameter is not provided,<br/>
        /// or<br/>
        /// Zero length array is provided.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Wrong field name is specified for foreign keys.
        /// </exception>
        /// <exception cref="DBHelper.DataException.NoPrimaryOrUniqueKeyAvailable">
        /// No primary keys or uique keys is available for parent table.
        /// </exception>
        /// <exception cref="DBHelper.DataException.ParentRelationAlreadyExist">
        /// The parent relation is already exist.
        /// </exception>
        /// <example>
        /// <code>
        /// mobjForeignTable.AddParentKeys(mobjPrimaryTable, new string[] {"CustIC"});
        /// </code>
        /// </example>
        public DBRelation AddParentKeys(SqlTableHelper objParentTable, string[] astrForeignField)
        {
            bool bGotPrimaryKey = false;
            bool bGotUniqueKey = false;

            DataColumn[] acolPrimaryKey;
            DataColumn[] acolForeignKey;
            ArrayList alForeignKey = new ArrayList();
            DataTable tblParent;
            DataTable tblForeign;

            string strForeignKeys = "";
            string strRelationName;

            if (astrForeignField == null || astrForeignField.Length == 0)
            {
                throw new ArgumentNullException("astrForeignField", "At least 1 field name must be specified");
            }

            tblParent = objParentTable.Data;
            tblForeign = this.Data;
            foreach (string strForeignField in astrForeignField)
            {
                if (!tblForeign.Columns.Contains(strForeignField))
                {
                    throw new ArgumentException("Invalid field name: " + strForeignField, "astrForeignField");
                }
                strForeignKeys += strForeignField + "|";
            }

            bGotPrimaryKey = (tblParent.PrimaryKey != null && tblParent.PrimaryKey.Length > 0);
            if (!bGotPrimaryKey && mtblData.Constraints != null)
            {
                bGotUniqueKey = tblParent.Constraints.Contains(TABLE_UNIQUE_CONSTRAINT);
            }

            if (!bGotPrimaryKey && !bGotUniqueKey)
            {
                throw new DataException.NoPrimaryOrUniqueKeyAvailable();
            }

            strRelationName = tblParent.TableName + "FK" + this.TableName + "|" + strForeignKeys;
            if (this.ParentRelations.Contains(strRelationName))
            {
                throw new DataException.ParentRelationAlreadyExist();
            }

            if (bGotPrimaryKey)
            {
                acolPrimaryKey = tblParent.PrimaryKey;
            }
            else
            {
                UniqueConstraint objUniqueConstraint;
                objUniqueConstraint = (UniqueConstraint)tblParent.Constraints[TABLE_UNIQUE_CONSTRAINT];
                acolPrimaryKey = objUniqueConstraint.Columns;
            }

            foreach (string strForeignField in astrForeignField)
            {
                alForeignKey.Add(tblForeign.Columns[strForeignField]);
            }
            acolForeignKey = (DataColumn[])alForeignKey.ToArray(typeof(System.Data.DataColumn));

            return (this.ParentRelations.Add(strRelationName, objParentTable, this, acolPrimaryKey, acolForeignKey));
        }

        /// <summary>
        /// Adds foreign relationship with the specified foreign <see cref="DBHelper.SqlClient.SqlTableHelper"/>
        /// instance and foreign keys.
        /// </summary>
        /// <remarks>
        /// This method will adds the foreign relation to this instance's child relation collection.
        /// The relation name format is:-<br/><br/>
        /// [ParentTableName]FK[ForeignTableName]|[ForeignFieldName]|[ForeignFieldName]..., e.g. <br/><br/>
        /// x_PrimaryFKx_Foreign|CustIC|<br/><br/>
        /// When adding foreign relationship using this method, primary key or unique instance
        /// in parent table will be used as parent key.
        /// </remarks>
        /// <param name="objForeignTable">A SqlTableHelper instance.</param>
        /// <param name="astrForeignField">An array consists of foreign keys' field names.</param>
        /// <returns><see cref="DBHelper.DBRelation"/> intance that represents the database 
        /// relationship.</returns>
        /// <exception cref="System.ArgumentNullException">
        /// <i>astrForeignField</i> parameter is not provided,<br/>
        /// or<br/>
        /// Zero length array is provided.
        /// </exception>
        /// <exception cref="System.ArgumentException">
        /// Wrong field name is specified for foreign keys.
        /// </exception>
        /// <exception cref="DBHelper.DataException.NoPrimaryOrUniqueKeyAvailable">
        /// No primary keys or uique keys is available for parent table.
        /// </exception>
        /// <exception cref="DBHelper.DataException.ForeignRelationAlreadyExist">
        /// The foreign relation is already exist.
        /// </exception>
        /// <example>
        /// <code>
        /// mobjPrimaryTable.AddForeignKeys(mobjForeignTable, new string[] {"CustIC"});
        /// </code>
        /// </example>
        public DBRelation AddForeignKeys(SqlTableHelper objForeignTable, string[] astrForeignField)
        {
            bool bGotPrimaryKey = false;
            bool bGotUniqueKey = false;

            DataColumn[] acolPrimaryKey;
            DataColumn[] acolForeignKey;
            ArrayList alForeignKey = new ArrayList();
            DataTable tblForeign;

            string strForeignKeys = "";
            string strRelationName;

            if (astrForeignField == null || astrForeignField.Length == 0)
            {
                throw new ArgumentNullException("astrForeignField", "At least 1 field name must be specified");
            }

            tblForeign = objForeignTable.Data;
            foreach (string strForeignField in astrForeignField)
            {
                if (!tblForeign.Columns.Contains(strForeignField))
                {
                    throw new ArgumentException("Invalid field name: " + strForeignField, "astrForeignField");
                }
                strForeignKeys += strForeignField + "|";
            }

            bGotPrimaryKey = (mtblData.PrimaryKey != null && mtblData.PrimaryKey.Length > 0);
            if (!bGotPrimaryKey && mtblData.Constraints != null)
            {
                bGotUniqueKey = mtblData.Constraints.Contains(TABLE_UNIQUE_CONSTRAINT);
            }

            if (!bGotPrimaryKey && !bGotUniqueKey)
            {
                throw new DataException.NoPrimaryOrUniqueKeyAvailable();
            }

            strRelationName = mtblData.TableName + "FK" + objForeignTable.TableName + "|" + strForeignKeys;
            if (this.ChildRelations.Contains(strRelationName))
            {
                throw new DataException.ForeignRelationAlreadyExist();
            }

            if (bGotPrimaryKey)
            {
                acolPrimaryKey = mtblData.PrimaryKey;
            }
            else
            {
                UniqueConstraint objUniqueConstraint;
                objUniqueConstraint = (UniqueConstraint)mtblData.Constraints[TABLE_UNIQUE_CONSTRAINT];
                acolPrimaryKey = objUniqueConstraint.Columns;
            }

            foreach (string strForeignField in astrForeignField)
            {
                alForeignKey.Add(objForeignTable.Data.Columns[strForeignField]);
            }
            acolForeignKey = (DataColumn[])alForeignKey.ToArray(typeof(System.Data.DataColumn));

            return (this.ChildRelations.Add(strRelationName, this, objForeignTable, acolPrimaryKey, acolForeignKey));
        }

        #endregion

        #region SqlCommand Preparation Methods
        /// <summary>
        /// Return an array of all fields' name that can be updated.
        /// </summary>
        /// <remarks>
        /// An auto incremented field and timestamp field cannot be updated.
        /// </remarks>
        /// <returns>
        /// A string array consists of all fields that can be updated.
        /// </returns>
        private string[] GetAllFieldsToUpdate()
        {
            ArrayList alFieldToUpdate;
            string[] astrFieldToUpdate;
            
            alFieldToUpdate = new ArrayList();
            foreach (Field objField in mhstField.Values)
            {
                if (!objField.IsIdentity && !objField.IsTimeStamp)
                {
                    alFieldToUpdate.Add(objField.FieldName);
                }
            }
            astrFieldToUpdate = (string[])alFieldToUpdate.ToArray(typeof(string));

            return (astrFieldToUpdate);
        }

        /// <summary>
        /// Gets the sort criteria (if any) for select command.
        /// </summary>
        /// <returns>Sort criteria or Empty if sort criteria is not specified.</returns>
        private string GetSortCriteria()
        {
            string strReturned;

            if (mstrSort == null || mstrSort == "")
            {
                strReturned = "";
            }
            else
            {
                strReturned = " ORDER BY " + mstrSort;
            }

            return (strReturned);
        }
        
        /// <summary>
        /// Gets an array of columns representing table's primary keys or unique constraints.
        /// </summary>
        /// <returns>An array of columns.</returns>
        private DataColumn[] GetCriteriaKeyColumns()
        {
            DataColumn[] aCol = null;

            if (mtblData.PrimaryKey != null && mtblData.PrimaryKey.Length > 0)
            {
                aCol = mtblData.PrimaryKey;   
            }
            else if (mtblData.Constraints.Contains("TableUniqueConstraint"))
            {
                aCol = ((UniqueConstraint)mtblData.Constraints["TableUniqueConstraint"]).Columns;
            }

            return (aCol);
        }

        /// <summary>
        /// Prepares Sql parameters and base strings. They are used in database operations
        /// that involved criteria using primary key or unique constraint.
        /// </summary>
        /// <remarks>
        /// Parameters' name is start with "@k_[FieldName]", e.g. "@k_IC".
        /// </remarks>
        private void PrepareCriteriaKey()
        {
            ArrayList alCriteriaKey = new ArrayList(5);
            StringBuilder strbCriteriaKey = new StringBuilder(100);
            DataColumn[] aCol = null;
            SqlParameter parameter;
            Field objField;

            aCol = GetCriteriaKeyColumns();

            if (aCol != null)
            {
                foreach (DataColumn col in aCol)
                {
                    objField = GetField(col.ColumnName);
                    parameter = new SqlParameter("@k_"+objField.FieldName,objField.GetFieldTypeAsSqlDbType(), objField.Length,
                        ParameterDirection.Input, objField.IsNullable, (byte)objField.Precision, (byte)objField.Scale,
                        objField.FieldName, DataRowVersion.Original, null);
                    alCriteriaKey.Add(parameter);
                    strbCriteriaKey.Append(DBCommon.DBField(objField.FieldName)+"=@k_"+objField.FieldName+
                        " AND ");
                }             
            }
            maparCriteriaKey = (SqlParameter[])alCriteriaKey.ToArray(typeof(SqlParameter));
            mstrCriteriaKey = (strbCriteriaKey.Length > 0) ? strbCriteriaKey.ToString(0, strbCriteriaKey.Length-5)
                : "";
        }

        /// <summary>
        /// Prepares a select command.
        /// </summary>
        /// <remarks>
        /// The select command will select fields specified by 
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToSelect"/> only.
        /// If <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToSelect"/> is not specified,
        /// it will select all fields.<br/>
        /// By default, primary keys' columns or unique constraint's column will be always selected
        /// even if it is not specified in <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToSelect"/>.
        /// </remarks>
        /// <exception cref="System.ArgumentException">
        /// Invalid field name is specified in 
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToSelect"/>.
        /// </exception>
        private void PrepareSelectCommand()
        {
            string[] astrFieldToSelect = null;
            string strFieldToSelect = null;
            string strTempField;

            if (mstrFieldsToSelect != null && mstrFieldsToSelect != "")
            {
                astrFieldToSelect = mstrFieldsToSelect.Split(new Char[] {';'});
            }
            if (astrFieldToSelect != null && astrFieldToSelect.Length > 0)
            {
                StringBuilder strbFields = new StringBuilder();
                DataColumn[] acolCriteria = GetCriteriaKeyColumns();
                bool bIsCriteria;
                string strCriteriaKey = "";

                if (acolCriteria != null)
                {
                    foreach (DataColumn colCriteria in acolCriteria)
                    {
                        strCriteriaKey += DBCommon.DBField(colCriteria.ColumnName) + ",";
                    }
                }

                foreach (string strField in astrFieldToSelect)
                {
                    strTempField = strField.Trim();
                    bIsCriteria = false;

                    if (acolCriteria != null)
                    {    
                        foreach (DataColumn colCriteria in acolCriteria)
                        {
                            if (colCriteria.ColumnName.ToUpper() == strTempField.ToUpper())
                            {
                                bIsCriteria = true;
                                break;
                            }
                        }
                    }

                    if (!bIsCriteria)
                    {
                        if (!mtblData.Columns.Contains(strTempField))
                        {
                            throw new ArgumentException("Invalid field name: " + strTempField, "FieldsToSelect");
                        }
                        strbFields.Append(DBCommon.DBField(strTempField) + ",");
                    }
                }
                strbFields.Insert(0, strCriteriaKey);
                strbFields.Remove(strbFields.Length-1, 1);
                strFieldToSelect = strbFields.ToString();
            }
            else
            {
                strFieldToSelect = "*";
            }

            mcmnSelect = new SqlCommand();
            mcmnSelect.CommandType = CommandType.Text;
            mstrBaseSelectCommandText = String.Format("SELECT {0} FROM {1}", strFieldToSelect, mstrTableName);
        }

        /// <summary>
        /// Prepares a insert command.
        /// </summary>
        /// <remarks>
        /// This method will prepares output parameter for identity column so that the identity
        /// value can be retrieved from database after insert.
        /// </remarks>
        private void PrepareInsertCommand()
        {
            mcmnInsert = new SqlCommand();
            mcmnInsert.CommandType = CommandType.Text;
            foreach (Field objField in mhstField.Values)
            {
                if (!objField.IsTimeStamp)
                {
                    SqlParameter parameter;
                    if (objField.IsIdentity)
                    {
                        parameter = new SqlParameter("@"+objField.FieldName,objField.GetFieldTypeAsSqlDbType(), 
                            objField.Length, ParameterDirection.Output, objField.IsNullable, 
                            (byte)objField.Precision, (byte)objField.Scale, objField.FieldName,
                            DataRowVersion.Current, null);
                        mhstBaseInsertOutputParameter[objField.FieldName] = parameter;
                        mcmnInsert.Parameters.Add(parameter);
                        mstrSelectIdentity = "SELECT @" + objField.FieldName + " = SCOPE_IDENTITY()";
                    }
                    else
                    {
                        parameter = new SqlParameter("@"+objField.FieldName,objField.GetFieldTypeAsSqlDbType(), 
                            objField.Length, ParameterDirection.Input, objField.IsNullable, 
                            (byte)objField.Precision, (byte)objField.Scale, objField.FieldName,
                            DataRowVersion.Current, null);
                        mhstBaseInsertInputParameter[objField.FieldName] = parameter;
                    }
                }
            }
        }

        /// <summary>
        /// Prepares a update command.
        /// </summary>
        /// <remarks>
        /// The update command will update fields specified by 
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToUpdate"/> only.
        /// If <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToUpdate"/> is not specified,
        /// it will update all fields. Identity and timestamp fields cannot be updated.
        /// </remarks>
        /// <exception cref="System.ArgumentException">
        /// Invalud field name is specified in 
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToUpdate"/>.<br/>
        /// or<br/>
        /// Identity field is specified in
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToUpdate"/>.<br/>
        /// or<br/>
        /// Timestamp field is specified in
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToUpdate"/>.<br/>
        /// </exception>
        private void PrepareUpdateCommand()
        {
            string strTempField;
            string[] astrFieldToUpdate = null;
            StringBuilder strbUpdate = new StringBuilder(200);

            if (mstrFieldsToUpdate != null && mstrFieldsToUpdate != "")
            {
                astrFieldToUpdate = mstrFieldsToUpdate.Split(new Char[] {';'});
            }
            if (astrFieldToUpdate != null && astrFieldToUpdate.Length > 0)
            {
                foreach (string strField in astrFieldToUpdate)
                {
                    strTempField = strField.Trim();
                    if (!mtblData.Columns.Contains(strTempField))
                    {
                        throw new ArgumentException("Invalid field name: " + strTempField, "FieldsToUpdate");
                    }
                    if (mtblData.Columns[strTempField].AutoIncrement)
                    {
                        throw new ArgumentException("Auto incremented field - " + strTempField + 
                                        " cannot be updated", "FieldsToUpdate");
                    }
                    if (GetField(strTempField).IsTimeStamp)
                    {
                        throw new ArgumentException("Timestamp field - " + strTempField + 
                                        " cannot be updated", "FieldsToUpdate");
                    }
                }
            }
            else
            {
                astrFieldToUpdate = GetAllFieldsToUpdate(); 
            }

            mcmnUpdate = new SqlCommand();
            mcmnUpdate.CommandType = CommandType.Text;
            foreach (string strField in astrFieldToUpdate)
            {
                SqlParameter parameter;
                Field objField;
                strbUpdate.Append(DBCommon.DBField(strField)+"=@"+strField +",");
                objField = GetField(strField);
                parameter = new SqlParameter("@"+strField,objField.GetFieldTypeAsSqlDbType(), objField.Length,
                    ParameterDirection.Input, objField.IsNullable, (byte)objField.Precision, (byte)objField.Scale,
                    strField, DataRowVersion.Current, null);
                mcmnUpdate.Parameters.Add(parameter);
            }
            strbUpdate.Remove(strbUpdate.Length-1, 1);
            mstrBaseUpdateCommandText = String.Format("UPDATE {0} SET {1}", mstrTableName, strbUpdate.ToString());
        }

        /// <summary>
        /// Prepares a delete command.
        /// </summary>
        private void PrepareDeleteCommand()
        {
            mcmnDelete = new SqlCommand();
            mcmnDelete.CommandType = CommandType.Text;
            mstrBaseDeleteCommandText = String.Format("Delete FROM {0}", mstrTableName);
        }

        /// <summary>
        /// Prepares a data adapter.
        /// </summary>
        private void PrepareDataAdapter()
        {   
            madpMain = new SqlDataAdapter();
        }
        #endregion

        #region Database Access Methods
        /// <summary>
        /// Select rows from the table based on requested return rows.
        /// </summary>
        /// <remarks>
        /// This method will select columns specified in 
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToSelect"/> only.
        /// </remarks>
        /// <param name="iTotalRow">The total rows or percentage rows to return.</param>
        /// <param name="bPercent">True if percentage is specified for returned rows otherwise False.</param>
        /// <returns><see cref="System.Data.DataTable"/> object if succeeded, otherwise an Exception 
        /// is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.Compile"/> is not called first.
        /// </exception>
        /// <example>
        /// <code>
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.SelectCriteria = mtxtFilterCriteria.Text;
        /// mobjTableHelper.Data.Clear();
        /// mobjTableHelper.Sort = "IC DESC, OldIC DESC";
        /// tblData = mobjTableHelper.SelectTop(2, false);          
        /// mdgrData.DataSource = tblData;
        /// mobjCnnProvider.CloseConnection();
        /// </code>
        /// </example>
        public DataTable SelectTop(int iTotalRow, bool bPercent)
        {
            string strSelectTopText;

            miErrorCode = (int)DBError.AllOk;

            if (mcmnSelect == null)
            {
                throw new InvalidOperationException("Compile() is not called first before calling of SelectTop()");
            }

            SqlDataAdapter adpSelect = new SqlDataAdapter(mcmnSelect);

            try
            {
                mcmnSelect.Connection = mcnnMainConnection;
                if (bPercent) 
                {
                    strSelectTopText = String.Format(" TOP {0} PERCENT", iTotalRow);
                }
                else
                {
                    strSelectTopText = String.Format(" TOP {0}", iTotalRow);
                }
                mcmnSelect.CommandText = mstrBaseSelectCommandText.Insert(6, strSelectTopText);
                if (mstrSelectCriteria != null && mstrSelectCriteria != "")
                {
                    mcmnSelect.CommandText += " WHERE " + mstrSelectCriteria;
                }
                mcmnSelect.CommandText += GetSortCriteria();
                
                if(mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                }
                else
                {
                    if(mobjMainConnectionProvider.IsTransactionPending)
                    {
                        mcmnSelect.Transaction = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }
                adpSelect.Fill(mtblData);

                return (mtblData);
            }
            catch (Exception ex)
            {
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
            finally
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
                adpSelect.Dispose();
            }
        }

        /// <summary>
        /// Select rows from the table based on <see cref="DBHelper.SqlClient.SqlTableHelper.SelectCriteria"/>.
        /// </summary>
        /// <remarks>
        /// This method will select columns specified in 
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToSelect"/> only.
        /// </remarks>
        /// <returns><see cref="System.Data.DataTable"/> object if succeeded, otherwise an Exception 
        /// is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.Compile"/> is not called first.
        /// </exception>
        /// <example>
        /// <code>
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.SelectCriteria = mtxtFilterCriteria.Text;
        /// mobjTableHelper.Data.Clear();
        /// mobjTableHelper.SelectCriteria = "IC = '800101069944'";
        /// mobjTableHelper.Sort = "IC DESC, OldIC DESC";
        /// tblData = mobjTableHelper.SelectSome();          
        /// mdgrData.DataSource = tblData;
        /// mobjCnnProvider.CloseConnection();
        /// </code>
        /// </example>
        public DataTable SelectSome()
        {
            miErrorCode = (int)DBError.AllOk;

            if (mcmnSelect == null)
            {
                throw new InvalidOperationException("Compile() is not called first before calling of SelectSome()");
            }

            SqlDataAdapter adpSelect = new SqlDataAdapter(mcmnSelect);

            try
            {
                mcmnSelect.Connection = mcnnMainConnection;
                mcmnSelect.CommandText = mstrBaseSelectCommandText;
                if (mstrSelectCriteria != null && mstrSelectCriteria != "")
                {
                    mcmnSelect.CommandText += " WHERE " + mstrSelectCriteria;
                }
                mcmnSelect.CommandText += GetSortCriteria();

                if(mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                }
                else
                {
                    if(mobjMainConnectionProvider.IsTransactionPending)
                    {
                        mcmnSelect.Transaction = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }
                adpSelect.Fill(mtblData);

                return (mtblData);
            }
            catch (Exception ex)
            {
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
            finally
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
                adpSelect.Dispose();
            }
        }

        /// <summary>
        /// Select all rows from the table.
        /// </summary>
        /// <remarks>
        /// This method will select columns specified in 
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToSelect"/> only.
        /// </remarks>
        /// <returns><see cref="System.Data.DataTable"/> object if succeeded, otherwise an Exception 
        /// is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.Compile"/> is not called first.
        /// </exception>
        /// <example>
        /// <code>
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.Data.Clear();
        /// mobjTableHelper.Sort = "IC DESC, OldIC DESC";
        /// tblData = mobjTableHelper.SelectAll();          
        /// mdgrData.DataSource = tblData;
        /// mobjCnnProvider.CloseConnection();
        /// </code>
        /// </example>
        public DataTable SelectAll()
        {
            miErrorCode = (int)DBError.AllOk;

            if (mcmnSelect == null)
            {
                throw new InvalidOperationException("Compile() is not called first before calling of SelectAll()");
            }

            SqlDataAdapter adpSelect = new SqlDataAdapter(mcmnSelect);

            try
            {
                mcmnSelect.Connection = mcnnMainConnection;
                mcmnSelect.CommandText = mstrBaseSelectCommandText + GetSortCriteria();
                if(mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                }
                else
                {
                    if(mobjMainConnectionProvider.IsTransactionPending)
                    {
                        mcmnSelect.Transaction = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }
                adpSelect.Fill(mtblData);

                return (mtblData);
            }
            catch (Exception ex)
            {
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
            finally
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
                adpSelect.Dispose();
            }
        }

        /// <summary>
        /// Insert rows from <see cref="System.Data.DataTable"/> object
        /// obtained from this instance.
        /// </summary>
        /// <remarks>
        /// This method will insert all rows in DataTable object. If a column is identity,
        /// the identity value will be retrieved and populated to the column after insert.
        /// Total of inserted rows can be retrieved from 
        /// <see cref="DBHelper.DBInteractionBase.RowsAffected"/> after each successful
        /// insertion<br/>
        /// .NET type default value will be used if no value is provided for non-nullable field.
        /// </remarks>
        /// <returns>True if succeeded, otherwise an Exception is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.Compile"/> is not called first.
        /// </exception>
        /// <example>
        /// <code>
        /// DataTable tblData = mobjTableHelper.Data;
        /// tblData.Rows.Clear();
        /// DataRow row = tblData.NewRow();
        /// row["IC"] = "090909055514";
        /// row["Name"] = "Leo";
        /// tblData.Rows.Add(row);
        /// row = tblData.NewRow();
        /// row["IC"] = "090909055515";
        /// row["Name"] = "Frodo";
        /// tblData.Rows.Add(row);
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.Insert();
        /// mobjCnnProvider.CloseConnection();
        /// mdgrData.DataSource = tblData;
        /// Console.WriteLine("Rows Affected:" + mobjTableHelper.RowsAffected);
        /// </code>
        /// </example>
        public bool Insert()
        {
            miErrorCode = (int)DBError.AllOk;
            miRowsAffected = 0;
            StringBuilder strbInsertField = new StringBuilder(200);
            StringBuilder strbInsertValue = new StringBuilder(200);

            if (mcmnInsert == null)
            {
                throw new InvalidOperationException("Compile() is not called first before calling of Insert()");
            }

            try
            {
                mcmnInsert.Connection = mcnnMainConnection;
                if (mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                    mcmnInsert.Transaction = mcnnMainConnection.BeginTransaction();
                }
                else
                {
                    if(mobjMainConnectionProvider.IsTransactionPending)
                    {
                        mcmnInsert.Transaction = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }
                
                foreach (DataRow row in mtblData.Rows)
                {
                    strbInsertField.Remove(0, strbInsertField.Length);
                    strbInsertValue.Remove(0, strbInsertValue.Length);
                    foreach (DictionaryEntry entry in mhstBaseInsertInputParameter)
                    {
                        bool bAdd = true;
                        string strFieldName = entry.Key.ToString();
                        SqlParameter parameter = (SqlParameter)entry.Value;
                        object objValue = row[strFieldName];
                        if (objValue.Equals(DBNull.Value))
                        {
                            Field objField = GetField(strFieldName);
                            if (!objField.IsNullable)
                            {
                                if (objField.DefaultValue.Length == 0)
                                {
                                    objValue = objField.GetFieldNetTypeDefaultValue();
                                }
                                else
                                {
                                    bAdd = false;
                                }
                            }
                        }
                        if (bAdd)
                        {
                            strbInsertField.Append(DBCommon.DBField(strFieldName)+",");
                            strbInsertValue.Append("@"+strFieldName+",");
                            parameter.Value = objValue;
                            if (!mcmnInsert.Parameters.Contains(parameter))
                            {
                                mcmnInsert.Parameters.Add(parameter);
                            }
                        }
                        else if (mcmnInsert.Parameters.Contains(parameter))
                        {
                            mcmnInsert.Parameters.Remove(parameter);
                        }
                    }
                    mcmnInsert.CommandText = String.Format("INSERT INTO {0} ({1}) VALUES({2})",
                        mstrTableName, strbInsertField.ToString(0, strbInsertField.Length-1),
                        strbInsertValue.ToString(0, strbInsertValue.Length-1))+";"+mstrSelectIdentity;
                    miRowsAffected += mcmnInsert.ExecuteNonQuery();
                    foreach (DictionaryEntry entry in mhstBaseInsertOutputParameter)
                    {
                        string strFieldName = entry.Key.ToString();
                        SqlParameter parameter = (SqlParameter)entry.Value;
                        row[strFieldName] = parameter.Value;
                    }
                }

                if (mbMainConnectionIsCreatedLocal)
                {
                    mcmnInsert.Transaction.Commit();
                }
                return (true);
            }
            catch (Exception ex)
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    try
                    {
                        mcmnInsert.Transaction.Rollback();
                    }
                    catch (Exception ignored) { }
                }
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
            finally
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
            }
        }

        /// <summary>
        /// Update rows in <see cref="System.Data.DataTable"/> based on 
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.CriteriaType"/>.
        /// </summary>
        /// <remarks>
        /// <p>
        /// This method will update columns specified in 
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.FieldsToUpdate"/> only.
        /// </p>
        /// Rows are updated based on selected criteria type - <see cref="DBHelper.DBCriteria"/>:
        /// <UL>
        /// <LI>None - Update all rows in database by using data from first row in DataTable.</LI>
        /// <LI>UseBoth - Update rows thats match primary key/unique constraint of rows in DataTable 
        /// and filter expression by using DataTable's data row by row.</LI>
        /// <LI>UseFilterExpression - Update rows thats match filter expression by using data
        /// from first row in DataTable.</LI>
        /// <LI>UsePrimaryKey - Update rows thats match primary key/unique constraint of rows in DataTable
        /// by using DataTable's data row by row.</LI>
        /// </UL>
        /// <p>
        /// Total of updated rows can be retrieved from 
        /// <see cref="DBHelper.DBInteractionBase.RowsAffected"/> after each successful update.
        /// </p>
        /// </remarks>
        /// <returns>True if succeeded, otherwise an Exception is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.Compile"/> is not called first.
        /// </exception>
        /// <example>
        /// Example of using DBCriteria.<see cref="DBHelper.DBCriteria.UsePrimaryKey"/>
        /// <code>
        /// DataTable tblData = mobjTableHelper.SelectTop(2, false);
        /// DataRow row = tblData.Rows[1];
        /// row.BeginEdit();
        /// row["Name"] = "Xeon";
        /// mobjTableHelper.CriteriaType = DBCriteria.UsePrimaryKey;
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.Update();
        /// mobjCnnProvider.CloseConnection();
        /// tblData.AcceptChanges();
        /// </code>
        /// Example of using DBCriteria.<see cref="DBHelper.DBCriteria.UseFilterExpression"/>
        /// <code>
        /// mobjTableHelper.Data.Clear();
        /// DataTable tblData = mobjTableHelper.Data;
        /// DataRow row = tblData.NewRow();
        /// row["IC"] = "090909079911";
        /// tblData.Rows.Add(row);
        /// mobjTableHelper.UpdateCriteria = "name = 'Test'";
        /// mobjTableHelper.CriteriaType = DBCriteria.UseFilterExpression;
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.Update();
        /// mobjCnnProvider.CloseConnection();
        /// </code>
        /// Example of using DBCriteria.<see cref="DBHelper.DBCriteria.UseBoth"/>
        /// <code>
        /// DataTable tblData = mobjTableHelper.SelectTop(2, false);
        /// DataRow row = tblData.Rows[1];
        /// row.BeginEdit();
        /// row["Name"] = "Xeon";
        /// mobjTableHelper.UpdateCriteria = "name = 'Test'";
        /// mobjTableHelper.CriteriaType = DBCriteria.UseBoth;
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.Update();
        /// mobjCnnProvider.CloseConnection();
        /// tblData.AcceptChanges();
        /// </code>
        /// Example of using DBCriteria.<see cref="DBHelper.DBCriteria.None"/>
        /// <code>
        /// mobjTableHelper.Data.Clear();
        /// DataTable tblData = mobjTableHelper.Data;
        /// DataRow row = tblData.NewRow();
        /// row["Name"] = "Xeon";
        /// tblData.Rows.Add(row);
        /// mobjTableHelper.CriteriaType = DBCriteria.None;
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.Update();
        /// mobjCnnProvider.CloseConnection();
        /// </code>
        /// </example>
        public bool Update()
        {
            bool bGotCriteriaKey = (maparCriteriaKey != null && maparCriteriaKey.Length > 0);

            miErrorCode = (int)DBError.AllOk;
            miRowsAffected = 0;

            if (mcmnUpdate == null)
            {
                throw new InvalidOperationException("Compile() is not called first before calling of Update()");
            }

            try
            {
                mcmnUpdate.CommandText = mstrBaseUpdateCommandText;
                if (menuCriteriaType == DBCriteria.UseBoth)
                {
                    if (bGotCriteriaKey)
                    {
                        mcmnUpdate.CommandText += " WHERE " + mstrCriteriaKey;
                        SqlUtils.AttachParameters(mcmnUpdate, maparCriteriaKey);
                        if (mstrUpdateCriteria != null && mstrUpdateCriteria != "")
                        {
                            mcmnUpdate.CommandText += " AND " + mstrUpdateCriteria;
                        }
                    }
                    else if (mstrUpdateCriteria != null && mstrUpdateCriteria != "")
                    {
                        mcmnUpdate.CommandText += " WHERE " + mstrUpdateCriteria;
                    }
                }
                else if (menuCriteriaType == DBCriteria.UseFilterExpression)
                {
                    if (mstrUpdateCriteria != null && mstrUpdateCriteria != "")
                    {
                        mcmnUpdate.CommandText += " WHERE " + mstrUpdateCriteria;
                    }
                }
                else if (menuCriteriaType == DBCriteria.UsePrimaryKey)
                {
                    if (bGotCriteriaKey)
                    {
                        mcmnUpdate.CommandText += " WHERE " + mstrCriteriaKey;
                        SqlUtils.AttachParameters(mcmnUpdate, maparCriteriaKey);
                    }
                }
                
                mcmnUpdate.Connection = mcnnMainConnection;
                if (mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                    mcmnUpdate.Transaction = mcnnMainConnection.BeginTransaction();
                }
                else
                {
                    if(mobjMainConnectionProvider.IsTransactionPending)
                    {
                        mcmnUpdate.Transaction = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }
                
                if (menuCriteriaType == DBCriteria.None || menuCriteriaType == DBCriteria.UseFilterExpression)
                {
                    if (mtblData.Rows.Count > 0)
                    {
                        DataRow row = mtblData.Rows[0];
                        foreach (SqlParameter parameter in mcmnUpdate.Parameters)
                        {
                            parameter.Value = row[parameter.ParameterName.Substring(1)];
                        }
                        miRowsAffected = mcmnUpdate.ExecuteNonQuery();
                    }
                }
                else
                {
                    foreach (DataRow row in mtblData.Rows)
                    {
                        foreach (SqlParameter parameter in mcmnUpdate.Parameters)
                        {
                            if (parameter.ParameterName.IndexOf("@k_") == 0)
                            {
                                parameter.Value = row[parameter.ParameterName.Substring(3), DataRowVersion.Original];
                            }
                            else
                            {
                                parameter.Value = row[parameter.ParameterName.Substring(1)];
                            }
                        }
                        miRowsAffected += mcmnUpdate.ExecuteNonQuery();
                    }
                }
                
                if (menuCriteriaType == DBCriteria.UseBoth || menuCriteriaType == DBCriteria.UsePrimaryKey)
                {
                    if (bGotCriteriaKey)
                    {
                        SqlUtils.DetachParameters(mcmnUpdate, maparCriteriaKey);
                    }
                }

                if (mbMainConnectionIsCreatedLocal)
                {
                    mcmnUpdate.Transaction.Commit();
                }
                return (true);
            }
            catch (Exception ex)
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    try
                    {
                        mcmnUpdate.Transaction.Rollback();
                    }
                    catch (Exception ignored) { }
                }
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
            finally
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
            }
        }

        /// <summary>
        /// Delete rows from <see cref="System.Data.DataTable"/> based on
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.CriteriaType"/>.
        /// </summary>
        /// <remarks>
        /// Rows are deleted based on selected criteria type - <see cref="DBHelper.DBCriteria"/>:
        /// <UL>
        /// <LI>None - Delete all rows in database.</LI>
        /// <LI>UseBoth - Delete rows in database thats match primary key/unique constraint of data in DataTable 
        /// and filter expression.</LI>
        /// <LI>UseFilterExpression - Delete rows in database thats match filter expression.</LI>
        /// <LI>UsePrimaryKey - Delete rows in database thats match primary key/unique constraint of data 
        /// in DataTable.</LI>
        /// </UL>
        /// <p>
        /// Total of deleted rows can be retrieved from 
        /// <see cref="DBHelper.DBInteractionBase.RowsAffected"/> after each successful deletion.
        /// </p>
        /// </remarks>
        /// <returns>True if succeeded, otherwise an Exception is thrown.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.Compile"/> is not called first.
        /// </exception>
        /// <example>
        /// Example of using DBCriteria.<see cref="DBHelper.DBCriteria.UsePrimaryKey"/>
        /// <code>
        /// mobjTableHelper.Data.Clear();
        /// DataTable tblData = mobjTableHelper.Data;
        /// DataRow row = tblData.NewRow();
        /// row["IC"] = "090909079911";
        /// tblData.Rows.Add(row);
        /// mobjTableHelper.CriteriaType = DBCriteria.UsePrimaryKey;
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.Delete();
        /// mobjCnnProvider.CloseConnection();
        /// </code>
        /// Example of using DBCriteria.<see cref="DBHelper.DBCriteria.UseFilterExpression"/>
        /// <code>
        /// mobjTableHelper.DeleteCriteria = "name = ='test'";
        /// mobjTableHelper.CriteriaType = DBCriteria.UseFilterExpression;
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.Delete();
        /// mobjCnnProvider.CloseConnection();
        /// </code>
        /// Example of using DBCriteria.<see cref="DBHelper.DBCriteria.UseBoth"/>
        /// <code>
        /// mobjTableHelper.Data.Clear();
        /// DataTable tblData = mobjTableHelper.Data;
        /// DataRow row = tblData.NewRow();
        /// row["IC"] = "090909079911";
        /// tblData.Rows.Add(row);
        /// mobjTableHelper.DeleteCriteria = "name = 'Test'";
        /// mobjTableHelper.CriteriaType = DBCriteria.UseBoth;
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.Delete();
        /// mobjCnnProvider.CloseConnection();
        /// </code>
        /// Example of using DBCriteria.<see cref="DBHelper.DBCriteria.None"/>
        /// <code>
        /// mobjTableHelper.CriteriaType = DBCriteria.None;
        /// mobjCnnProvider.OpenConnection();
        /// mobjTableHelper.Delete();
        /// mobjCnnProvider.CloseConnection();
        /// </code>
        /// </example>
        public bool Delete()
        {
            bool bGotCriteriaKey = (maparCriteriaKey != null && maparCriteriaKey.Length > 0);

            miErrorCode = (int)DBError.AllOk;
            miRowsAffected = 0;

            if (mcmnDelete == null)
            {
                throw new InvalidOperationException("Compile() is not called first before calling of Delete()");
            }

            try
            {
                mcmnDelete.CommandText = mstrBaseDeleteCommandText;
                if (menuCriteriaType == DBCriteria.UseBoth)
                {
                    if (bGotCriteriaKey)
                    {
                        mcmnDelete.CommandText += " WHERE " + mstrCriteriaKey;
                        SqlUtils.AttachParameters(mcmnDelete, maparCriteriaKey);
                        if (mstrDeleteCriteria != null && mstrDeleteCriteria != "")
                        {
                            mcmnDelete.CommandText += " AND " + mstrDeleteCriteria;
                        }
                    }
                    else if (mstrDeleteCriteria != null && mstrDeleteCriteria != "")
                    {
                        mcmnDelete.CommandText += " WHERE " + mstrDeleteCriteria;
                    }
                }
                else if (menuCriteriaType == DBCriteria.UseFilterExpression)
                {
                    if (mstrDeleteCriteria != null && mstrDeleteCriteria != "")
                    {
                        mcmnDelete.CommandText += " WHERE " + mstrDeleteCriteria;
                    }
                }
                else if (menuCriteriaType == DBCriteria.UsePrimaryKey)
                {
                    if (bGotCriteriaKey)
                    {
                        mcmnDelete.CommandText += " WHERE " + mstrCriteriaKey;
                        SqlUtils.AttachParameters(mcmnDelete, maparCriteriaKey);
                    }
                }
                
                mcmnDelete.Connection = mcnnMainConnection;
                if (mbMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    mcnnMainConnection.Open();
                    mcmnDelete.Transaction = mcnnMainConnection.BeginTransaction();
                }
                else
                {
                    if (mobjMainConnectionProvider.IsTransactionPending)
                    {
                        mcmnDelete.Transaction = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }
                
                if (menuCriteriaType == DBCriteria.None || menuCriteriaType == DBCriteria.UseFilterExpression)
                {
                    miRowsAffected = mcmnDelete.ExecuteNonQuery();
                }
                else
                {
                    foreach (DataRow row in mtblData.Rows)
                    {
                        foreach (SqlParameter parameter in mcmnDelete.Parameters)
                        {
                            parameter.Value = row[parameter.ParameterName.Substring(3)];
                        }
                        miRowsAffected += mcmnDelete.ExecuteNonQuery();
                    }
                }

                if (menuCriteriaType == DBCriteria.UseBoth || menuCriteriaType == DBCriteria.UsePrimaryKey)
                {
                    if (bGotCriteriaKey)
                    {
                        SqlUtils.DetachParameters(mcmnDelete, maparCriteriaKey);
                    }
                }

                if (mbMainConnectionIsCreatedLocal)
                {
                    mcmnDelete.Transaction.Commit();
                }
                return (true);
            }
            catch (Exception ex)
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    try
                    {
                        mcmnDelete.Transaction.Rollback();
                    }
                    catch (Exception ignored) { }
                }
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
            finally
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
            }
        }

        /// <summary>
        /// Calls the respective INSERT, UPDATE, or DELETE statements for each 
        /// inserted, updated, or deleted rows in the underlined <see cref="System.Data.DataTable"/> 
        /// instance using <see cref="System.Data.Common.DbDataAdapter.Update(DataTable)"/> method.
        /// </summary>
        /// <remarks>
        /// Insert, Update, and Delete commands are built automatically using 
        /// <see cref="System.Data.SqlClient.SqlCommandBuilder"/> based on select command built
        /// by <see cref="DBHelper.SqlClient.PrepareSelectCommand"/>.
        /// <br/>
        /// Total of updated rows can be retrieved from 
        /// <see cref="DBHelper.DBInteractionBase.RowsAffected"/> after each successful update.
        /// </remarks>
        /// <returns>The number of rows successfully updated from the underlined 
        /// <see cref="System.Data.DataTable"/> instance.</returns>
        /// <exception cref="System.InvalidOperationException">
        /// <see cref="DBHelper.SqlClient.SqlTableHelper.Compile"/> is not called first.
        /// </exception>
        /// <example>
        /// <code>
        /// DataRow row;
        /// DataTable tblData = mobjTableHelper.SelectAll();
        /// row = tblData.Select("id = 10")[0];
        /// row["Name"] = "eagle";
        /// row = tblData.NewRow();
        /// row["IC"] = "8107310421";
        /// row["Name"] = "wolf";
        /// tblData.Rows.Add(row);
        /// row = tblData.NewRow();
        /// row["IC"] = "8207310421";
        /// tblData.Rows.Add(row);
        /// row = tblData.Select("id = 11")[0];
        /// row.Delete();
        /// mobjCnnProvider.OpenConnection();
        /// int iTotalUpdated = mobjTableHelper.DBAdapterUpdate();
        /// mobjCnnProvider.CloseConnection();
        /// tblData.AcceptChanges();
        /// </code>
        /// </example>
        public int DBAdapterUpdate()
        {
            SqlCommand cmnSelect;
            SqlTransaction tra = null;
            SqlCommandBuilder cmnbMain;
            int iTotalUpdated;

            miErrorCode = (int)DBError.AllOk;
            miRowsAffected = 0;

            if (madpMain == null)
            {
                throw new InvalidOperationException("Compile() is not called first before calling of DBAdapterUpdate()");
            }

            cmnSelect = new SqlCommand(mstrBaseSelectCommandText, mcnnMainConnection);
            madpMain.SelectCommand = cmnSelect;
            cmnbMain = new SqlCommandBuilder(madpMain);
            try
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    // Open connection
                    mcnnMainConnection.Open();
                    tra = mcnnMainConnection.BeginTransaction();
                    madpMain.SelectCommand.Transaction = tra;
                }
                else
                {
                    if (mobjMainConnectionProvider.IsTransactionPending)
                    {
                        madpMain.SelectCommand.Transaction = mobjMainConnectionProvider.CurrentTransaction;
                    }
                }
                iTotalUpdated = madpMain.Update(mtblData);
                miRowsAffected = iTotalUpdated;

                if (mbMainConnectionIsCreatedLocal)
                {
                    tra.Commit();
                }

                return (iTotalUpdated);
            }
            catch (Exception ex)
            {
                if (mbMainConnectionIsCreatedLocal)
                {
                    try
                    {
                        tra.Rollback();
                    }
                    catch (Exception ignored) { }
                }
                if (ex is SqlException)
                {
                    SqlException exSql = (SqlException)ex;
                    miErrorCode = exSql.Number;
                }
                throw ex;
            }
            finally
            {
                cmnSelect.Dispose();
                cmnbMain.Dispose();
                if (mbMainConnectionIsCreatedLocal)
                {
                    mcnnMainConnection.Close();
                }
            }
        }

        /// <summary>
        /// Select rows based on specified select criteria.
        /// </summary>
        /// <param name="ictTableHelper">A TableHelper instance to select data from.</param>
        /// <param name="strSelectCriteria">Select criteria.</param>
        /// <returns>A <see cref="System.Data.DataTable"/> instance containing the selected rows.</returns>
        private DataTable SelectRows(ICommonDBAccess ictTableHelper, string strSelectCriteria)
        {
            string strTempSelectCriteria;
            DataTable tblData;

            strTempSelectCriteria = ictTableHelper.SelectCriteria;
            ictTableHelper.SelectCriteria = strSelectCriteria;
            tblData = ictTableHelper.SelectSome();
            ictTableHelper.SelectCriteria = strTempSelectCriteria;

            return (tblData);
        }

        /// <summary>
        /// Retrieves parent rows for the specified row and specified relation and a value
        /// specifying whehter to requery the rows from database needed.
        /// </summary>
        /// <remarks>
        /// If <i>bRequeryIfNeeded</i> is True, this method will try to retrieve parent rows
        /// from DataTable instance first; if none is returned, it will retrieve from database.
        /// If <i>bRequeryIfNeeded</i> is False, this method will retrieve parent rows from
        /// DataTable instance only.
        /// </remarks>
        /// <param name="rowToSelect">The specified child row.</param>
        /// <param name="objRelation">The specified relation.</param>
        /// <param name="bRequeryIfNeeded">True if requery from database when needed , otherwise False.</param>
        /// <param name="bQueryFromDB">Return True if the rows are retrieved from database, ohterwise
        /// False if the rows are retrieved from existing rows in DataTable instance.</param>
        /// <returns>The parent rows.</returns>
        /// <exception cref="DBHelper.DataException.ParentRowTableMismatch">
        /// Wrong relation is specified.
        /// </exception>
        public DataRow[] GetParentRowsDataTable(DataRow rowToSelect, DBRelation objRelation, 
            bool bRequeryIfNeeded, out bool bQueryFromDB)
        {
            return GetParentRows(rowToSelect, objRelation, bRequeryIfNeeded, false, out bQueryFromDB);
        }

        /// <summary>
        /// Retrieves parent rows for the specified row and specified relation.
        /// </summary>
        /// <remarks>
        /// This method always retrieves parent rows from database.
        /// </remarks>
        /// <param name="rowToSelect">The specified child row.</param>
        /// <param name="objRelation">The specified relation.</param>
        /// <returns>The parent rows.</returns>
        /// <exception cref="DBHelper.DataException.ParentRowTableMismatch">
        /// Wrong relation is specified.
        /// </exception>
        public DataRow[] GetParentRowsDatabase(DataRow rowToSelect, DBRelation objRelation)
        {
            bool bQueryFromDB;

            return GetParentRows(rowToSelect, objRelation, false, true, out bQueryFromDB);
        }

        /// <summary>
        /// Retrieves parent rows for the specified row and specified relation and a value
        /// specifying whehter to requery the rows from database when needed, and a value
        /// specifying whether to always requery the rows from database.
        /// </summary>
        /// <remarks>
        /// <i>bAlwaysRequery</i> take precedence over <i>bRequeryIfNeeded</i>.<br/>
        /// <p>
        /// If <i>bRequeryIfNeeded</i> is True, this method will try to retrieve parent rows
        /// from DataTable instance first; if none is returned, it will retrieve from database.
        /// If <i>bRequeryIfNeeded</i> is False, this method will retrieve parent rows from
        /// DataTable instance only.
        /// </p>
        /// </remarks>
        /// <param name="rowToSelect">The specified child row.</param>
        /// <param name="objRelation">The specified relation.</param>
        /// <param name="bRequeryIfNeeded">True if requery from database when needed , otherwise False.</param>
        /// <param name="bAlwaysRequery">True if always requery from database, ohterwise False.</param>
        /// <param name="bQueryFromDB">Return True if the rows are retrieved from database, ohterwise
        /// False if the rows are retrieved from existing rows in DataTable instance.</param>
        /// <returns>The parent rows.</returns>
        /// <exception cref="DBHelper.DataException.ParentRowTableMismatch">
        /// Wrong relation is specified.
        /// </exception>
        private DataRow[] GetParentRows(DataRow rowToSelect, DBRelation objRelation,
            bool bRequeryIfNeeded, bool bAlwaysRequery, out bool bQueryFromDB)
        {
            DataRow[] arowToReturn;
            DataTable tblToSelect;
            DataColumn[] acolToSelect;
            DataColumn[] acolForSelect;
            StringBuilder strbSelectCriteria = new StringBuilder();
            ArrayList alData = new ArrayList();
            string strSelectCriteria;

            if (objRelation.ChildTable != this.Data)
            {
                throw new DataException.ParentRowTableMismatch(objRelation.ChildTable.TableName, this.Data.TableName);
            }

            bQueryFromDB = false;

            tblToSelect = objRelation.ParentTable;
            acolToSelect = objRelation.ParentKey.Columns;
            acolForSelect = objRelation.ChildKey.Columns;
            for (int i=0; i<acolToSelect.Length; i++)
            {
                DataColumn colTemp = acolToSelect[i];
                strbSelectCriteria.Append(colTemp.ColumnName + " = {" + i + "} and ");
                alData.Add(rowToSelect[acolForSelect[i]]);
            }
            strSelectCriteria = DBCommon.ConstructFilterExpression(strbSelectCriteria.ToString(0, strbSelectCriteria.Length - 5),
                alData);

            if (bAlwaysRequery)
            {
                tblToSelect = SelectRows(objRelation.ParentTableHelper, strSelectCriteria);
                bQueryFromDB = true;
            }

            arowToReturn = tblToSelect.Select(strSelectCriteria);

            if (!bAlwaysRequery && arowToReturn.Length == 0 && bRequeryIfNeeded)
            {
                tblToSelect = SelectRows(objRelation.ParentTableHelper, strSelectCriteria);
                arowToReturn = tblToSelect.Select(strSelectCriteria);
                bQueryFromDB = true;
            }

            return (arowToReturn);
        }

        /// <summary>
        /// Retrieves foreign rows for the specified row and specified relation and a value
        /// specifying whehter to requery the rows from database needed.
        /// </summary>
        /// <remarks>
        /// If <i>bRequeryIfNeeded</i> is True, this method will try to retrieve foreign rows
        /// from DataTable instance first; if none is returned, it will retrieve from database.
        /// If <i>bRequeryIfNeeded</i> is False, this method will retrieve foreign rows from
        /// DataTable instance only.
        /// </remarks>
        /// <param name="rowToSelect">The specified parent row.</param>
        /// <param name="objRelation">The specified relation.</param>
        /// <param name="bRequeryIfNeeded">True if requery from database when needed , otherwise False.</param>
        /// <param name="bQueryFromDB">Return True if the rows are retrieved from database, ohterwise
        /// False if the rows are retrieved from existing rows in DataTable instance.</param>
        /// <returns>The foreign rows.</returns>
        /// <exception cref="DBHelper.DataException.RelationForeignTableMismatch">
        /// Wrong relation is specified.
        /// </exception>
        public DataRow[] GetChildRowsDataTable(DataRow rowToSelect, DBRelation objRelation, 
            bool bRequeryIfNeeded, out bool bQueryFromDB)
        {
            return GetChildRows(rowToSelect, objRelation, bRequeryIfNeeded, false, out bQueryFromDB);
        }

        /// <summary>
        /// Retrieves foreign rows for the specified row and specified relation.
        /// </summary>
        /// <remarks>
        /// This method always retrieves foreign rows from database.
        /// </remarks>
        /// <param name="rowToSelect">The specified parent row.</param>
        /// <param name="objRelation">The specified relation.</param>
        /// <returns>The parent rows.</returns>
        /// <exception cref="DBHelper.DataException.RelationForeignTableMismatch">
        /// Wrong relation is specified.
        /// </exception>
        public DataRow[] GetChildRowsDatabase(DataRow rowToSelect, DBRelation objRelation)
        {
            bool bQueryFromDB;

            return GetChildRows(rowToSelect, objRelation, false, true, out bQueryFromDB);
        }

        /// <summary>
        /// Retrieves foreign rows for the specified row and specified relation and a value
        /// specifying whehter to requery the rows from database when needed, and a value
        /// specifying whether to always requery the rows from database.
        /// </summary>
        /// <remarks>
        /// <i>bAlwaysRequery</i> take precedence over <i>bRequeryIfNeeded</i>.<br/>
        /// <p>
        /// If <i>bRequeryIfNeeded</i> is True, this method will try to retrieve foreign rows
        /// from DataTable instance first; if none is returned, it will retrieve from database.
        /// If <i>bRequeryIfNeeded</i> is False, this method will retrieve foreign rows from
        /// DataTable instance only.
        /// </p>
        /// </remarks>
        /// <param name="rowToSelect">The specified parent row.</param>
        /// <param name="objRelation">The specified relation.</param>
        /// <param name="bRequeryIfNeeded">True if requery from database when needed , otherwise False.</param>
        /// <param name="bAlwaysRequery">True if always requery from database, ohterwise False.</param>
        /// <param name="bQueryFromDB">Return True if the rows are retrieved from database, ohterwise
        /// False if the rows are retrieved from existing rows in DataTable instance.</param>
        /// <returns>The foreign rows.</returns>
        /// <exception cref="DBHelper.DataException.RelationForeignTableMismatch">
        /// Wrong relation is specified.
        /// </exception>
        private DataRow[] GetChildRows(DataRow rowToSelect, DBRelation objRelation,
            bool bRequeryIfNeeded, bool bAlwaysRequery, out bool bQueryFromDB)
        {
            DataRow[] arowToReturn;
            DataTable tblToSelect;
            DataColumn[] acolToSelect;
            DataColumn[] acolForSelect;
            StringBuilder strbSelectCriteria = new StringBuilder();
            ArrayList alData = new ArrayList();
            string strSelectCriteria;

            if (objRelation.ParentTable != this.Data)
            {
                throw new DataException.RelationForeignTableMismatch(objRelation.ParentTable.TableName, this.Data.TableName);
            }

            bQueryFromDB = false;

            tblToSelect = objRelation.ChildTable;
            acolToSelect = objRelation.ChildKey.Columns;
            acolForSelect = objRelation.ParentKey.Columns;
            for (int i=0; i<acolToSelect.Length; i++)
            {
                DataColumn colTemp = acolToSelect[i];
                strbSelectCriteria.Append(colTemp.ColumnName + " = {" + i + "} and ");
                alData.Add(rowToSelect[acolForSelect[i]]);
            }
            strSelectCriteria = DBCommon.ConstructFilterExpression(strbSelectCriteria.ToString(0, strbSelectCriteria.Length - 5),
                alData);

            if (bAlwaysRequery)
            {
                tblToSelect = SelectRows(objRelation.ChildTableHelper, strSelectCriteria);
                bQueryFromDB = true;
            }

            arowToReturn = tblToSelect.Select(strSelectCriteria);

            if (!bAlwaysRequery && arowToReturn.Length == 0 && bRequeryIfNeeded)
            {
                tblToSelect = SelectRows(objRelation.ChildTableHelper, strSelectCriteria);
                arowToReturn = tblToSelect.Select(strSelectCriteria);
                bQueryFromDB = true;
            }

            return (arowToReturn);
        }

        #endregion

        #region Class Property Declarations
        /// <summary>
        /// Gets the table name for this instance.
        /// </summary>
        public string TableName
        {
            get
            {
                return (mstrTableName);
            }
        }

        /// <summary>
        /// Gets the <see cref="System.Collections.Hashtable"/> containing the 
        /// <see cref="DBHelper.Field"/> instances.
        /// </summary>
        public Hashtable Fields
        {
            get
            {
                return (mhstField);
            }
        }

        /// <summary>
        /// Gets the <see cref="System.Data.DataTable"/> instance containing data of this instance.
        /// </summary>
        public DataTable Data
        {
            get
            {
                return (mtblData);
            }
        }

        /// <summary>
        /// Sets or gets fields to be retrieved from database.
        /// </summary>
        /// <remarks>
        /// Fields is delimited using semicolon. If all fields needed to be retrieved,
        /// set this property to null or empty.
        /// </remarks>
        public string FieldsToSelect
        {
            get
            {
                return (mstrFieldsToSelect);
            }
            set
            {
                mstrFieldsToSelect = value;
            }
        }

        /// <summary>
        /// Sets or gets fields to be updated to database.
        /// </summary>
        /// <remarks>
        /// Fields is delimited using semi colon. If all fields (except auto incremented fields)
        /// needed to be updated, set this property to null.
        /// </remarks>
        public string FieldsToUpdate
        {
            get
            {
                return (mstrFieldsToUpdate);
            }
            set
            {
                mstrFieldsToUpdate = value;
            }
        }

        /// <summary>
        /// Sets or gets record select criteria.
        /// </summary>
        public string SelectCriteria
        {
            get
            {
                return (mstrSelectCriteria);
            }
            set
            {
                mstrSelectCriteria = value;
            }
        }

        /// <summary>
        /// Sets or gets record update criteria.
        /// </summary>
        public string UpdateCriteria
        {
            get
            {
                return (mstrUpdateCriteria);
            }
            set
            {
                mstrUpdateCriteria = value;
            }
        }

        /// <summary>
        /// Sets or gets record delete criteria.
        /// </summary>
        public string DeleteCriteria
        {
            get
            {
                return (mstrDeleteCriteria);
            }
            set
            {
                mstrDeleteCriteria = value;
            }
        }

        /// <summary>
        /// Sets or gets criteria type used to perform update and delete database
        /// operation.
        /// </summary>
        public DBCriteria CriteriaType
        {
            get
            {
                return (menuCriteriaType);
            }
            set
            {
                menuCriteriaType = value;
            }
        }

        /// <summary>
        /// Gets the parent relations.
        /// </summary>
        public DBRelationCollection ParentRelations
        {
            get
            {
                if (this.mcolParentRelation == null)
                {
                    this.mcolParentRelation = new SqlTableRelationCollection(this, true);
                }
                return (mcolParentRelation);
            }
        }

        /// <summary>
        /// Gets the child relations.
        /// </summary>
        public DBRelationCollection ChildRelations
        {
            get
            {
                if (this.mcolChildRelation == null)
                {
                    this.mcolChildRelation = new SqlTableRelationCollection(this, false);
                }
                return (mcolChildRelation);
            }
        }

        /// <summary>
        /// Specifies the sort criteria used by select command.
        /// </summary>
        /// <example>
        /// <code>
        /// Sort = "IC ASC";
        /// </code>
        /// </example>
        public string Sort
        {
            get
            {
                return (mstrSort);
            }
            set
            {
                mstrSort = value;
            }
        }

        #endregion

        #region IDisposable Members

        /// <summary>
        /// Implements the Dispose functionality.
        /// </summary>
        protected override void Dispose(bool bIsDisposing)
        {
            // Check to see if Dispose has already been called.
            if(!mbIsDisposed)
            {
                try
                {
                    if(bIsDisposing)
                    {
                        if (mcmnSelect != null) 
                        {
                            mcmnSelect.Dispose();
                            mcmnSelect = null;
                        }
                        if (mcmnInsert != null) 
                        {
                            mcmnInsert.Dispose();
                            mcmnInsert = null;
                        }
                        if (mcmnUpdate != null) 
                        {
                            mcmnUpdate.Dispose();
                            mcmnUpdate = null;
                        }
                        if (mcmnDelete != null) 
                        {
                            mcmnDelete.Dispose();
                            mcmnDelete = null;
                        }
                        if (madpMain != null)
                        {
                            madpMain.Dispose();
                            madpMain = null;
                        }
                        
                    }
                    mbIsDisposed = true;
                }
                finally
                {
                    base.Dispose(bIsDisposing);
                }
            }
            
        }

        #endregion
    }
}

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