Click here to Skip to main content
12,354,763 members (60,778 online)
Click here to Skip to main content

Stats

61.1K views
2.3K downloads
56 bookmarked
Posted

Database Helper Class Library to Ease Database Operation

, 14 Apr 2007 CPOL
Database Helper Class Library to Ease Database Operation
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
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
Web02 | 2.8.160621.1 | Last Updated 14 Apr 2007
Article Copyright 2007 by falconsoon
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid