Database Helper Class Library to Ease Database Operation

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;
		}
	}
}