(C#) ASP.NET SQL Server Database Class (Visual Studio .Net 2003 - .NET 1.1)

Using System.Data; 
Using System.Data.SqlClient;

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


public class clsDatabase
{

	private SqlConnection objConn;
	private SqlCommand objCmd;
	private SqlTransaction Trans;
	private String strConnString;

	public clsDatabase()
	{
		strConnString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
	}

	public SqlDataReader QueryDataReader(String strSQL)
	{
		SqlDataReader dtReader;
		objConn = new SqlConnection();
		objConn.ConnectionString = strConnString;
		objConn.Open();

		objCmd = new SqlCommand(strSQL, objConn);
		dtReader = objCmd.ExecuteReader();
		return dtReader; //*** Return DataReader ***//
	}

	public DataSet QueryDataSet(String strSQL)
	{
		DataSet ds = new DataSet();
		SqlDataAdapter dtAdapter = new SqlDataAdapter();
		objConn = new SqlConnection();
		objConn.ConnectionString = strConnString;
		objConn.Open();

		objCmd = new SqlCommand();
		objCmd.Connection = objConn;
		objCmd.CommandText = strSQL;
		objCmd.CommandType = CommandType.Text;

		dtAdapter.SelectCommand = objCmd;
		dtAdapter.Fill(ds);
		return ds;   //*** Return DataSet ***//
	}

	public DataTable QueryDataTable(String strSQL)
	{
		SqlDataAdapter dtAdapter;
		DataTable dt = new DataTable();
		objConn = new SqlConnection();
		objConn.ConnectionString = strConnString;
		objConn.Open();

		dtAdapter = new SqlDataAdapter(strSQL, objConn);
		dtAdapter.Fill(dt);
		return dt; //*** Return DataTable ***//
	}

	public Boolean QueryExecuteNonQuery(String strSQL)
	{
		objConn = new SqlConnection();
		objConn.ConnectionString = strConnString;
		objConn.Open();

		try
		{
			objCmd = new SqlCommand();
			objCmd.Connection = objConn;
			objCmd.CommandType = CommandType.Text;
			objCmd.CommandText = strSQL;

			objCmd.ExecuteNonQuery();
			return true; //*** Return True ***//
		}
		catch (Exception)
		{
			return false; //*** Return False ***//
		}
	}


	public Object QueryExecuteScalar(String strSQL)
	{
		Object obj;
		objConn = new SqlConnection();
		objConn.ConnectionString = strConnString;
		objConn.Open();

		try
		{
			objCmd = new SqlCommand();
			objCmd.Connection = objConn;
			objCmd.CommandType = CommandType.Text;
			objCmd.CommandText = strSQL;

			obj = objCmd.ExecuteScalar();  //*** Return Scalar ***//
			return obj;
		}
		catch (Exception)
		{
			return null; //*** Return Nothing ***//
		}
	}

	public void TransStart()
	{
		objConn = new SqlConnection();
		objConn.ConnectionString = strConnString;
		objConn.Open();
		Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted);
	}


	public void TransExecute(String strSQL)
	{
		objCmd = new SqlCommand();
		objCmd.Connection = objConn;
		objCmd.Transaction = Trans;
		objCmd.CommandType = CommandType.Text;
		objCmd.CommandText = strSQL;
		objCmd.ExecuteNonQuery();
	}


	public void TransRollBack()
	{
		Trans.Rollback();
	}

	public void TransCommit()
	{
		Trans.Commit();
	}

	public void Close()
	{
		objConn.Close();
		objConn = null;
	}

}

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


public class _Default : System.Web.UI.Page
{
	protected System.Web.UI.WebControls.DataGrid myDataGrid1;
	protected System.Web.UI.WebControls.DataGrid myDataGrid2;
	protected System.Web.UI.WebControls.DataGrid myDataGrid3;
	protected System.Web.UI.WebControls.Label lblHeaderCustomerID;
	protected System.Web.UI.WebControls.Label lblCustomerID;
	protected System.Web.UI.WebControls.Label lblHeaderName;
	protected System.Web.UI.WebControls.Label lblName;
	protected System.Web.UI.WebControls.Label lblHeaderEmail;
	protected System.Web.UI.WebControls.Label lblEmail;
	protected System.Web.UI.WebControls.Label lblHeaderCountryCode;
	protected System.Web.UI.WebControls.Label lblCountryCode;
	protected System.Web.UI.WebControls.Label lblHeaderBudget;
	protected System.Web.UI.WebControls.Label lblBudget;
	protected System.Web.UI.WebControls.Label lblHeaderUsed;
	protected System.Web.UI.WebControls.Label lblUsed;
	protected System.Web.UI.WebControls.Label lblText;

	#region Web Form Designer generated code
	override protected void OnInit(EventArgs e)
	{
		InitializeComponent();
		base.OnInit(e);
	}
		
	private void InitializeComponent()
	{    
		this.Load += new System.EventHandler(this.Page_Load);

	}
	#endregion
	
	clsDatabase clsDB = new clsDatabase();
	private void Page_Load(object sender, System.EventArgs e)
	{
		myDataReader();
		myDataSet();
		myDataTable();
		myQueryExecuteScalar();
		myExecuteNonQuery();
		myExecuteTransaction();
	}

	//*** DataReader ***//
	void myDataReader()
	{
		String strSQL;
		SqlDataReader dtReader;
		strSQL = "SELECT * FROM customer ";
		dtReader = clsDB.QueryDataReader(strSQL);
		this.myDataGrid1.DataSource = dtReader;
		this.myDataGrid1.DataBind();

		//*** Bind Rows ***//
		/*
		if(dtReader.HasRows == true)
		{
			dtReader.Read();
			this.lblCustomerID.Text = dtReader["CustomerID"].ToString();
			this.lblName.Text = dtReader["Name"].ToString();
			this.lblEmail.Text = dtReader["Email"].ToString();
			this.lblCountryCode.Text = dtReader["CountryCode"].ToString();
			this.lblBudget.Text = dtReader["Budget"].ToString();
			this.lblUsed.Text = dtReader["Used"].ToString();
		}
		*/

		clsDB.Close();
	}

	//*** DataSet ***//
	void myDataSet()
	{
		String strSQL;
		DataSet ds;
		strSQL = "SELECT * FROM customer ";
		ds = clsDB.QueryDataSet(strSQL);
		this.myDataGrid2.DataSource = ds.Tables[0].DefaultView;
		this.myDataGrid2.DataBind();

		//*** Bind Rows ***//
		if(ds.Tables[0].Rows.Count > 0)
		{
			this.lblCustomerID.Text = ds.Tables[0].Rows[0]["CustomerID"].ToString();
			this.lblName.Text = ds.Tables[0].Rows[0]["Name"].ToString();
			this.lblEmail.Text = ds.Tables[0].Rows[0]["Email"].ToString();
			this.lblCountryCode.Text = ds.Tables[0].Rows[0]["CountryCode"].ToString();
			this.lblBudget.Text = ds.Tables[0].Rows[0]["Budget"].ToString();
			this.lblUsed.Text = ds.Tables[0].Rows[0]["Used"].ToString();
		}
		clsDB.Close();
	}

	//*** DataTable ***//
	void myDataTable()
	{
		String strSQL;
		DataTable dt;
		strSQL = "SELECT * FROM customer ";
		dt = clsDB.QueryDataTable(strSQL);
		this.myDataGrid3.DataSource = dt;
		this.myDataGrid3.DataBind();

		//*** Bind Rows ***//
		if(dt.Rows.Count > 0)
		{
			this.lblCustomerID.Text = dt.Rows[0]["CustomerID"].ToString();
			this.lblName.Text = dt.Rows[0]["Name"].ToString();
			this.lblEmail.Text = dt.Rows[0]["Email"].ToString();
			this.lblCountryCode.Text = dt.Rows[0]["CountryCode"].ToString();
			this.lblBudget.Text = dt.Rows[0]["Budget"].ToString();
			this.lblUsed.Text = dt.Rows[0]["Used"].ToString();
		}
		clsDB.Close();
	}

	//*** Execute Scalar ***//
	void myQueryExecuteScalar()
	{
		String strSQL;
		strSQL = "SELECT MAX(Budget) FROM customer ";
		this.lblText.Text = clsDB.QueryExecuteScalar(strSQL).ToString();
		clsDB.Close();
	}

	//*** ExecuteNonQuery ***//
	void myExecuteNonQuery()
	{
		String strSQL1, strSQL2, strSQL3;

		//*** Insert ***//
		strSQL1 = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " +
		" VALUES('C005','Weerachai Nukitram','[email protected]','TH','200000','100000')";
		if(clsDB.QueryExecuteNonQuery(strSQL1) == true)
		{
			//*** Condition Success ***//
		}
			else
		{
			//*** Condition Error ***//
		}
		clsDB.Close();

		//*** Update ***//
		strSQL2 = "UPDATE customer SET Budget = '3000000' WHERE CustomerID = 'C005' ";
		if(clsDB.QueryExecuteNonQuery(strSQL2) == true)
		{
			//*** Condition Success ***//
		}
			else
		{
			//*** Condition Error ***//
		}
		clsDB.Close();

		//*** Delete ***//
		strSQL3 = "DELETE FROM customer WHERE CustomerID = 'C005' ";
		if(clsDB.QueryExecuteNonQuery(strSQL3) == true)
		{
			//*** Condition Success ***//
		}
			else
		{
			//*** Condition Error ***//
		}
		clsDB.Close();
	}

	//*** Execute Transaction ***'
	void myExecuteTransaction()
	{
		String strSQL1, strSQL2, strSQL3;

		//*** Start Transaction ***//
		clsDB.TransStart();

		try
		{

			//*** Insert ***//
			strSQL1 = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " +
			" VALUES('C005','Weerachai Nukitram','[email protected]','TH','200000','100000')";
			clsDB.TransExecute(strSQL1); //*** Execute Query 1 ***//

			//*** Update ***//
			strSQL2 = "UPDATE customer SET Budget = '3000000' WHERE CustomerID = 'C005' ";
			clsDB.TransExecute(strSQL2); //*** Execute Query 2 ***//

			//*** Delete ***//
			strSQL3 = "DELETE FROM customer WHERE CustomerID = 'C005' ";
			clsDB.TransExecute(strSQL3); //*** Execute Query 3 **//

			//*** Commit Transaction ***//
			clsDB.TransCommit();
		}
		catch (Exception)
		{
			//*** RollBack Transaction ***//
			clsDB.TransRollBack();
		}

		clsDB.Close();		
	}
}