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

namespace DBHelperQuickHelperSamples
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class frmSqlHelper : System.Windows.Forms.Form
	{
		private SqlConnectionProvider mobjCnnProvider;
		private SqlTableHelper mobjTableHelper;
		private SqlHelper mobjSqlHelper;
		private System.Windows.Forms.Button mbtnSelectAll;
		private System.Windows.Forms.DataGrid mdgrData;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.Button mbtnExecNonQuery;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.TextBox mtxtItemID;
		private System.Windows.Forms.TextBox mtxtItemPrice;
		private System.Windows.Forms.TextBox mtxtItemOnHand;
		private System.Windows.Forms.Button mbtnExecDataset;
		private System.Windows.Forms.GroupBox groupBox2;
		private System.Windows.Forms.GroupBox groupBox3;
		private System.Windows.Forms.Label label4;
		private System.Windows.Forms.TextBox mtxtItemName;
		private System.Windows.Forms.Button mbtnExecXmlReader;

		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public frmSqlHelper()
		{
			//
			// 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.Compile();

			//Initialize Sql Helper
			mobjSqlHelper = new SqlHelper();
			mobjSqlHelper.MainConnectionProvider = mobjCnnProvider;
		}

		/// <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.mbtnSelectAll = new System.Windows.Forms.Button();
			this.mdgrData = new System.Windows.Forms.DataGrid();
			this.groupBox1 = new System.Windows.Forms.GroupBox();
			this.mtxtItemOnHand = new System.Windows.Forms.TextBox();
			this.mtxtItemPrice = new System.Windows.Forms.TextBox();
			this.mtxtItemID = new System.Windows.Forms.TextBox();
			this.label3 = new System.Windows.Forms.Label();
			this.label2 = new System.Windows.Forms.Label();
			this.label1 = new System.Windows.Forms.Label();
			this.mbtnExecNonQuery = new System.Windows.Forms.Button();
			this.mbtnExecDataset = new System.Windows.Forms.Button();
			this.groupBox2 = new System.Windows.Forms.GroupBox();
			this.groupBox3 = new System.Windows.Forms.GroupBox();
			this.label4 = new System.Windows.Forms.Label();
			this.mtxtItemName = new System.Windows.Forms.TextBox();
			this.mbtnExecXmlReader = new System.Windows.Forms.Button();
			((System.ComponentModel.ISupportInitialize)(this.mdgrData)).BeginInit();
			this.groupBox1.SuspendLayout();
			this.groupBox2.SuspendLayout();
			this.groupBox3.SuspendLayout();
			this.SuspendLayout();
			// 
			// mbtnSelectAll
			// 
			this.mbtnSelectAll.Location = new System.Drawing.Point(24, 152);
			this.mbtnSelectAll.Name = "mbtnSelectAll";
			this.mbtnSelectAll.Size = new System.Drawing.Size(120, 23);
			this.mbtnSelectAll.TabIndex = 7;
			this.mbtnSelectAll.Text = "Select All Items";
			this.mbtnSelectAll.Click += new System.EventHandler(this.mbtnSelectAll_Click);
			// 
			// mdgrData
			// 
			this.mdgrData.DataMember = "";
			this.mdgrData.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.mdgrData.Location = new System.Drawing.Point(16, 16);
			this.mdgrData.Name = "mdgrData";
			this.mdgrData.Size = new System.Drawing.Size(496, 120);
			this.mdgrData.TabIndex = 6;
			// 
			// groupBox1
			// 
			this.groupBox1.Controls.Add(this.groupBox3);
			this.groupBox1.Controls.Add(this.groupBox2);
			this.groupBox1.Location = new System.Drawing.Point(16, 184);
			this.groupBox1.Name = "groupBox1";
			this.groupBox1.Size = new System.Drawing.Size(504, 312);
			this.groupBox1.TabIndex = 8;
			this.groupBox1.TabStop = false;
			this.groupBox1.Text = "Sql Helper";
			// 
			// mtxtItemOnHand
			// 
			this.mtxtItemOnHand.Location = new System.Drawing.Point(112, 96);
			this.mtxtItemOnHand.Name = "mtxtItemOnHand";
			this.mtxtItemOnHand.Size = new System.Drawing.Size(192, 20);
			this.mtxtItemOnHand.TabIndex = 6;
			this.mtxtItemOnHand.Text = "";
			// 
			// mtxtItemPrice
			// 
			this.mtxtItemPrice.Location = new System.Drawing.Point(112, 64);
			this.mtxtItemPrice.Name = "mtxtItemPrice";
			this.mtxtItemPrice.Size = new System.Drawing.Size(192, 20);
			this.mtxtItemPrice.TabIndex = 5;
			this.mtxtItemPrice.Text = "";
			// 
			// mtxtItemID
			// 
			this.mtxtItemID.Location = new System.Drawing.Point(112, 32);
			this.mtxtItemID.Name = "mtxtItemID";
			this.mtxtItemID.Size = new System.Drawing.Size(192, 20);
			this.mtxtItemID.TabIndex = 4;
			this.mtxtItemID.Text = "";
			// 
			// label3
			// 
			this.label3.Location = new System.Drawing.Point(16, 88);
			this.label3.Name = "label3";
			this.label3.Size = new System.Drawing.Size(88, 23);
			this.label3.TabIndex = 3;
			this.label3.Text = "Item On Hand:";
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(16, 56);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(88, 23);
			this.label2.TabIndex = 2;
			this.label2.Text = "Item Price:";
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(16, 24);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(88, 23);
			this.label1.TabIndex = 1;
			this.label1.Text = "Item ID:";
			// 
			// mbtnExecNonQuery
			// 
			this.mbtnExecNonQuery.Location = new System.Drawing.Point(16, 128);
			this.mbtnExecNonQuery.Name = "mbtnExecNonQuery";
			this.mbtnExecNonQuery.Size = new System.Drawing.Size(160, 23);
			this.mbtnExecNonQuery.TabIndex = 0;
			this.mbtnExecNonQuery.Text = "Execute Non Query";
			this.mbtnExecNonQuery.Click += new System.EventHandler(this.mbtnExecNonQuery_Click);
			// 
			// mbtnExecDataset
			// 
			this.mbtnExecDataset.Location = new System.Drawing.Point(16, 56);
			this.mbtnExecDataset.Name = "mbtnExecDataset";
			this.mbtnExecDataset.Size = new System.Drawing.Size(160, 23);
			this.mbtnExecDataset.TabIndex = 7;
			this.mbtnExecDataset.Text = "Execute Dataset";
			this.mbtnExecDataset.Click += new System.EventHandler(this.mbtnExecDataset_Click);
			// 
			// groupBox2
			// 
			this.groupBox2.Controls.Add(this.mtxtItemOnHand);
			this.groupBox2.Controls.Add(this.mbtnExecNonQuery);
			this.groupBox2.Controls.Add(this.label1);
			this.groupBox2.Controls.Add(this.mtxtItemPrice);
			this.groupBox2.Controls.Add(this.label3);
			this.groupBox2.Controls.Add(this.label2);
			this.groupBox2.Controls.Add(this.mtxtItemID);
			this.groupBox2.Location = new System.Drawing.Point(16, 16);
			this.groupBox2.Name = "groupBox2";
			this.groupBox2.Size = new System.Drawing.Size(472, 160);
			this.groupBox2.TabIndex = 8;
			this.groupBox2.TabStop = false;
			// 
			// groupBox3
			// 
			this.groupBox3.Controls.Add(this.mbtnExecXmlReader);
			this.groupBox3.Controls.Add(this.mtxtItemName);
			this.groupBox3.Controls.Add(this.label4);
			this.groupBox3.Controls.Add(this.mbtnExecDataset);
			this.groupBox3.Location = new System.Drawing.Point(16, 192);
			this.groupBox3.Name = "groupBox3";
			this.groupBox3.Size = new System.Drawing.Size(472, 96);
			this.groupBox3.TabIndex = 9;
			this.groupBox3.TabStop = false;
			this.groupBox3.Text = "groupBox3";
			// 
			// label4
			// 
			this.label4.Location = new System.Drawing.Point(16, 24);
			this.label4.Name = "label4";
			this.label4.Size = new System.Drawing.Size(88, 23);
			this.label4.TabIndex = 8;
			this.label4.Text = "Item Name:";
			// 
			// mtxtItemName
			// 
			this.mtxtItemName.Location = new System.Drawing.Point(112, 24);
			this.mtxtItemName.Name = "mtxtItemName";
			this.mtxtItemName.Size = new System.Drawing.Size(192, 20);
			this.mtxtItemName.TabIndex = 9;
			this.mtxtItemName.Text = "";
			// 
			// mbtnExecXmlReader
			// 
			this.mbtnExecXmlReader.Location = new System.Drawing.Point(200, 56);
			this.mbtnExecXmlReader.Name = "mbtnExecXmlReader";
			this.mbtnExecXmlReader.Size = new System.Drawing.Size(192, 23);
			this.mbtnExecXmlReader.TabIndex = 10;
			this.mbtnExecXmlReader.Text = "Execute XML Reader";
			this.mbtnExecXmlReader.Click += new System.EventHandler(this.mbtnExecXmlReader_Click);
			// 
			// frmSqlHelper
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(536, 510);
			this.Controls.Add(this.groupBox1);
			this.Controls.Add(this.mbtnSelectAll);
			this.Controls.Add(this.mdgrData);
			this.Name = "frmSqlHelper";
			this.Text = "Sql Helper Samples";
			((System.ComponentModel.ISupportInitialize)(this.mdgrData)).EndInit();
			this.groupBox1.ResumeLayout(false);
			this.groupBox2.ResumeLayout(false);
			this.groupBox3.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

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

		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();
			
			//select all rows and return a data table instance
			tblData = mobjTableHelper.SelectAll();
			mdgrData.DataSource = tblData;

			//close connection
			mobjCnnProvider.CloseConnection();
		}

		private void mbtnExecNonQuery_Click(object sender, System.EventArgs e)
		{
			int iItemID;
			double dblItemPrice;
			int iItemOnHand;
			int iItemOnHand3x;

			int iRetVal;
			Hashtable hstOutput;
			int iRowsAffected;
			StringBuilder strbTemp;

			strbTemp = new StringBuilder(100);

			iItemID = Convert.ToInt32(mtxtItemID.Text.Trim());
			dblItemPrice = Convert.ToDouble(mtxtItemPrice.Text.Trim());
			iItemOnHand = Convert.ToInt32(mtxtItemOnHand.Text.Trim());
			iItemOnHand3x = 0;

			//update using stored procedure and 1 type of ExecuteNonQuery overloads
			//you can try other overloads that achieve same result
			mobjSqlHelper.ExecuteNonQuery("sp_UpdItem", iItemID, dblItemPrice, iItemOnHand, iItemOnHand3x);
			
			//display executing result
			iRetVal = mobjSqlHelper.ReturnValue;
			hstOutput = mobjSqlHelper.OutputValue;
			iRowsAffected = mobjSqlHelper.RowsAffected;
			
			strbTemp.Append("Return Value: " + mobjSqlHelper.ReturnValue + "\n");
			if (hstOutput != null && hstOutput.Count > 0) 
			{
				strbTemp.Append("Output Value: \n");
				foreach (DictionaryEntry entry in hstOutput)
				{
					strbTemp.Append(entry.Key + ": " + entry.Value + "\n");
				}
			}
			strbTemp.Append("Rows Affected: " + iRowsAffected + "\n");

			MessageBox.Show(strbTemp.ToString());
		}

		private void mbtnExecDataset_Click(object sender, System.EventArgs e)
		{
			string strItemName;
			int iRetVal;
			Hashtable hstOutput;
			int iRowsAffected;
			StringBuilder strbTemp;

			DataSet dsData;
			DataTable tblData;
			DataRow row;

			strbTemp = new StringBuilder(100);

			strItemName = mtxtItemName.Text.Trim();
			
			//prepare row for selecting purpose
			tblData = new DataTable();
			tblData.Columns.Add("strItemName", typeof (string));
			row = tblData.NewRow();
			row["strItemName"] = strItemName;

			//select using stored procedure and 1 type of ExecuteDataset overloads
			//you can try other overloads that achieve same result
			dsData = mobjSqlHelper.ExecuteDatasetTypedParams("sp_SelItem", row);
			
			//display executing result
			iRetVal = mobjSqlHelper.ReturnValue;
			hstOutput = mobjSqlHelper.OutputValue;
			iRowsAffected = mobjSqlHelper.RowsAffected;
			
			strbTemp.Append("Return Value: " + mobjSqlHelper.ReturnValue + "\n");
			if (hstOutput != null && hstOutput.Count > 0) 
			{
				strbTemp.Append("Output Value: \n");
				foreach (DictionaryEntry entry in hstOutput)
				{
					strbTemp.Append(entry.Key + ": " + entry.Value + "\n");
				}
			}
			strbTemp.Append("Rows Affected: " + iRowsAffected + "\n");

			MessageBox.Show(strbTemp.ToString());

			//display return dataset value
			strbTemp.Remove(0, strbTemp.Length);
			tblData = dsData.Tables[0];
			foreach (DataRow rowData in tblData.Rows)
			{
				foreach (DataColumn col in tblData.Columns)
				{
					strbTemp.Append(col.ColumnName + ": " + rowData[col.ColumnName] + "\n");
				}
			}

			MessageBox.Show(this, strbTemp.ToString(), "DataSet result");
		}

		private void mbtnExecXmlReader_Click(object sender, System.EventArgs e)
		{
			string strItemName;
			int iRetVal;
			Hashtable hstOutput;
			int iRowsAffected;

			string strSQL;
			StringBuilder strbTemp;

			XmlReader xmlr;
			SqlParameter[] apar = new SqlParameter[1];

			strbTemp = new StringBuilder(100);

			strItemName = mtxtItemName.Text.Trim();
			
			//prepare sql parameters for updating purpose
			apar[0] = new SqlParameter("@strItemName", SqlDbType.NVarChar, 50, ParameterDirection.Input,
				false, 0, 0, "", DataRowVersion.Default, strItemName);
			strSQL = "SELECT * FROM x_Item WHERE ItemName = @strItemName FOR XML AUTO";

			//select using inline SQL and 1 type of ExecuteDataset overloads
			//you can try other overloads that achieve same result
			xmlr = mobjSqlHelper.ExecuteXmlReader(CommandType.Text, strSQL, apar);
			
			//display executing result
			iRetVal = mobjSqlHelper.ReturnValue;
			hstOutput = mobjSqlHelper.OutputValue;
			iRowsAffected = mobjSqlHelper.RowsAffected;
			
			strbTemp.Append("Return Value: " + mobjSqlHelper.ReturnValue + "\n");
			if (hstOutput != null && hstOutput.Count > 0) 
			{
				strbTemp.Append("Output Value: \n");
				foreach (DictionaryEntry entry in hstOutput)
				{
					strbTemp.Append(entry.Key + ": " + entry.Value + "\n");
				}
			}
			strbTemp.Append("Rows Affected: " + iRowsAffected + "\n");

			MessageBox.Show(strbTemp.ToString());

			//display return dataset value
			strbTemp.Remove(0, strbTemp.Length);
			while (xmlr.Read())
			{
				strbTemp.Append(xmlr.ReadOuterXml() + "\n");
			}


			MessageBox.Show(this, strbTemp.ToString(), "XML Reader result");	
		}

	}
}