[ASP.net(C#)]自定义数据库操作类(一)
2024-07-10 12:57:09
供稿：网友
这两天写了个类。针对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 + "頁 每頁" + dglist.pagesize + "筆");
e.item.cells[0].controls.addat(0,new literalcontrol(pagerstring.tostring())) ;
}
}
