Click here to Skip to main content
11,573,862 members (62,080 online)
Click here to Skip to main content
Add your own
alternative version

Database Helper Class Library to Ease Database Operation

, 14 Apr 2007 CPOL 57.5K 2.1K 56
Database Helper Class Library to Ease Database Operation
article_demo.zip
DBHelperQuickRelationshipSamples
DBHelperQuickRelationshipSamples
App.ico
DBHelperQuickRelationshipSamples.csproj.user
DBHelperQuickRelationshipSamples.suo
DBHelperQuickSelectSamples
DBHelperQuickSelectSamples
DBHelperQuickSelectSamples.csproj.user
DBHelperQuickSelectSamples.suo
DBHelperQuickHelperSamples
DBHelperQuickHelperSamples
App.ico
DBHelperQuickHelperSamples.csproj.user
DBHelperQuickHelperSamples.suo
DBHelperQuickInsUpdDelSamples
DBHelperQuickInsUpdDelSamples
DBHelperQuickInsUpdDelSamples.csproj.user
DBHelperQuickInsUpdDelSamples.suo
article_src.zip
Microsoft.ApplicationBlocks.ExceptionManagement.Interfaces
Microsoft.ApplicationBlocks.ExceptionManagement.Interfaces.csproj.user
Microsoft.ApplicationBlocks.ExceptionManagement.suo
DbHelper
DBHelper.chm
DBHelper.csproj.user
DbHelper.ndoc
DBHelper.suo
Microsoft.ApplicationBlocks.ExceptionManagement
ExceptionManagerText.xsx
Microsoft.ApplicationBlocks.ExceptionManagement.csproj.user
Microsoft.ApplicationBlocks.suo
///////////////////////////////////////////////////////////////////////////
// Copyright 2003-2005 Falcon Soon
//
// Author: Soon Chun Boon
// Date: 20 September 2004
// Description: 
// Class that represents data relationship between 2 tables.
///////////////////////////////////////////////////////////////////////////

using System;
using System.ComponentModel;
using System.Data;

namespace DBHelper
{
	/// <summary>
	/// Class that represents data relationship between 2 tables.
	/// </summary>
	public class DBRelation
	{
        #region Class Member Declarations
        private DBKey mobjParentKey;
        private DBKey mobjChildKey;
        private bool mbCreateConstraints;
        private string mstrRelationName;
        private ICommonDBAccess mitcParent;
        private ICommonDBAccess mitcChild;
        private string[] mastrParentColumnName;
        private string[] mastrChildColumnName;
        private string mstrParentTableName;
        private string mstrChildTableName;
        private string mstrParentColumnNames;
        private string mstrChildColumnNames;
        //private bool mbNested;
        #endregion

        #region Class Constructors
        /// <summary>
        /// Initialize a new instance of <see cref="DBHelper.DBRelation"/> by providing single parent
        /// and child key.
        /// </summary>
        /// <param name="strRelationName">Relationship name.</param>
        /// <param name="itcParent">Instance of parent storage.</param>
        /// <param name="itcChild">Instance of child storage.</param>
        /// <param name="colParent">Single column represents parent key.</param>
        /// <param name="colChild">Sindle column represents child key.</param>
        /// <exception cref="DBHelper.DataException.ColumnNotInAnyTable">
        /// A <see cref="System.Data.DataColumn"/> in <see cref="DBHelper.DBKey"/> 
        /// is not belonging to any <see cref="System.Data.DataTable"/>.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyTableMismatch">
        /// <see cref="System.Data.DataTable"/> for every <see cref="System.Data.DataColumn"/>
        /// in <see cref="DBHelper.DBKey"/> is not same.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyColumnsIdentical">
        /// Parent keys and child keys refers to same data columns.
        /// </exception>
        /// <exception cref="DBHelper.DataException.ColumnsTypeMismatch">
        /// Columns type for parent keys and child keys are mismatched.
        /// </exception>
        public DBRelation(string strRelationName, ICommonDBAccess itcParent, ICommonDBAccess itcChild,
            DataColumn colParent, DataColumn colChild) : 
            this(strRelationName, itcParent, itcChild, colParent, colChild, true)
        {
        }

        /// <summary>
        /// Initialize a new instance of <see cref="DBHelper.DBRelation"/> by providing multiple
        /// parent and child keys.
        /// </summary>
        /// <param name="strRelationName">Relationship name.</param>
        /// <param name="itcParent">Instance of parent storage.</param>
        /// <param name="itcChild">Instance of child storage.</param>
        /// <param name="acolParent">An array of columns represent parent keys.</param>
        /// <param name="acolChild">An array of columns represent child keys.</param>
        /// <exception cref="DBHelper.DataException.KeyLengthMismatch">
        /// Parent columns length is not same as child columns length.
        /// </exception>
        /// <exception cref="DBHelper.DataException.ColumnNotInAnyTable">
        /// A <see cref="System.Data.DataColumn"/> in <see cref="DBHelper.DBKey"/> 
        /// is not belonging to any <see cref="System.Data.DataTable"/>.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyTableMismatch">
        /// <see cref="System.Data.DataTable"/> for every <see cref="System.Data.DataColumn"/>
        /// in <see cref="DBHelper.DBKey"/> is not same.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyColumnsIdentical">
        /// Parent keys and child keys refers to same data columns.
        /// </exception>
        /// <exception cref="DBHelper.DataException.ColumnsTypeMismatch">
        /// Columns type for parent keys and child keys are mismatched.
        /// </exception>
        public DBRelation(string strRelationName, ICommonDBAccess itcParent, ICommonDBAccess itcChild,
            DataColumn[] acolParent, DataColumn[] acolChild) : 
            this(strRelationName, itcParent, itcChild, acolParent, acolChild, true)
        {
        }

        /// <summary>
        /// Initialize a new instance of <see cref="DBHelper.DBRelation"/> by providing single
        /// parent and child key and with specified constraint creation boolean.
        /// </summary>
        /// <param name="strRelationName">Relationship name.</param>
        /// <param name="itcParent">Instance of parent storage.</param>
        /// <param name="itcChild">Instance of child storage.</param>
        /// <param name="colParent">Single column represents parent key.</param>
        /// <param name="colChild">Single column represents child key.</param>
        /// <param name="bCreateConstraints">True if constraints is created, otherwise False.</param>
        /// <remarks>
        /// Constraints creation is not implemented yet.
        /// </remarks>
        /// <exception cref="DBHelper.DataException.ColumnNotInAnyTable">
        /// A <see cref="System.Data.DataColumn"/> in <see cref="DBHelper.DBKey"/> 
        /// is not belonging to any <see cref="System.Data.DataTable"/>.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyTableMismatch">
        /// <see cref="System.Data.DataTable"/> for every <see cref="System.Data.DataColumn"/>
        /// in <see cref="DBHelper.DBKey"/> is not same.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyColumnsIdentical">
        /// Parent keys and child keys refers to same data columns.
        /// </exception>
        /// <exception cref="DBHelper.DataException.ColumnsTypeMismatch">
        /// Columns type for parent keys and child keys are mismatched.
        /// </exception>
        public DBRelation(string strRelationName, ICommonDBAccess itcParent, ICommonDBAccess itcChild,
            DataColumn colParent, DataColumn colChild, bool bCreateConstraints)
        {
            //this.extendedProperties = null;
            this.mstrRelationName = "";
            //this.onPropertyChangingDelegate = null;
            this.mitcParent = null;
            this.mitcChild = null;
            this.mobjParentKey = null;
            this.mobjChildKey = null;
            //this.parentKeyConstraint = null;
            //this.childKeyConstraint = null;
            this.mstrParentColumnNames = null;
            this.mstrChildColumnNames = null;
            this.mstrParentTableName = null;
            this.mstrChildTableName = null;
            //this.mbNested = false;
            DataColumn[] acolParent = new DataColumn[1];
            acolParent[0] = colParent;
            DataColumn[] acolChild = new DataColumn[1];
            acolChild[0] = colChild;
            this.Create(strRelationName, itcParent, itcChild, acolParent, acolChild, bCreateConstraints);
        }

        /// <summary>
        /// Initialize a new instance of <see cref="DBHelper.DBRelation"/> by providing multiple
        /// parent and child keys and with specified constraint creation boolean.
        /// </summary>
        /// <param name="strRelationName">Relationship name.</param>
        /// <param name="itcParent">Instance of parent storage.</param>
        /// <param name="itcChild">Instance of child storage.</param>
        /// <param name="acolParent">An array of columns represents parent keys.</param>
        /// <param name="acolChild">An array of columns represents child keys.</param>
        /// <param name="bCreateConstraints">True if constraints is created, otherwise False.</param>
        /// <remarks>
        /// Constraints creation is not implemented yet.
        /// </remarks>
        /// <exception cref="DBHelper.DataException.KeyLengthMismatch">
        /// Parent columns length is not same as child columns length.
        /// </exception>
        /// <exception cref="DBHelper.DataException.ColumnNotInAnyTable">
        /// A <see cref="System.Data.DataColumn"/> in <see cref="DBHelper.DBKey"/> 
        /// is not belonging to any <see cref="System.Data.DataTable"/>.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyTableMismatch">
        /// <see cref="System.Data.DataTable"/> for every <see cref="System.Data.DataColumn"/>
        /// in <see cref="DBHelper.DBKey"/> is not same.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyColumnsIdentical">
        /// Parent keys and child keys refers to same data columns.
        /// </exception>
        /// <exception cref="DBHelper.DataException.ColumnsTypeMismatch">
        /// Columns type for parent keys and child keys are mismatched.
        /// </exception>
        public DBRelation(string strRelationName, ICommonDBAccess itcParent, ICommonDBAccess itcChild,
            DataColumn[] acolParent, DataColumn[] acolChild, bool bCreateConstraints)
        {
            //this.extendedProperties = null;
            this.mstrRelationName = "";
            //this.onPropertyChangingDelegate = null;
            this.mitcParent = null;
            this.mitcChild = null;
            this.mobjParentKey = null;
            this.mobjChildKey = null;
            //this.parentKeyConstraint = null;
            //this.childKeyConstraint = null;
            this.mstrParentColumnNames = null;
            this.mstrChildColumnNames = null;
            this.mstrParentTableName = null;
            this.mstrChildTableName = null;
            //this.mbNested = false;
            this.Create(strRelationName, itcParent, itcChild, acolParent, acolChild, bCreateConstraints);
        }

//        [Browsable(false)]
//        public DBRelation(string strRelationName, string strParentTableName, string strChildTableName, 
//            string[] astrParentColumnName, string[] astrChildColumnName, bool bNested)
//        {
//            //this.extendedProperties = null;
//            this.mstrRelationName = "";
//            //this.onPropertyChangingDelegate = null;
//            this.mobjParentKey = null;
//            this.mobjChildKey = null;
//            //this.parentKeyConstraint = null;
//            //this.childKeyConstraint = null;
//            this.mastrParentColumnName = null;
//            this.mastrChildColumnName = null;
//            this.mstrParentTableName = null;
//            this.mstrChildTableName = null;
//            this.mbNested = false;
//            this.mstrRelationName = strRelationName;
//            this.mastrParentColumnName = astrParentColumnName;
//            this.mastrChildColumnName = astrChildColumnName;
//            this.mstrParentTableName = strParentTableName;
//            this.mstrChildTableName = strChildTableName;
//            this.mbNested = bNested;
//        }
        #endregion

        /// <summary>
        /// Create a new instance of <see cref="DBHelper.DBRelation"/>.
        /// </summary>
        /// <param name="strRelationName">Relationship name.</param>
        /// <param name="itcParent">Instance of parent storage.</param>
        /// <param name="itcChild">Instance of child storage.</param>
        /// <param name="acolParent">An array of columns represents parent keys.</param>
        /// <param name="acolChild">An array of columns represents child keys.</param>
        /// <param name="bCreateConstraints">True if constraints is created, otherwise False.</param>
        /// <remarks>
        /// Constraints creation is not implemented yet.<br/>
        /// <p>
        /// If provided relationship name is null, relationship name will be empty string by default.
        /// </p>
        /// </remarks>
        /// <exception cref="DBHelper.DataException.KeyLengthMismatch">
        /// Parent columns length is not same as child columns length.
        /// </exception>
        /// <exception cref="DBHelper.DataException.ColumnNotInAnyTable">
        /// A <see cref="System.Data.DataColumn"/> in <see cref="DBHelper.DBKey"/> 
        /// is not belonging to any <see cref="System.Data.DataTable"/>.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyTableMismatch">
        /// <see cref="System.Data.DataTable"/> for every <see cref="System.Data.DataColumn"/>
        /// in <see cref="DBHelper.DBKey"/> is not same.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyColumnsIdentical">
        /// Parent keys and child keys refers to same data columns.
        /// </exception>
        /// <exception cref="DBHelper.DataException.ColumnsTypeMismatch">
        /// Columns type for parent keys and child keys are mismatched.
        /// </exception>
        private void Create(string strRelationName, ICommonDBAccess itcParent, ICommonDBAccess itcChild,
            DataColumn[] acolParent, DataColumn[] acolChild, bool bCreateConstraints)
        {
            this.mitcParent = itcParent;
            this.mitcChild = itcChild;
            this.mobjParentKey = new DBKey(acolParent);
            this.mobjChildKey = new DBKey(acolChild);
            if (acolParent.Length != acolChild.Length)
            {
                throw new DataException.KeyLengthMismatch();
            }
            this.CheckState();
            this.mstrRelationName = ((strRelationName == null) ? "" : strRelationName);
            this.mbCreateConstraints = bCreateConstraints;
        }

        /// <summary>
        /// Validates the provided parent keys and child keys.
        /// </summary>
        /// <exception cref="DBHelper.DataException.ColumnNotInAnyTable">
        /// A <see cref="System.Data.DataColumn"/> in <see cref="DBHelper.DBKey"/> 
        /// is not belonging to any <see cref="System.Data.DataTable"/>.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyTableMismatch">
        /// <see cref="System.Data.DataTable"/> for every <see cref="System.Data.DataColumn"/>
        /// in <see cref="DBHelper.DBKey"/> is not same.
        /// </exception>
        /// <exception cref="DBHelper.DataException.KeyColumnsIdentical">
        /// Parent keys and child keys refers to same data columns.
        /// </exception>
        /// <exception cref="DBHelper.DataException.ColumnsTypeMismatch">
        /// Columns type for parent keys and child keys are mismatched.
        /// </exception>
        internal void CheckState()
        {
            this.mobjParentKey.CheckState();
            this.mobjChildKey.CheckState();
            
            if (this.mobjChildKey.ColumnsEqual(this.mobjParentKey))
            {
                throw new DataException.KeyColumnsIdentical();
            }
            for (int iIndex = 0; (iIndex < this.mobjParentKey.Columns.Length); iIndex++)
            {
                if (this.mobjParentKey.Columns[iIndex].DataType != this.mobjChildKey.Columns[iIndex].DataType)
                {
                    throw new DataException.ColumnsTypeMismatch();
                }
            }
        }

        /// <summary>
        /// Validation performed when instance property is accessed.
        /// </summary>
        /// <exception cref="DBHelper.DataException.BadObjectPropertyAccess">
        /// Instance becomes bad while property being accessed.
        /// </exception>
        protected void CheckStateForProperty()
        {
            try
            {
                this.CheckState();
                return;
            }
            catch (Exception ex)
            {
                throw new DataException.BadObjectPropertyAccess(ex.Message);
            }
        }

        #region Class Property Declarations
        /// <summary>
        /// Returns <see cref="System.Data.DataColumn"/> of parent keys.
        /// </summary>
        /// <exception cref="DBHelper.DataException.BadObjectPropertyAccess">
        /// Instance becomes bad while property being accessed.
        /// </exception>
        public virtual DataColumn[] ParentColumns
        {
            get
            {
                this.CheckStateForProperty();
                return this.mobjParentKey.Columns;
            }
        }

        /// <summary>
        /// Returns parent <see cref="DBHelper.DBKey"/>.
        /// </summary>
        /// <exception cref="DBHelper.DataException.BadObjectPropertyAccess">
        /// Instance becomes bad while property being accessed.
        /// </exception>
        internal virtual DBKey ParentKey
        {
            get
            {
                this.CheckStateForProperty();
                return this.mobjParentKey;
            }
        }

        /// <summary>
        /// Returns instance of parent table helper.
        /// </summary>
        public virtual ICommonDBAccess ParentTableHelper
        {
            get
            {
                return (this.mitcParent);
            }
        }

        /// <summary>
        /// Returns parent <see cref="System.Data.DataTable"/>.
        /// </summary>
        /// <exception cref="DBHelper.DataException.BadObjectPropertyAccess">
        /// Instance becomes bad while property being accessed.
        /// </exception>
        public virtual DataTable ParentTable
        {
            get
            {
                this.CheckStateForProperty();
                return this.mobjParentKey.Table;
            }
        }

        /// <summary>
        /// Returns <see cref="System.Data.DataColumn"/> of child keys.
        /// </summary>
        /// <exception cref="DBHelper.DataException.BadObjectPropertyAccess">
        /// Instance becomes bad while property being accessed.
        /// </exception>
        public virtual DataColumn[] ChildColumns
        {
            get
            {
                this.CheckStateForProperty();
                return this.mobjChildKey.Columns;
            }
        }

        /// <summary>
        /// Returns child <see cref="DBHelper.DBKey"/>.
        /// </summary>
        /// <exception cref="DBHelper.DataException.BadObjectPropertyAccess">
        /// Instance becomes bad while property being accessed.
        /// </exception>
        internal virtual DBKey ChildKey
        {
            get
            {
                this.CheckStateForProperty();
                return this.mobjChildKey;
            }
        }

        /// <summary>
        /// Returns instance of child table helper.
        /// </summary>
        public virtual ICommonDBAccess ChildTableHelper
        {
            get
            {
                return (this.mitcChild);
            }
        }

        /// <summary>
        /// Returns child <see cref="System.Data.DataTable"/>.
        /// </summary>
        /// <exception cref="DBHelper.DataException.BadObjectPropertyAccess">
        /// Instance becomes bad while property being accessed.
        /// </exception>
        public virtual DataTable ChildTable
        {
            get
            {
                this.CheckStateForProperty();
                return this.mobjChildKey.Table;
            }
        }

        /// <summary>
        /// Specifies the relationship name.
        /// </summary>
        /// <exception cref="DBHelper.DataException.BadObjectPropertyAccess">
        /// Instance becomes bad while property being accessed.
        /// </exception>
        public virtual string RelationName
        {
            get
            {
                this.CheckStateForProperty();
                return this.mstrRelationName;
            }
            set
            {
                if (value == null)
                {
                    value = "";
                }
                
                this.mstrRelationName = value;
            }
        }
        #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.

You may also be interested in...

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150624.2 | Last Updated 14 Apr 2007
Article Copyright 2007 by falconsoon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid