其实,微软的企业库中有一个非常不错的数据操作类了.但是,不少公司(起码我遇到的几个...),对一些"封装"了些什么的东西不太敢用,虽然我推荐过微软的企业库框架了...但是还是要"评估"...一评就是几个月...而且,一些公司有的根本就是裸开发,或者自己封装的数据库操作类非常别扭,很不好用.
这里我给大家共享一个我参照企业库中的数据操作组件编码风格写的数据库操作类,对使用它的程序员来说,编码是很舒服滴(起码我觉得很好撒).以下是代码,很简单的,没有做任何多余的封装,只是改变了的编码步骤,方便了具体开发数据库操作代码的程序员. usingSystem;
usingSystem.Data;
mon;
usingSystem.Configuration;
publicclassDbHelper
{
privatestaticstringdbProviderName=ConfigurationManager.AppSettings["DbHelperProvider"];
privatestaticstringdbConnectionString=ConfigurationManager.AppSettings["DbHelperConnectionString"];
privateDbConnectionconnection;
publicDbHelper()
{
this.connection=CreateConnection(DbHelper.dbConnectionString);
}
publicDbHelper(stringconnectionString)
{
this.connection=CreateConnection(connectionString);
}
publicstaticDbConnectionCreateConnection()
{
DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbConnectiondbconn=dbfactory.CreateConnection();
dbconn.ConnectionString=DbHelper.dbConnectionString;
returndbconn;
}
publicstaticDbConnectionCreateConnection(stringconnectionString)
{
DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbConnectiondbconn=dbfactory.CreateConnection();
dbconn.ConnectionString=connectionString;
returndbconn;
}
publicDbCommandGetStoredProcCommond(stringstoredProcedure)
{
DbCommanddbCommand=connection.CreateCommand();
mandText=storedProcedure;
mandType=CommandType.StoredProcedure;
returndbCommand;
}
publicDbCommandGetSqlStringCommond(stringsqlQuery)
{
DbCommanddbCommand=connection.CreateCommand();
mandText=sqlQuery;
mandType=CommandType.Text;
returndbCommand;
}
增加参数#region增加参数
publicvoidAddParameterCollection(DbCommandcmd,DbParameterCollectiondbParameterCollection)
{
foreach(DbParameterdbParameterindbParameterCollection)
{
cmd.Parameters.Add(dbParameter);
}
}
publicvoidAddOutParameter(DbCommandcmd,stringparameterName,DbTypedbType,intsize)
{
DbParameterdbParameter=cmd.CreateParameter();
dbParameter.DbType=dbType;
dbParameter.ParameterName=parameterName;
dbParameter.Size=size;
dbParameter.Direction=ParameterDirection.Output;
cmd.Parameters.Add(dbParameter);
}
publicvoidAddInParameter(DbCommandcmd,stringparameterName,DbTypedbType,objectvalue)
{
DbParameterdbParameter=cmd.CreateParameter();
dbParameter.DbType=dbType;
dbParameter.ParameterName=parameterName;
dbParameter.Value=value;
dbParameter.Direction=ParameterDirection.Input;
cmd.Parameters.Add(dbParameter);
}
publicvoidAddReturnParameter(DbCommandcmd,stringparameterName,DbTypedbType)
{
DbParameterdbParameter=cmd.CreateParameter();
dbParameter.DbType=dbType;
dbParameter.ParameterName=parameterName;
dbParameter.Direction=ParameterDirection.ReturnValue;
cmd.Parameters.Add(dbParameter);
}
publicDbParameterGetParameter(DbCommandcmd,stringparameterName)
{
returncmd.Parameters[parameterName];
}
#endregion
执行#region执行
publicDataSetExecuteDataSet(DbCommandcmd)
{
DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter();
dbDataAdapter.SelectCommand=cmd;
DataSetds=newDataSet();
dbDataAdapter.Fill(ds);
returnds;
}
publicDataTableExecuteDataTable(DbCommandcmd)
{
DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter();
dbDataAdapter.SelectCommand=cmd;
DataTabledataTable=newDataTable();
dbDataAdapter.Fill(dataTable);
returndataTable;
}
publicDbDataReaderExecuteReader(DbCommandcmd)
{
cmd.Connection.Open();
DbDataReaderreader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
returnreader;
}
publicintExecuteNonQuery(DbCommandcmd)
{
cmd.Connection.Open();
intret=cmd.ExecuteNonQuery();
cmd.Connection.Close();
returnret;
}
publicobjectExecuteScalar(DbCommandcmd)
{
cmd.Connection.Open();
objectret=cmd.ExecuteScalar();
cmd.Connection.Close();
returnret;
}
#endregion
执行事务#region执行事务
publicDataSetExecuteDataSet(DbCommandcmd,Transt)
{
cmd.Connection=t.DbConnection;
cmd.Transaction=t.DbTrans;
DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter();
dbDataAdapter.SelectCommand=cmd;
DataSetds=newDataSet();
dbDataAdapter.Fill(ds);
returnds;
}
publicDataTableExecuteDataTable(DbCommandcmd,Transt)
{
cmd.Connection=t.DbConnection;
cmd.Transaction=t.DbTrans;
DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter();
dbDataAdapter.SelectCommand=cmd;
DataTabledataTable=newDataTable();
dbDataAdapter.Fill(dataTable);
returndataTable;
}
publicDbDataReaderExecuteReader(DbCommandcmd,Transt)
{
cmd.Connection.Close();
cmd.Connection=t.DbConnection;
cmd.Transaction=t.DbTrans;
DbDataReaderreader=cmd.ExecuteReader();
DataTabledt=newDataTable();
returnreader;
}
publicintExecuteNonQuery(DbCommandcmd,Transt)
{
cmd.Connection.Close();
cmd.Connection=t.DbConnection;
cmd.Transaction=t.DbTrans;
intret=cmd.ExecuteNonQuery();
returnret;
}
publicobjectExecuteScalar(DbCommandcmd,Transt)
{
cmd.Connection.Close();
cmd.Connection=t.DbConnection;
cmd.Transaction=t.DbTrans;
objectret=cmd.ExecuteScalar();
returnret;
}
#endregion
}
publicclassTrans:IDisposable
{
privateDbConnectionconn;
privateDbTransactiondbTrans;
publicDbConnectionDbConnection
{
get{returnthis.conn;}
}
publicDbTransactionDbTrans
{
get{returnthis.dbTrans;}
}
publicTrans()
{
conn=DbHelper.CreateConnection();
conn.Open();
dbTrans=conn.BeginTransaction();
}
publicTrans(stringconnectionString)
{
conn=DbHelper.CreateConnection(connectionString);
conn.Open();
dbTrans=conn.BeginTransaction();
}
publicvoidCommit()
{
mit();
this.Colse();
}
publicvoidRollBack()
{
dbTrans.Rollback();
this.Colse();
}
publicvoidDispose()
{
this.Colse();
}
publicvoidColse()
{
if(conn.State==System.Data.ConnectionState.Open)
{
conn.Close();
}
}
} 那么如何使用它呢?下面我给出一些基本的使用示例,基本能满足你大部分的数据库操作需要了.
1)直接执行sql语句DbHelperdb=newDbHelper();
DbCommandcmd=db.GetSqlStringCommond("insertt1(id)values('haha')");
db.ExecuteNonQuery(cmd);2)执行存储过程DbHelperdb=newDbHelper();
DbCommandcmd=db.GetStoredProcCommond("t1_insert");
db.AddInParameter(cmd,"@id",DbType.String,"heihei");
db.ExecuteNonQuery(cmd);3)返回DataSetDbHelperdb=newDbHelper();
DbCommandcmd=db.GetSqlStringCommond("select*fromt1");
DataSetds=db.ExecuteDataSet(cmd);4)返回DataTable
DbHelperdb=newDbHelper();
DbCommandcmd=db.GetSqlStringCommond("t1_findall");
DataTabledt=db.ExecuteDataTable(cmd);5)输入参数/输出参数/返回值的使用(比较重要哦)DbHelperdb=newDbHelper();
DbCommandcmd=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);
db.ExecuteNonQuery(cmd);
strings=db.GetParameter(cmd,"@outString").Valueasstring;//outparameter
intr=Convert.ToInt32(db.GetParameter(cmd,"@returnValue").Value);//returnvalue
6)DataReader使用DbHelperdb=newDbHelper();
DbCommandcmd=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);
using(DbDataReaderreader=db.ExecuteReader(cmd))
{
dt.Load(reader);
}
strings=db.GetParameter(cmd,"@outString").Valueasstring;//outparameter
intr=Convert.ToInt32(db.GetParameter(cmd,"@returnValue").Value);//returnvalue
7)事务的使用.(项目中需要将基本的数据库操作组合成一个完整的业务流时,代码级的事务是必不可少的哦)
以上我们好像没有指定数据库连接字符串,大家如果看下DbHelper的代码,就知道要使用它必须在config中配置两个参数,如下:
pubicvoidDoBusiness()
{
using(Transt=newTrans())
{
try
{
D1(t);
thrownewException();//如果有异常,会回滚滴
D2(t);
mit();
}
catch
{
t.RollBack();
}
}
}
publicvoidD1(Transt)
{
DbHelperdb=newDbHelper();
DbCommandcmd=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);
if(t==null)db.ExecuteNonQuery(cmd);
elsedb.ExecuteNonQuery(cmd,t);
strings=db.GetParameter(cmd,"@outString").Valueasstring;//outparameter
intr=Convert.ToInt32(db.GetParameter(cmd,"@returnValue").Value);//returnvalue
}
publicvoidD2(Transt)
{
DbHelperdb=newDbHelper();
DbCommandcmd=db.GetSqlStringCommond("insertt1(id)values('..')");
if(t==null)db.ExecuteNonQuery(cmd);
elsedb.ExecuteNonQuery(cmd,t);
} <appSettings>
<addkey="DbHelperProvider"value="System.Data.SqlClient"/>
<addkey="DbHelperConnectionString"value="DataSource=(local);InitialCatalog=DbHelperTest;PersistSecurityInfo=True;UserID=sa;Password=sa"/>
appSettings> 其实,DbHelper需要的仅仅是两个字符串,你可以自己修改,作成加密什么的...
好了,就这样,DbHelper的代码是非常简单和透明的,只是在上做了一点小包装,改变了一下使用它的程序员的编码方式,去除掉一些比较"物理级"的编程概念,如connection的open和close之类的,使程序员更专注于业务逻辑代码的编写,少死掉点脑细胞,另外,统一了数据操作层的数据操作代码的风格和格式,维护起来很方便的撒~~~
