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 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)
		{
		
		}
	}
}

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
Web03 | 2.8.150123.1 | Last Updated 14 Apr 2007
Article Copyright 2007 by falconsoon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid