微軟的企業庫中有一個非常不錯的資料操作類了.但是,不少公司(起碼我遇到的幾個...),對一些"封裝"了些什麼的東西不太敢用,雖然我推薦過微軟的企業庫架構了...但是還是要"評估"...一評就是幾個月...而且,一些公司有的根本就是裸ado.net開發,或者自己封裝的資料庫操作類非常别扭,很不好用.
這裡我給大家共享一個我參照企業庫中的資料操作元件編碼風格寫的資料庫操作類,對使用它的程式員來說,編碼是很舒服滴(起碼我覺得很好撒).以下是代碼,很簡單的,沒有做任何多餘的封裝,隻是改變了ado.net的編碼步驟,友善了具體開發資料庫操作代碼的程式員.
using system;
using system.data;
using system.data.common;
using system.configuration;
public class dbhelper
{
private static string dbprovidername = configurationmanager.appsettings["dbhelperprovider"];
private static string dbconnectionstring = configurationmanager.appsettings["dbhelperconnectionstring"];
private dbconnection connection;
public dbhelper()
{
this.connection = createconnection(dbhelper.dbconnectionstring);
}
public dbhelper(string connectionstring)
this.connection = createconnection(connectionstring);
public static dbconnection createconnection()
dbproviderfactory dbfactory = dbproviderfactories.getfactory(dbhelper.dbprovidername);
dbconnection dbconn = dbfactory.createconnection();
dbconn.connectionstring = dbhelper.dbconnectionstring;
return dbconn;
public static dbconnection createconnection(string connectionstring)
dbconn.connectionstring = connectionstring;
public dbcommand getstoredproccommond(string storedprocedure)
dbcommand dbcommand = connection.createcommand();
dbcommand.commandtext = storedprocedure;
dbcommand.commandtype = commandtype.storedprocedure;
return dbcommand;
public dbcommand getsqlstringcommond(string sqlquery)
dbcommand.commandtext = sqlquery;
dbcommand.commandtype = commandtype.text;
增加參數#region 增加參數
public void addparametercollection(dbcommand cmd, dbparametercollection dbparametercollection)
foreach (dbparameter dbparameter in dbparametercollection)
{
cmd.parameters.add(dbparameter);
}
public void addoutparameter(dbcommand cmd, string parametername, dbtype dbtype, int size)
dbparameter dbparameter = cmd.createparameter();
dbparameter.dbtype = dbtype;
dbparameter.parametername = parametername;
dbparameter.size = size;
dbparameter.direction = parameterdirection.output;
cmd.parameters.add(dbparameter);
public void addinparameter(dbcommand cmd, string parametername, dbtype dbtype, object value)
dbparameter.value = value;
dbparameter.direction = parameterdirection.input;
public void addreturnparameter(dbcommand cmd, string parametername, dbtype dbtype)
dbparameter.direction = parameterdirection.returnvalue;
public dbparameter getparameter(dbcommand cmd, string parametername)
return cmd.parameters[parametername];
#endregion
執行#region 執行
public dataset executedataset(dbcommand cmd)
dbdataadapter dbdataadapter = dbfactory.createdataadapter();
dbdataadapter.selectcommand = cmd;
dataset ds = new dataset();
dbdataadapter.fill(ds);
return ds;
public datatable executedatatable(dbcommand cmd)
datatable datatable = new datatable();
dbdataadapter.fill(datatable);
return datatable;
public dbdatareader executereader(dbcommand cmd)
cmd.connection.open();
dbdatareader reader = cmd.executereader(commandbehavior.closeconnection);
return reader;
public int executenonquery(dbcommand cmd)
int ret = cmd.executenonquery();
cmd.connection.close();
return ret;
public object executescalar(dbcommand cmd)
object ret = cmd.executescalar();
#endregion
執行事務#region 執行事務
public dataset executedataset(dbcommand cmd,trans t)
cmd.connection = t.dbconnection;
cmd.transaction = t.dbtrans;
public datatable executedatatable(dbcommand cmd, trans t)
public dbdatareader executereader(dbcommand cmd, trans t)
cmd.transaction = t.dbtrans;
dbdatareader reader = cmd.executereader();
datatable dt = new datatable();
public int executenonquery(dbcommand cmd, trans t)
cmd.transaction = t.dbtrans;
int ret = cmd.executenonquery();
public object executescalar(dbcommand cmd, trans t)
object ret = cmd.executescalar();
}
public class trans : idisposable
private dbconnection conn;
private dbtransaction dbtrans;
public dbconnection dbconnection
get { return this.conn; }
public dbtransaction dbtrans
get { return this.dbtrans; }
public trans()
conn = dbhelper.createconnection();
conn.open();
dbtrans = conn.begintransaction();
public trans(string connectionstring)
conn = dbhelper.createconnection(connectionstring);
public void commit()
dbtrans.commit();
this.colse();
public void rollback()
dbtrans.rollback();
public void dispose()
public void colse()
if (conn.state == system.data.connectionstate.open)
conn.close();
那麼如何使用它呢?下面我給出一些基本的使用示例,基本能滿足你大部分的資料庫操作需要了.
1)直接執行sql語句
dbhelper db = new dbhelper();
dbcommand cmd = db.getsqlstringcommond("insert t1 (id)values('haha')");
db.executenonquery(cmd);
2)執行存儲過程
dbcommand cmd = db.getstoredproccommond("t1_insert");
db.addinparameter(cmd, "@id", dbtype.string, "heihei");
3)傳回dataset
dbcommand cmd = db.getsqlstringcommond("select * from t1");
dataset ds = db.executedataset(cmd);
4)傳回datatable
dbcommand cmd = db.getsqlstringcommond("t1_findall");
datatable dt = db.executedatatable(cmd);
5)輸入參數/輸出參數/傳回值的使用(比較重要哦)
dbcommand cmd = db.getstoredproccommond("t2_insert");
db.addinparameter(cmd, "@timeticks", dbtype.int64, datetime.now.ticks);
db.addoutparameter(cmd, "@outstring", dbtype.string, 20);
db.addreturnparameter(cmd, "@returnvalue", dbtype.int32);
string s = db.getparameter(cmd, "@outstring").value as string;//out parameter
int r = convert.toint32(db.getparameter(cmd, "@returnvalue").value);//return value
6)datareader使用
dbhelper db = new dbhelper();
using (dbdatareader reader = db.executereader(cmd))
dt.load(reader);
}
7)事務的使用.(項目中需要将基本的資料庫操作組合成一個完整的業務流時,代碼級的事務是必不可少的哦)
以上我們好像沒有指定資料庫連接配接字元串,大家如果看下dbhelper的代碼,就知道要使用它必須在config中配置兩個參數,如下:
pubic void dobusiness()
using (trans t = new trans())
try
d1(t);
throw new exception();//如果有異常,會復原滴
d2(t);
t.commit();
catch
t.rollback();
public void d1(trans t)
if (t == null) db.executenonquery(cmd);
else db.executenonquery(cmd,t);
public void d2(trans t)
dbcommand cmd = db.getsqlstringcommond("insert t1 (id)values('
..')");
else db.executenonquery(cmd, t);
<appsettings>
<add key="dbhelperprovider" value="system.data.sqlclient"/>
<add key="dbhelperconnectionstring" value="data source=(local);initial catalog=dbhelpertest;persist security info=true;user id=sa;password=sa"/>
appsettings>
其實,dbhelper需要的僅僅是兩個字元串,你可以自己修改,作成加密什麼的...
好了,就這樣,dbhelper的代碼是非常簡單和透明的,隻是在ado.net上做了一點小包裝,改變了一下使用它的程式員的編碼方式,去除掉一些比較"實體級"的程式設計概念,如connection的open和close之類的,使程式員更專注于業務邏輯代碼的編寫,少死掉點腦細胞,另外,統一了資料操作層的資料操作代碼的風格和格式,維護起來很友善!