(C#) ASP.NET Microsoft Access Database Class (Visual Studio .Net 2003 - .NET 1.1) |
(C#) ASP.NET Microsoft Access Database Class (Visual Studio .Net 2003 - .NET 1.1) สำหรับ Database Class ตัวนี้เป็นของ Framework 1.1 ซึ่งผมได้พัฒนาบน Visual Studio .Net 2003 เหมาะสมกับผู้ที่ต้องการสร้าง Database Class ไว้สำหรับจัดการฐานข้อมูล Microsoft Access ซึ่งผมได้ทำการออกแบบไว้มีฟังก์ชั่นที่ครบครับ สามารถทำการ เพิ่ม/ลบ/แก้ไข/อ่าน ข้อมูลจากฐานข้อมูลได้อย่างง่ายดายครับ
Instance NameSpace
C#Using System.Data;
Using System.Data.OleDb;
ASP.NET & System.Data.OleDb
Language Code : VB.NET || C#
clsDatabase.cs
using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
public class clsDatabase
{
private OleDbConnection objConn;
private OleDbCommand objCmd;
private OleDbTransaction Trans;
private String strConnString;
public clsDatabase()
{
strConnString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
}
public OleDbDataReader QueryDataReader(String strSQL)
{
OleDbDataReader dtReader;
objConn = new OleDbConnection();
objConn.ConnectionString = strConnString;
objConn.Open();
objCmd = new OleDbCommand(strSQL, objConn);
dtReader = objCmd.ExecuteReader();
return dtReader; //*** Return DataReader ***//
}
public DataSet QueryDataSet(String strSQL)
{
DataSet ds = new DataSet();
OleDbDataAdapter dtAdapter = new OleDbDataAdapter();
objConn = new OleDbConnection();
objConn.ConnectionString = strConnString;
objConn.Open();
objCmd = new OleDbCommand();
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)
{
OleDbDataAdapter dtAdapter;
DataTable dt = new DataTable();
objConn = new OleDbConnection();
objConn.ConnectionString = strConnString;
objConn.Open();
dtAdapter = new OleDbDataAdapter(strSQL, objConn);
dtAdapter.Fill(dt);
return dt; //*** Return DataTable ***//
}
public Boolean QueryExecuteNonQuery(String strSQL)
{
objConn = new OleDbConnection();
objConn.ConnectionString = strConnString;
objConn.Open();
try
{
objCmd = new OleDbCommand();
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 OleDbConnection();
objConn.ConnectionString = strConnString;
objConn.Open();
try
{
objCmd = new OleDbCommand();
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 OleDbConnection();
objConn.ConnectionString = strConnString;
objConn.Open();
Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted);
}
public void TransExecute(String strSQL)
{
objCmd = new OleDbCommand();
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;
}
}
Default.aspx.cs
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.OleDb;
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;
OleDbDataReader 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();
}
}
Code อื่น ๆ ดาวน์โหลดได้จากบทความนี้
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน Source Code 2.0 ของทีมงานไทยครีเอท
|
|
|
By : |
ThaiCreate.Com Team (บทความเป็นลิขสิทธิ์ของเว็บไทยครีเอทห้ามนำเผยแพร่ ณ เว็บไซต์อื่น ๆ) |
|
Score Rating : |
|
|
|
Create/Update Date : |
2008-10-26 22:46:40 /
2017-03-29 09:51:26 |
|
Download : |
|
|
Sponsored Links / Related |
|
|
|
|
|
|
|