数据库操作类

 

这两天写了个类。针对Sql server和OLE数据库所写的。源码如下：

\Classes\DbControl.cs ========================================================== using System; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; namespace GuestBook { /// <summary> /// DbControl 的摘要描述。 /// </summary> public class DbControl:Classes.DataBaseType.DbOpen { //類成員定義。 private int record_total = 0; protected string DBTYPE,SQL_SELECT; protected SqlConnection sqlConn; protected OleDbConnection oleConn; protected SqlCommand sqlCmd; protected OleDbCommand oleCmd; protected DataSet ds = new DataSet(); public int RecordTotal { get { switch (DBTYPE) { case ”SQL”: sqlCmd.Cancel(); sqlCmd.CommandText = SQL_SELECT; SqlDataReader sqlDr; sqlDr = sqlCmd.ExecuteReader(); while (sqlDr.Read()) { record_total++; } sqlDr.Close(); break; case ”OLE”: oleCmd.Cancel(); oleCmd.CommandText = SQL_SELECT; OleDbDataReader oleDr; oleDr = oleCmd.ExecuteReader(); while (oleDr.Read()) { record_total++; } oleDr.Close(); break; } return record_total; } } public DbControl(string dbType,string dbName) { //重載構造函數。 DBTYPE = dbType.ToUpper(); switch (dbType.ToUpper()) { case ”SQL”: sqlConn = this.SqlConnect(dbName); oleConn.Close(); oleConn.Dispose(); break; case ”OLE”: oleConn = this.OleConnect(dbName); oleConn.Close(); oleConn.Dispose(); break; } } public DbControl():base() { // // TODO: 在這裡加入建構函式的程式碼 // } public void Open(string dbType,string dbName) { //數據庫文件打開。 DBTYPE = dbType.ToUpper(); switch (dbType.ToUpper()) { case ”SQL”: sqlConn = this.SqlConnect(dbName); break; case ”OLE”: oleConn = this.OleConnect(dbName); break; } } public SqlDataReader SqlGetReader(string strQuery) { //返回一個SqlDataReader。用於Sql server SQL_SELECT = strQuery; sqlCmd = new SqlCommand(strQuery,sqlConn); SqlDataReader dr; try { sqlCmd.Connection.Open(); } catch (Exception e) { throw e; } dr = sqlCmd.ExecuteReader(); return dr; } public OleDbDataReader OleGetReader(string strQuery) { //返回一個OleDbDataReader。用於OleDb SQL_SELECT = strQuery; oleCmd = new OleDbCommand(strQuery,oleConn); OleDbDataReader dr; try { oleCmd.Connection.Open(); } catch (Exception e) { throw e; } dr = oleCmd.ExecuteReader(); return dr; } public int SqlRunCommand(string strQuery) { //執行一條SQL語句。包括記錄插入、更新、刪除。用於Sql server SQL_SELECT = strQuery; sqlCmd = new SqlCommand(strQuery,sqlConn); try { sqlCmd.Connection.Open(); } catch (Exception e) { throw e; } return sqlCmd.ExecuteNonQuery(); } public int OleRunCommand(string strQuery) { //執行一條SQL語句。包括記錄插入、更新、刪除。用於OleDb SQL_SELECT = strQuery; oleCmd = new OleDbCommand(strQuery,oleConn); try { oleCmd.Connection.Open(); } catch (Exception e) { throw e; } return oleCmd.ExecuteNonQuery(); } public DataView SqlGetDataSet(string strQuery) { //返回一個DataSet。用於Sql server SQL_SELECT = strQuery; sqlCmd = new SqlCommand(strQuery,sqlConn); try { sqlCmd.Connection.Open(); } catch (Exception e) { throw e; } SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = sqlCmd; da.Fill(ds,”DefaultTable”); return ds.Tables[”DefaultTable”].DefaultView; } public DataView OleGetDataSet(string strQuery) { //返回一個DataSet。用於OleDb SQL_SELECT = strQuery; oleCmd = new OleDbCommand(strQuery,oleConn); try { oleCmd.Connection.Open(); } catch (Exception e) { throw e; } OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = oleCmd; da.Fill(ds,”DefaultTable”); return ds.Tables[”DefaultTable”].DefaultView; } public void Close() { //數據庫關閉。 switch (DBTYPE) { case ”SQL”: sqlCmd.Cancel(); sqlCmd.Dispose(); sqlConn.Close(); sqlConn.Dispose(); break; case ”OLE”: oleCmd.Cancel(); oleCmd.Dispose(); oleConn.Close(); oleConn.Dispose(); break; } ds.Clear(); ds.Dispose(); } } } ============================================================ \Classes\DataBaseType\DbOpen.cs ============================================================ using System; using System.Data.SqlClient; using System.Data.OleDb; using System.Configuration; namespace GuestBook.Classes.DataBaseType { /// <summary> /// DbOpen 的摘要描述。 /// </summary> public class DbOpen { public DbOpen() { // // TODO: 在這裡加入建構函式的程式碼 // } protected SqlConnection SqlConnect(string dbName) { SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings[dbName]); return conn; } protected OleDbConnection OleConnect(string dbName) { OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings[dbName]); return conn; } } } ============================================================== 类的调用方法： ============================================================== DbControl objDbControl = new DbControl(); string SQL = ”SELECT * FROM content”; objDbControl.Open(”sql”,”sqlConnection”); dgList.DataSource = objDbControl.SqlGetDataSet(SQL); intTotal = objDbControl.RecordTotal; dgList.DataBind(); objDbControl.Close(); ============================================================== 其中“intTotal = objDbControl.RecordTotal;”即返回记录总数。 可以把它插入到DataGrid中。如： ============================================================== private void dgList_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) { if (e.Item.ItemType == ListItemType.Pager) { System.Text.StringBuilder pagerString = new System.Text.StringBuilder(); pagerString.Append(”總計” + intTotal.ToString() + ” 共” + dgList.PageCount + ”頁 每頁” +