数据库操作类

 

这两天写了个类。针对 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 + ”頁 每頁” +