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: 29 August 2004
// Description: 
// Class that is used to group a number of related SqlTableHelper instances.
///////////////////////////////////////////////////////////////////////////

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

namespace DBHelper.SqlClient
{
    /// <summary>
	/// Convenient data access container to perform relational database opetation.
	/// </summary>
	public class SqlDataSetHelper : IDisposable
	{
        #region Class Member Declarations
        private SqlTableHelperCollection colSqlTableHelper;
        private	bool mbIsDisposed;
        #endregion

        /// <summary>
        /// Initializes a new instance of <see cref="DBHelper.SqlClient.SqlDataSetHelper"/>.
        /// </summary>
		public SqlDataSetHelper()
		{
            colSqlTableHelper = new SqlTableHelperCollection();
            mbIsDisposed = false;
		}

        /// <summary>
        /// Implements the IDispose' method Dispose.
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }


        /// <summary>
        /// Implements the Dispose functionality.
        /// </summary>
        protected virtual void Dispose(bool bIsDisposing)
        {
            // Check to see if Dispose has already been called.
            if(!mbIsDisposed)
            {
                if(bIsDisposing)
                {
                    // Dispose managed resources.
                }
            }
            mbIsDisposed = true;
        }

        /// <summary>
        /// Retrieves all parent rows in all tables that are related by parent relationships.
        /// </summary>
        /// <remarks>
        /// The process starts from first table by retrieving rows based on some select criteria
        /// and then retriving its parent rows by following the defined parent relationships.
        /// </remarks>
        /// <param name="objFirstHelper">The first SqlTableHelper instance.</param>
        /// <param name="strSelectCriteria">The select criteria.</param>
        /// <example>
        /// This example will retrieves all child rows with "ParentID = 1" and then
        /// its related parent rows and grand rows into x_Child, x_Parent, and x_Grand tables
        /// respectively.
        /// <code>
        /// mobjGrandHelper = new SqlTableHelper("x_Grand");
        /// mobjParentHelper = new SqlTableHelper("x_Parent");
        /// mobjChildHelper = new SqlTableHelper("x_Child");            
        ///            
        /// mobjGrandHelper.MainConnectionProvider = mobjCnnProvider;
        /// mobjParentHelper.MainConnectionProvider = mobjCnnProvider;
        /// mobjChildHelper.MainConnectionProvider = mobjCnnProvider;
        /// mobjGrandHelper.Compile();
        /// mobjParentHelper.Compile();
        /// mobjChildHelper.Compile();
        /// 
        /// mobjChildHelper.AddParentKeys(mobjParentHelper, new string[] {"ParentID"});
        /// mobjParentHelper.AddParentKeys(mobjGrandHelper, new string[] {"GrandID"});
        /// 
        /// mobjGrandHelper.Data.Clear();
        /// mobjParentHelper.Data.Clear();
        /// mobjChildHelper.Data.Clear();
        /// 
        /// SqlDataSetHelper.FillParents(mobjChildHelper, "ParentID = 1");
        /// </code>
        /// </example>
        static public void FillParents(SqlTableHelper objFirstHelper, string strSelectCriteria)
        {
            string strTempCriteria;
            DataTable tblData;
            DataRow[] arow;

            strTempCriteria = objFirstHelper.SelectCriteria;
            objFirstHelper.SelectCriteria = strSelectCriteria;
            tblData = objFirstHelper.SelectSome();
            objFirstHelper.SelectCriteria = strTempCriteria;
            arow = tblData.Select(strSelectCriteria);

            foreach (DBRelation objRel in objFirstHelper.ParentRelations)
            {
                foreach (DataRow row in arow)
                {
                    FillParentsRecur(objRel, row);
                }
            }
        }

        /// <summary>
        /// Retrieves parent rows based on the specified child row.
        /// </summary>
        /// <remarks>
        /// The process will retrieves all parent rows across a number of different
        /// tables as defined by parent relationships recursively.
        /// </remarks>
        /// <param name="objParentRel">The parent relationship to start with.</param>
        /// <param name="row">The specified child row.</param>
        static private void FillParentsRecur(DBRelation objParentRel, DataRow row)
        {
            DataRow[] arow;
            SqlTableHelper objChildHelper;
            SqlTableHelper objParentHelper;
            bool bQueryFromDB;

            objChildHelper = (SqlTableHelper)objParentRel.ChildTableHelper;
            objParentHelper = (SqlTableHelper)objParentRel.ParentTableHelper;
            arow = objChildHelper.GetParentRowsDataTable(row, objParentRel, true, out bQueryFromDB);

            if (bQueryFromDB)
            {
                foreach (DBRelation objRel in objParentHelper.ParentRelations)
                {
                    foreach (DataRow rowTemp in arow)
                    {
                        FillParentsRecur(objRel, rowTemp);
                    }
                }
            }
        }

        /// <summary>
        /// Retrieves all child rows in all tables that are related by child relationships.
        /// </summary>
        /// <remarks>
        /// The process starts from first table by retrieving rows based on some select criteria
        /// and then retriving its child rows by following the defined child relationships.
        /// </remarks>
        /// <param name="objFirstHelper">The first SqlTableHelper instance.</param>
        /// <param name="strSelectCriteria">The select criteria.</param>
        /// <example>
        /// This example will retrieves all grand rows with "GrandID = 2" and then
        /// its related parent rows and child rows into x_Grand, x_Parent, and x_Child tables
        /// respectively.
        /// <code>
        /// mobjGrandHelper = new SqlTableHelper("x_Grand");
        /// mobjParentHelper = new SqlTableHelper("x_Parent");
        /// mobjChildHelper = new SqlTableHelper("x_Child");            
        ///     
        /// mobjGrandHelper.MainConnectionProvider = mobjCnnProvider;
        /// mobjParentHelper.MainConnectionProvider = mobjCnnProvider;
        /// mobjChildHelper.MainConnectionProvider = mobjCnnProvider;
        /// mobjGrandHelper.Compile();
        /// mobjParentHelper.Compile();
        /// mobjChildHelper.Compile();
        /// 
        /// mobjGrandHelper.AddForeignKeys(mobjParentHelper, new string[] {"GrandID"});
        /// mobjParentHelper.AddForeignKeys(mobjChildHelper, new string[] {"ParentID"});
        /// 
        /// mobjGrandHelper.Data.Clear();
        /// mobjParentHelper.Data.Clear();
        /// mobjChildHelper.Data.Clear();
        /// 
        /// SqlDataSetHelper.FillChilds(mobjGrandHelper, "GrandID = 2");
        /// </code>
        /// </example>
        static public void FillChilds(SqlTableHelper objFirstHelper, string strSelectCriteria)
        {
            string strTempCriteria;
            DataTable tblData;
            DataRow[] arow;

            strTempCriteria = objFirstHelper.SelectCriteria;
            objFirstHelper.SelectCriteria = strSelectCriteria;
            tblData = objFirstHelper.SelectSome();
            objFirstHelper.SelectCriteria = strTempCriteria;
            arow = tblData.Select(strSelectCriteria);

            foreach (DBRelation objRel in objFirstHelper.ChildRelations)
            {
                foreach (DataRow row in arow)
                {
                    FillChildsRecur(objRel, row);
                }
            }
        }

        /// <summary>
        /// Retrieves child rows based on the specified parent row.
        /// </summary>
        /// <remarks>
        /// The process will retrieves all child rows across a number of different
        /// tables as defined by child relationships recursively.
        /// </remarks>
        /// <param name="objChildRel">The child relationship to start with.</param>
        /// <param name="row">The specified parent row.</param>
        static private void FillChildsRecur(DBRelation objChildRel, DataRow row)
        {
            DataRow[] arow;
            SqlTableHelper objParentHelper;
            SqlTableHelper objChildHelper;
            bool bQueryFromDB;

            objParentHelper = (SqlTableHelper)objChildRel.ParentTableHelper;
            objChildHelper = (SqlTableHelper)objChildRel.ChildTableHelper;
            arow = objParentHelper.GetChildRowsDataTable(row, objChildRel, true, out bQueryFromDB);

            if (bQueryFromDB)
            {
                foreach (DBRelation objRel in objChildHelper.ChildRelations)
                {
                    foreach (DataRow rowTemp in arow)
                    {
                        FillChildsRecur(objRel, rowTemp);
                    }
                }
            }
        }

        #region Class Property Declarations
        /// <summary>
        /// Retrieves <see cref="DBHelper.SqlClient.SqlTableHelper"/> collection.
        /// </summary>
        public SqlTableHelperCollection SqlTableHelpers
        {
            get
            {
                return (colSqlTableHelper);
            }
        }
        #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
Web04 | 2.8.150129.1 | Last Updated 14 Apr 2007
Article Copyright 2007 by falconsoon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid