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
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using DBHelper;
using DBHelper.SqlClient;

namespace DBHelperQuickRelationshipSamples
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class frmRelationship : System.Windows.Forms.Form
	{
		private SqlTableHelper mobjOrderHeaderTblHelper;
		private SqlTableHelper mobjOrderDetailsTblHelper;
		private SqlTableHelper mobjItemTblHelper;
		private SqlConnectionProvider mobjCnnProvider;

		private System.Windows.Forms.DataGrid mdgrOrderHeader;
		private System.Windows.Forms.DataGrid mdgrOrderDetails;
		private System.Windows.Forms.DataGrid mdgrItem;
		private System.Windows.Forms.Button mbtnSelectAllOrder;
		private System.Windows.Forms.Button mbtnSelectOrderDetails;
		private System.Windows.Forms.Button mbtnFillChild;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public frmRelationship()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			//Initialize connection provider
			mobjCnnProvider = new SqlConnectionProvider();
			mobjCnnProvider.ConnectionString = "Initial Catalog=SwComponent;Data Source=amenthyst;UID=sa;Password=vios";
			
			//Initialize table helper
			mobjOrderHeaderTblHelper = new SqlTableHelper("x_OrderHeader");
			mobjOrderHeaderTblHelper.MainConnectionProvider = mobjCnnProvider;
			mobjOrderHeaderTblHelper.Compile();

			mobjOrderDetailsTblHelper = new SqlTableHelper("x_OrderDetails");
			mobjOrderDetailsTblHelper.MainConnectionProvider = mobjCnnProvider;
			mobjOrderDetailsTblHelper.Compile();

			mobjItemTblHelper = new SqlTableHelper("x_Item");
			mobjItemTblHelper.MainConnectionProvider = mobjCnnProvider;
			mobjItemTblHelper.Compile();
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.mdgrOrderHeader = new System.Windows.Forms.DataGrid();
			this.mdgrOrderDetails = new System.Windows.Forms.DataGrid();
			this.mdgrItem = new System.Windows.Forms.DataGrid();
			this.mbtnSelectOrderDetails = new System.Windows.Forms.Button();
			this.mbtnSelectAllOrder = new System.Windows.Forms.Button();
			this.mbtnFillChild = new System.Windows.Forms.Button();
			((System.ComponentModel.ISupportInitialize)(this.mdgrOrderHeader)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.mdgrOrderDetails)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.mdgrItem)).BeginInit();
			this.SuspendLayout();
			// 
			// mdgrOrderHeader
			// 
			this.mdgrOrderHeader.DataMember = "";
			this.mdgrOrderHeader.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.mdgrOrderHeader.Location = new System.Drawing.Point(24, 8);
			this.mdgrOrderHeader.Name = "mdgrOrderHeader";
			this.mdgrOrderHeader.Size = new System.Drawing.Size(496, 120);
			this.mdgrOrderHeader.TabIndex = 7;
			// 
			// mdgrOrderDetails
			// 
			this.mdgrOrderDetails.DataMember = "";
			this.mdgrOrderDetails.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.mdgrOrderDetails.Location = new System.Drawing.Point(24, 168);
			this.mdgrOrderDetails.Name = "mdgrOrderDetails";
			this.mdgrOrderDetails.Size = new System.Drawing.Size(496, 120);
			this.mdgrOrderDetails.TabIndex = 8;
			// 
			// mdgrItem
			// 
			this.mdgrItem.DataMember = "";
			this.mdgrItem.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.mdgrItem.Location = new System.Drawing.Point(24, 296);
			this.mdgrItem.Name = "mdgrItem";
			this.mdgrItem.Size = new System.Drawing.Size(496, 120);
			this.mdgrItem.TabIndex = 9;
			// 
			// mbtnSelectOrderDetails
			// 
			this.mbtnSelectOrderDetails.Location = new System.Drawing.Point(24, 432);
			this.mbtnSelectOrderDetails.Name = "mbtnSelectOrderDetails";
			this.mbtnSelectOrderDetails.Size = new System.Drawing.Size(288, 23);
			this.mbtnSelectOrderDetails.TabIndex = 10;
			this.mbtnSelectOrderDetails.Text = "Select Order Details based on selected Order Header";
			this.mbtnSelectOrderDetails.Click += new System.EventHandler(this.mbtnSelectOrderDetails_Click);
			// 
			// mbtnSelectAllOrder
			// 
			this.mbtnSelectAllOrder.Location = new System.Drawing.Point(40, 136);
			this.mbtnSelectAllOrder.Name = "mbtnSelectAllOrder";
			this.mbtnSelectAllOrder.Size = new System.Drawing.Size(192, 23);
			this.mbtnSelectAllOrder.TabIndex = 11;
			this.mbtnSelectAllOrder.Text = "Select All Orders";
			this.mbtnSelectAllOrder.Click += new System.EventHandler(this.mbtnSelectAllOrder_Click);
			// 
			// mbtnFillChild
			// 
			this.mbtnFillChild.Location = new System.Drawing.Point(24, 472);
			this.mbtnFillChild.Name = "mbtnFillChild";
			this.mbtnFillChild.Size = new System.Drawing.Size(344, 23);
			this.mbtnFillChild.TabIndex = 12;
			this.mbtnFillChild.Text = "Select Order Details and Items based on selected Order Header";
			this.mbtnFillChild.Click += new System.EventHandler(this.mbtnFillChild_Click);
			// 
			// frmRelationship
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(544, 510);
			this.Controls.Add(this.mbtnFillChild);
			this.Controls.Add(this.mbtnSelectAllOrder);
			this.Controls.Add(this.mbtnSelectOrderDetails);
			this.Controls.Add(this.mdgrItem);
			this.Controls.Add(this.mdgrOrderDetails);
			this.Controls.Add(this.mdgrOrderHeader);
			this.Name = "frmRelationship";
			this.Text = "Relationship Samples";
			((System.ComponentModel.ISupportInitialize)(this.mdgrOrderHeader)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.mdgrOrderDetails)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.mdgrItem)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new frmRelationship());
		}

		private void mbtnSelectAllOrder_Click(object sender, System.EventArgs e)
		{
			DataTable tblData;

			tblData = mobjOrderHeaderTblHelper.SelectAll();
			mdgrOrderHeader.DataSource = tblData;
		}

		private void mbtnSelectOrderDetails_Click(object sender, System.EventArgs e)
		{
			DataTable tblData;
			DataTable tblChildData;
			DBRelation objForeignRelation;
			DataRow[] arowData;
			int iSelected;
			
			//add foreign keys, apply same concept for parent relationship situation
			mobjOrderHeaderTblHelper.ChildRelations.Clear();
			objForeignRelation = mobjOrderHeaderTblHelper.AddForeignKeys(mobjOrderDetailsTblHelper, new string[] {"OrderDetailsOrderHeaderID"});

			//construct beginning row to retrieve child rows
			iSelected = Convert.ToInt32(mdgrOrderHeader[mdgrOrderHeader.CurrentRowIndex, 0]);
			Console.WriteLine("Order Header ID: " + iSelected);
			mobjOrderHeaderTblHelper.SelectCriteria = String.Format("OrderHeaderID = {0}", iSelected);
			mobjOrderHeaderTblHelper.Data.Rows.Clear();
			tblData = mobjOrderHeaderTblHelper.SelectSome();

			//retrieve child rows, use GetParentRowsDatabase for retrive parent rows case
			mobjOrderDetailsTblHelper.Data.Rows.Clear();
			arowData = mobjOrderHeaderTblHelper.GetChildRowsDatabase(tblData.Rows[0], objForeignRelation);
			Console.WriteLine("Total child rows: " + arowData.Length);
			
			tblChildData = arowData[0].Table;
			mdgrOrderDetails.DataSource = tblChildData;
			if (mdgrItem.DataSource != null)
			{
				((DataTable)mdgrItem.DataSource).Clear();
			}
		}

		private void mbtnFillChild_Click(object sender, System.EventArgs e)
		{
			int iSelected;
			string strSelected;

			//add foreign keys, apply same concept for parent relationship situation
			mobjOrderHeaderTblHelper.ChildRelations.Clear();
			mobjOrderDetailsTblHelper.ChildRelations.Clear();
			mobjOrderHeaderTblHelper.AddForeignKeys(mobjOrderDetailsTblHelper, new string[] {"OrderDetailsOrderHeaderID"});
			//add foreign relationship explicitly as foreign key in x_Item not reference primary key
			//in x_OrderDetails
			mobjOrderDetailsTblHelper.ChildRelations.Add("x_OrderDetailsFKx_Item|ItemID", 
				mobjOrderDetailsTblHelper, mobjItemTblHelper,
				new DataColumn[] {mobjOrderDetailsTblHelper.Data.Columns["OrderDetailsItemID"]},
				new DataColumn[] {mobjItemTblHelper.Data.Columns["ItemID"]});

			//construct beginning row to retrieve child rows
			iSelected = Convert.ToInt32(mdgrOrderHeader[mdgrOrderHeader.CurrentRowIndex, 0]);
			Console.WriteLine("Order Header ID: " + iSelected);
			strSelected = String.Format("OrderHeaderID = {0}", iSelected);

			//clear data before retrieve foreign/child records
			mobjOrderHeaderTblHelper.Data.Clear();
			mobjOrderDetailsTblHelper.Data.Clear();
			mobjItemTblHelper.Data.Clear();

			mobjCnnProvider.OpenConnection();
			mobjCnnProvider.BeginTransaction();

			//retrive all related child/foreign rows based on select criteria for top table helper.
			//use FillParents for retrieving all related parent rows case
			SqlDataSetHelper.FillChilds(mobjOrderHeaderTblHelper, strSelected);
			
			mobjCnnProvider.CommitTransaction();
			mobjCnnProvider.CloseConnection();
	
			mdgrOrderHeader.DataSource = mobjOrderHeaderTblHelper.Data;
			mdgrOrderDetails.DataSource = mobjOrderDetailsTblHelper.Data;
			mdgrItem.DataSource = mobjItemTblHelper.Data;
		}
	}
}

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 | Mobile
Web01 | 2.8.141015.1 | Last Updated 14 Apr 2007
Article Copyright 2007 by falconsoon
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid