DbHelper数据操作类【DBHelper.CS】【转：http://www.cnblogs.com/fanrong/articles/726526.html】
其实,微软的企业库中有一个非常不错的数据操作类了.但是,不少公司(起码我遇到的几个...),对一些"封装"了些什么的东西不太敢用,虽然我推荐过微软的企业库框架了...但是还是要"评估"...一评就是几个月...而且,一些公司有的根本就是裸ado.net开发,或者自己封装的数据库操作类非常别扭,很不好用.
这里我给大家共享一个我参照企业库中的数据操作组件编码风格写的数据库操作类,对使用它的程序员来说,编码是很舒服滴(起码我觉得很好撒).以下是代码,很简单的,没有做任何多余的封装,只是改变了ADO.NET的编码步骤,方便了具体开发数据库操作代码的程序员. using System;
using System; using System.Data; using System.Data.Common; using System.Configuration; namespace QPAdmin { public class DbHelper { private static string dbProviderName = ConfigurationManager.AppSettings["DbHelperProvider"]; private static string dbConnectionString = ConfigurationManager.AppSettings["DbHelperNews"]; 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) { DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbConnection dbconn = dbfactory.CreateConnection(); dbconn.ConnectionString = connectionString; return dbconn; } public DbCommand GetStoredProcCommond(string storedProcedure) { DbCommand dbCommand = connection.CreateCommand(); dbCommand.CommandText = storedProcedure; dbCommand.CommandType = CommandType.StoredProcedure; return dbCommand; } public DbCommand GetSqlStringCommond(string sqlQuery) { DbCommand dbCommand = connection.CreateCommand(); dbCommand.CommandText = sqlQuery; dbCommand.CommandType = CommandType.Text; return dbCommand; } #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 dbParameter = cmd.CreateParameter(); dbParameter.DbType = dbType; dbParameter.ParameterName = parameterName; dbParameter.Value = value; dbParameter.Direction = ParameterDirection.Input; cmd.Parameters.Add(dbParameter); } public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType) { DbParameter dbParameter = cmd.CreateParameter(); dbParameter.DbType = dbType; dbParameter.ParameterName = parameterName; dbParameter.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(dbParameter); } public DbParameter GetParameter(DbCommand cmd, string parameterName) { return cmd.Parameters[parameterName]; } #endregion #region 执行 public DataSet ExecuteDataSet(DbCommand cmd) { DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataSet ds = new DataSet(); dbDataAdapter.Fill(ds); return ds; } public DataTable ExecuteDataTable(DbCommand cmd) { DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = 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) { cmd.Connection.Open(); int ret = cmd.ExecuteNonQuery(); cmd.Connection.Close(); return ret; } public object ExecuteScalar(DbCommand cmd) { cmd.Connection.Open(); object ret = cmd.ExecuteScalar(); cmd.Connection.Close(); return ret; } #endregion #region 执行事务 public DataSet ExecuteDataSet(DbCommand cmd,Trans t) { cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataSet ds = new DataSet(); dbDataAdapter.Fill(ds); return ds; } public DataTable ExecuteDataTable(DbCommand cmd, Trans t) { cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataTable dataTable = new DataTable(); dbDataAdapter.Fill(dataTable); return dataTable; } public DbDataReader ExecuteReader(DbCommand cmd, Trans t) { cmd.Connection.Close(); cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; DbDataReader reader = cmd.ExecuteReader(); DataTable dt = new DataTable(); return reader; } public int ExecuteNonQuery(DbCommand cmd, Trans t) { cmd.Connection.Close(); cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; int ret = cmd.ExecuteNonQuery(); return ret; } public object ExecuteScalar(DbCommand cmd, Trans t) { cmd.Connection.Close(); cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; object ret = cmd.ExecuteScalar(); return ret; } #endregion } 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); conn.Open(); dbTrans = conn.BeginTransaction(); } public void Commit() { dbTrans.Commit(); this.Colse(); } public void RollBack() { dbTrans.Rollback(); this.Colse(); } public void Dispose() { this.Colse(); } public void Colse() { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } } }
好了,就这样,DbHelper的代码是非常简单和透明的,只是在ado.net上做了一点小包装,改变了一下使用它的程序员的编码方式,去除掉一些比较"物理级"的编程概念,如connection的open和close之类的,使程序员更专注于业务逻辑代码的编写,少死掉点脑细胞,另外,统一了数据操作层的数据操作代码的风格和格式,维护起来很方便的撒~~~
另:以上代码大家可以随意使用, 不需要给我版权费的啦,嘿嘿.如果大家发现有什么BUG,或者有更好的数据操作类的实现方式,请联系我哦.
