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 System.Data.SqlClient;
using DBHelper;
using DBHelper.SqlClient;

namespace DBHelperQuickSelectSamples
{
	/// <summary>
	/// Summary description for frmSelect.
	/// </summary>
	public class frmSelect : System.Windows.Forms.Form
	{	
		//module level definition
		private SqlTableHelper mobjTableHelper;
		private SqlConnectionProvider mobjCnnProvider;
		private System.Windows.Forms.DataGrid mdgrData;
		private System.Windows.Forms.Button mbtnSelectAll;
		private System.Windows.Forms.TextBox mtxtSelectCriteria;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Button mbtnSelectSome;
		private System.Windows.Forms.RadioButton mrbtnTotal;
		private System.Windows.Forms.RadioButton mrbtnPercentage;
		private System.Windows.Forms.Button mbtnSelectTop;
		private System.Windows.Forms.TextBox mtxtTotal;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.TextBox mtxtSort;

		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;
		
		public frmSelect()
		{
			//
			// 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
			mobjTableHelper = new SqlTableHelper("x_Item");
			mobjTableHelper.MainConnectionProvider = mobjCnnProvider;
			mobjTableHelper.FieldsToSelect = "ItemID;ItemName;ItemPrice;ItemOnHand";
			mobjTableHelper.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.mdgrData = new System.Windows.Forms.DataGrid();
			this.mbtnSelectAll = new System.Windows.Forms.Button();
			this.mtxtSelectCriteria = new System.Windows.Forms.TextBox();
			this.label1 = new System.Windows.Forms.Label();
			this.mbtnSelectSome = new System.Windows.Forms.Button();
			this.mrbtnTotal = new System.Windows.Forms.RadioButton();
			this.mrbtnPercentage = new System.Windows.Forms.RadioButton();
			this.mbtnSelectTop = new System.Windows.Forms.Button();
			this.mtxtTotal = new System.Windows.Forms.TextBox();
			this.groupBox1 = new System.Windows.Forms.GroupBox();
			this.mtxtSort = new System.Windows.Forms.TextBox();
			this.label2 = new System.Windows.Forms.Label();
			((System.ComponentModel.ISupportInitialize)(this.mdgrData)).BeginInit();
			this.groupBox1.SuspendLayout();
			this.SuspendLayout();
			// 
			// mdgrData
			// 
			this.mdgrData.DataMember = "";
			this.mdgrData.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.mdgrData.Location = new System.Drawing.Point(8, 8);
			this.mdgrData.Name = "mdgrData";
			this.mdgrData.Size = new System.Drawing.Size(496, 120);
			this.mdgrData.TabIndex = 4;
			this.mdgrData.Navigate += new System.Windows.Forms.NavigateEventHandler(this.mdgrData_Navigate);
			// 
			// mbtnSelectAll
			// 
			this.mbtnSelectAll.Location = new System.Drawing.Point(16, 144);
			this.mbtnSelectAll.Name = "mbtnSelectAll";
			this.mbtnSelectAll.Size = new System.Drawing.Size(120, 23);
			this.mbtnSelectAll.TabIndex = 5;
			this.mbtnSelectAll.Text = "Select All Items";
			this.mbtnSelectAll.Click += new System.EventHandler(this.mbtnSelectAll_Click);
			// 
			// mtxtSelectCriteria
			// 
			this.mtxtSelectCriteria.Location = new System.Drawing.Point(128, 176);
			this.mtxtSelectCriteria.Name = "mtxtSelectCriteria";
			this.mtxtSelectCriteria.Size = new System.Drawing.Size(376, 20);
			this.mtxtSelectCriteria.TabIndex = 6;
			this.mtxtSelectCriteria.Text = "ItemName = \'Pen\'";
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(16, 176);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(100, 24);
			this.label1.TabIndex = 7;
			this.label1.Text = "Select Criteria: ";
			// 
			// mbtnSelectSome
			// 
			this.mbtnSelectSome.Location = new System.Drawing.Point(16, 208);
			this.mbtnSelectSome.Name = "mbtnSelectSome";
			this.mbtnSelectSome.Size = new System.Drawing.Size(280, 23);
			this.mbtnSelectSome.TabIndex = 8;
			this.mbtnSelectSome.Text = "Select Some Items based Select Criteria";
			this.mbtnSelectSome.Click += new System.EventHandler(this.mbtnSelectSome_Click);
			// 
			// mrbtnTotal
			// 
			this.mrbtnTotal.Checked = true;
			this.mrbtnTotal.Location = new System.Drawing.Point(16, 240);
			this.mrbtnTotal.Name = "mrbtnTotal";
			this.mrbtnTotal.TabIndex = 9;
			this.mrbtnTotal.TabStop = true;
			this.mrbtnTotal.Text = "Total";
			// 
			// mrbtnPercentage
			// 
			this.mrbtnPercentage.Location = new System.Drawing.Point(128, 240);
			this.mrbtnPercentage.Name = "mrbtnPercentage";
			this.mrbtnPercentage.TabIndex = 10;
			this.mrbtnPercentage.Text = "Percentage";
			// 
			// mbtnSelectTop
			// 
			this.mbtnSelectTop.Location = new System.Drawing.Point(16, 272);
			this.mbtnSelectTop.Name = "mbtnSelectTop";
			this.mbtnSelectTop.Size = new System.Drawing.Size(256, 23);
			this.mbtnSelectTop.TabIndex = 11;
			this.mbtnSelectTop.Text = "Select Top based on Total or Percentage";
			this.mbtnSelectTop.Click += new System.EventHandler(this.mbtnSelectTop_Click);
			// 
			// mtxtTotal
			// 
			this.mtxtTotal.Location = new System.Drawing.Point(240, 240);
			this.mtxtTotal.Name = "mtxtTotal";
			this.mtxtTotal.Size = new System.Drawing.Size(192, 20);
			this.mtxtTotal.TabIndex = 12;
			this.mtxtTotal.Text = "50";
			// 
			// groupBox1
			// 
			this.groupBox1.Controls.Add(this.mtxtSort);
			this.groupBox1.Controls.Add(this.label2);
			this.groupBox1.Location = new System.Drawing.Point(24, 304);
			this.groupBox1.Name = "groupBox1";
			this.groupBox1.Size = new System.Drawing.Size(464, 56);
			this.groupBox1.TabIndex = 13;
			this.groupBox1.TabStop = false;
			this.groupBox1.Text = "Option";
			// 
			// mtxtSort
			// 
			this.mtxtSort.Location = new System.Drawing.Point(112, 16);
			this.mtxtSort.Name = "mtxtSort";
			this.mtxtSort.Size = new System.Drawing.Size(336, 20);
			this.mtxtSort.TabIndex = 1;
			this.mtxtSort.Text = "";
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(8, 16);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(100, 32);
			this.label2.TabIndex = 0;
			this.label2.Text = "Sort Criteria (Optional) :";
			// 
			// frmSelect
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(504, 366);
			this.Controls.Add(this.groupBox1);
			this.Controls.Add(this.mtxtTotal);
			this.Controls.Add(this.mbtnSelectTop);
			this.Controls.Add(this.mrbtnPercentage);
			this.Controls.Add(this.mrbtnTotal);
			this.Controls.Add(this.mbtnSelectSome);
			this.Controls.Add(this.label1);
			this.Controls.Add(this.mtxtSelectCriteria);
			this.Controls.Add(this.mbtnSelectAll);
			this.Controls.Add(this.mdgrData);
			this.Name = "frmSelect";
			this.Text = "Select Only";
			((System.ComponentModel.ISupportInitialize)(this.mdgrData)).EndInit();
			this.groupBox1.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

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

		private void mbtnSelectAll_Click(object sender, System.EventArgs e)
		{
			DataTable tblData;
			//open connection first, not really neccessary as table helper will open
			//and close connection itself if no available open connection is exists.
			mobjCnnProvider.OpenConnection();

			//It is good to clear table helper instance's data table first b4
			//start select any data
			mobjTableHelper.Data.Clear();
			
			//Get Sort criteria to apply to select action
			if (mtxtSort.Text.Length > 0)
			{
				mobjTableHelper.Sort = mtxtSort.Text.Trim();
			}

			//select all rows and return a data table instance
			tblData = mobjTableHelper.SelectAll();
			mdgrData.DataSource = tblData;

			//close connection
			mobjCnnProvider.CloseConnection();
		}

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

			//It is good to clear table helper instance's data table first b4
			//start select any data
			mobjTableHelper.Data.Clear();
			
			//Get Sort criteria to apply to select action
			if (mtxtSort.Text.Length > 0)
			{
				mobjTableHelper.Sort = mtxtSort.Text.Trim();
			}

			//select some rows based on select criteria and return a data table instance
			//e.g. ItemName = 'Pen'
			mobjTableHelper.SelectCriteria = mtxtSelectCriteria.Text;
			tblData = mobjTableHelper.SelectSome();
			mdgrData.DataSource = tblData;
		}

		private void mbtnSelectTop_Click(object sender, System.EventArgs e)
		{
			DataTable tblData;
			int iTotal;
			bool bPercentage;

			//It is good to clear table helper instance's data table first b4
			//start select any data
			mobjTableHelper.Data.Clear();
			

			//select top based on total or percentage
			if (mrbtnPercentage.Checked) 
			{
				bPercentage = true;
			}
			else
			{
				bPercentage = false;
			}
			
			//Get Sort criteria to apply to select action
			if (mtxtSort.Text.Length > 0)
			{
				mobjTableHelper.Sort = mtxtSort.Text.Trim();
			}

			iTotal = Convert.ToInt32(mtxtTotal.Text);
			//note that select top will also consider select criteria when selecting from database
			mobjTableHelper.SelectCriteria = mtxtSelectCriteria.Text;
			tblData = mobjTableHelper.SelectTop(iTotal, bPercentage);
			mdgrData.DataSource = tblData;
		}

		private void mdgrData_Navigate(object sender, System.Windows.Forms.NavigateEventArgs ne)
		{
		
		}
	}
}