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

	}
}

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