做开发也有不少年月了，刚入行时，只知道不断重复着写SqlConnection啊，SqlCommand啊等等方法啊，写多了实在感到很枯燥，那么为何不把它们封装起来，做成通用的呢？于是琢磨着开始把常用的方法封装成类库，目的只有一个：实现基本的数据层通用，不用为移植数据库而修改太多的数据层代码，不用在项目中重复写那些简单的数据操作方法，提高项目的开发速度。于是DevNet类库形成。
自DevNet类库形成后，一直在项目中使用，最近在思索如何再次增强其功能，让她适用的范围更广，苦于经验、思路有限，一直没有找到更好的方法，故将DevNet在此详细描述，还请高手们多提宝贵建议。
发现文字表达能力非常的差，悲哀啊！还是闲话少说，直接说该类库。
首先声明：该类库中的方法借鉴了网络上很多好的思路，在此表示非常感谢！因为借鉴的地方很多，现在也无法一一罗列，如发现该类库中使用了您的思路，还请多多包涵！
要想做成通用的开发类库，适用多数据库，类似于DBHelper的类也是必不可少，只不过我做成了抽象类，DBAccessBase.cs,以下是部分代码
/// <summary>
/// 通用数据库访问基类 /// </summary>//[LicenseProvider(typeof(DevNetLicenseProvider))] //License，类库中已注释
public class DBAccessBase { /// <summary> /// 静态的 DataTable ExtendedProperties 属性 /// </summary> internal static string C_PROP_SQL = "SourceSQL"; /// <summary> /// 连接对象 /// </summary> protected DbConnection con; /// <summary> /// 事务对象 /// </summary> protected DbTransaction trans;/// <summary>
/// 构造函数 /// </summary> public DBAccessBase() { // DevNetLicenseProvider.ValidateLicense(typeof (DBConnect), this); }/// <summary>
/// 构造函数 /// </summary> /// <param name="dbConnection">DbConnection</param> public DBAccessBase(DbConnection dbConnection):this() { this.con = dbConnection; }/// <summary>
/// 析构函数 /// </summary> ~DBAccessBase() { con = null; trans = null; }/// <summary>
/// 打开连接 /// </summary> public virtual void Open() { if (con != null) { if (con.State == ConnectionState.Broken) this.con.Close(); if(con.State == ConnectionState.Closed) this.con.Open(); } }/// <summary>
/// 关闭连接 /// </summary> public virtual void Close() { if (con != null && con.State != ConnectionState.Closed) this.con.Close(); }/// <summary>
/// 开始事务 /// </summary> public virtual void BeginTransaction() { if (con == null || con.State == ConnectionState.Closed) throw new Exception("Connection is Null or Not Open");this.trans = this.con.BeginTransaction();
}/// <summary>
/// 开始事务 /// </summary> /// <param name="isoLationLevel">事务锁定行为</param> public virtual void BeginTransaction(IsolationLevel isoLationLevel) { if (con == null || con.State == ConnectionState.Closed) throw new Exception("Connection is Null or Not Open"); this.trans = this.con.BeginTransaction(isoLationLevel); }/// <summary>
/// 回滚事务 /// </summary> public virtual void RollBackTransaction() { if (this.trans == null) throw new Exception("Transaction Not Begin");this.trans.Rollback();
this.trans.Dispose(); this.trans = null; }/// <summary>
/// 提交事务 /// </summary> public virtual void CommitTransaction() { if (this.trans == null) throw new Exception("Transaction Not Begin");this.trans.Commit();
this.trans.Dispose(); this.trans = null; }/// <summary>
/// 创建参数 /// </summary> /// <param name="cmd"></param> /// <param name="parameterName"></param> /// <param name="direction"></param> /// <param name="paraType"></param> /// <param name="size"></param> /// <returns></returns> public DbParameter CreateCmdParameter(DbCommand cmd, string parameterName, ParameterDirection direction, DbType paraType, int size) { DbParameter parameter = cmd.CreateParameter(); parameter.ParameterName = parameterName; parameter.Direction = direction; if (this.con is OleDbConnection && (paraType == DbType.DateTime)) { ((OleDbParameter)parameter).OleDbType = OleDbType.Date; } else parameter.DbType = paraType; parameter.Size = size; return parameter; }/// <summary>
/// 创建参数 /// </summary> /// <param name="cmd"></param> /// <param name="parameterName"></param> /// <param name="direction"></param> /// <param name="paraType"></param> /// <param name="size"></param> /// <param name="Value"></param> /// <returns></returns> public DbParameter CreateCmdParameter(DbCommand cmd, string parameterName, ParameterDirection direction, DbType paraType, int size, object Value) { DbParameter parameter = this.CreateCmdParameter(cmd, parameterName, direction, paraType, size); parameter.Value = Value; return parameter; }/// <summary>
/// 增加一个参数 /// </summary> /// <param name="cmd"></param> /// <param name="paramName"></param> /// <param name="paramValue"></param> public virtual void AddCmdParamWithValue(DbCommand cmd, string paramName, object paramValue) { if (cmd is SqlCommand) ((SqlCommand)cmd).Parameters.AddWithValue(paramName, paramValue); else if (cmd is OleDbCommand) ((OleDbCommand)cmd).Parameters.AddWithValue(paramName, paramValue); else if (cmd is System.Data.OracleClient.OracleCommand) ((System.Data.OracleClient.OracleCommand)cmd).Parameters.AddWithValue(paramName, paramValue); else if (cmd is MySqlDBAccess.MySqlCommand) ((MySqlDBAccess.MySqlCommand)cmd).Parameters.AddWithValue(paramName, paramValue); else throw new Exception("DbCommand Error!"); }/// <summary>
/// 创建 DbCommandBuilder /// </summary> /// <param name="da"></param> /// <returns></returns> public virtual DbCommandBuilder CreateCommandBuilder(DbDataAdapter da) { if (da is SqlDataAdapter) return new SqlCommandBuilder((SqlDataAdapter)da); else if (da is OleDbDataAdapter) return new OleDbCommandBuilder((OleDbDataAdapter)da); else if (da is System.Data.OracleClient.OracleDataAdapter) return new System.Data.OracleClient.OracleCommandBuilder((System.Data.OracleClient.OracleDataAdapter)da); else if (da is MySqlDBAccess.MySqlDataAdapter) return new MySqlDBAccess.MySqlCommandBuilder((MySqlDBAccess.MySqlDataAdapter)da); return null; } /// <summary> /// 创建 DbDataAdapter /// </summary> /// <param name="selectCmd">DbCommand</param> /// <returns></returns> public virtual DbDataAdapter CreateDbAdapter(DbCommand selectCmd) { if (selectCmd is SqlCommand) return new SqlDataAdapter((SqlCommand)selectCmd); else if (selectCmd is OleDbCommand) return new OleDbDataAdapter((OleDbCommand)selectCmd); else if (selectCmd is System.Data.OracleClient.OracleCommand) return new System.Data.OracleClient.OracleDataAdapter((System.Data.OracleClient.OracleCommand)selectCmd); else if (selectCmd is MySqlDBAccess.MySqlCommand) return new MySqlDBAccess.MySqlDataAdapter((MySqlDBAccess.MySqlCommand)selectCmd); return null; }/// <summary>
/// 创建DbDataAdapter /// </summary> /// <param name="selectCmd"></param> /// <returns></returns> public DbDataAdapter CreateDbAdapter(string selectCmd) { DbCommand dbCmd = this.PrepareCommand(CommandType.Text, selectCmd, null); return this.CreateDbAdapter(dbCmd); } /// <summary> /// 执行DbCommand /// </summary> /// <param name="selectCommand"></param> /// <returns></returns> public DataTable ExecuteDataTable(DbCommand selectCommand) { return this.ExecuteDataTable(selectCommand, null); }/// <summary>
/// 获取一个DataTable /// </summary> /// <param name="commandText"></param> /// <returns></returns> public DataTable ExecuteDataTable(string commandText) { return this.ExecuteDataTable(CommandType.Text, commandText, null); }/// <summary>
/// 获取一个DataTable /// </summary> /// <param name="selectCommand"></param> /// <param name="srcTable"></param> /// <returns></returns> public virtual DataTable ExecuteDataTable(DbCommand selectCommand, string srcTable) { DataTable dataTable = new DataTable(); using (DbDataAdapter adapter = this.CreateDbAdapter(selectCommand)) { adapter.Fill(dataTable); dataTable.ExtendedProperties[C_PROP_SQL] = selectCommand.CommandText; if (!String.IsNullOrEmpty(srcTable)) { dataTable.TableName = srcTable; } } return dataTable; }/// <summary>
/// 获取一个DataTable /// </summary> /// <param name="commandText"></param> /// <param name="srcTable"></param> /// <returns></returns> public DataTable ExecuteDataTable(string commandText, string srcTable) { DataTable table = this.ExecuteDataTable(CommandType.Text, commandText, null); if(!String.IsNullOrEmpty(srcTable)) table.TableName = srcTable; return table; }/// <summary>
/// 获取指定记录数集合的表 /// </summary> /// <param name="commandText"></param> /// <param name="startIndex"></param> /// <param name="maxRecords"></param> /// <returns></returns> public DataTable ExecuteDataTable(string commandText, int startIndex, int maxRecords) { DataTable dt;// = new DataTable(); using (DbCommand cmd = this.PrepareCommand(CommandType.Text, commandText, null)) { dt = ExecuteDataTable(cmd, startIndex, maxRecords); } return dt; }/// <summary>
/// 获取指定记录数集合的表 /// </summary> /// <param name="selectCmd"></param> /// <param name="startIndex"></param> /// <param name="maxRecords"></param> /// <returns></returns> public virtual DataTable ExecuteDataTable(DbCommand selectCmd, int startIndex, int maxRecords) { DataTable dt = new DataTable(); using (DbDataAdapter da = this.CreateDbAdapter(selectCmd)) { da.Fill(startIndex, maxRecords, dt); dt.ExtendedProperties[C_PROP_SQL] = selectCmd.CommandText; } return dt; }/// <summary>
/// 获取一个DataTable /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public virtual DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params DbParameter[] commandParameters) { DataTable dataTable = new DataTable(); using(DbCommand selectCommand = this.PrepareCommand(cmdType, cmdText, commandParameters)) { using (DbDataAdapter adapter = this.CreateDbAdapter(selectCommand)) { adapter.Fill(dataTable); dataTable.ExtendedProperties[C_PROP_SQL] = cmdText; } selectCommand.Parameters.Clear(); } return dataTable; }/// <summary>
/// 执行ExecuteNonQuery /// </summary> /// <param name="cmdText"></param> /// <returns></returns> public int ExecuteNonQuery(string cmdText) { return this.ExecuteNonQuery(CommandType.Text, cmdText, null); }/// <summary>
/// 执行ExecuteNonQuery /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] commandParameters) { using (DbCommand dbCmd = this.PrepareCommand(cmdType, cmdText, commandParameters)) { int i = dbCmd.ExecuteNonQuery(); dbCmd.Parameters.Clear(); return i; } }/// <summary>
/// 获取DbDataReader /// </summary> /// <param name="cmdText"></param> /// <returns></returns> public DbDataReader ExecuteReader(string cmdText) { return this.ExecuteReader(CommandType.Text, cmdText, null); }/// <summary>
/// 获取DbDataReader /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] commandParameters) { return this.ExecuteReader(cmdType, cmdText, CommandBehavior.CloseConnection, commandParameters); }/// <summary>
/// 获取DbDataReader /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdBehavior"></param> /// <param name="commandParameters"></param> /// <returns></returns> public DbDataReader ExecuteReader(CommandType cmdType, string cmdText,CommandBehavior cmdBehavior, params DbParameter[] commandParameters) { DbCommand dbCmd = this.PrepareCommand(cmdType, cmdText, commandParameters);DbDataReader read = dbCmd.ExecuteReader(cmdBehavior);
dbCmd.Parameters.Clear(); return read; }/// <summary>
/// 获取ExecuteScalar /// </summary> /// <param name="cmdText"></param> /// <returns></returns> public object ExecuteScalar(string cmdText) { return this.ExecuteScalar(CommandType.Text, cmdText, null); }/// <summary>
/// 获取ExecuteScalar /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] commandParameters) { using (DbCommand dbCmd = this.PrepareCommand(cmdType, cmdText, commandParameters)) { object obj = dbCmd.ExecuteScalar(); dbCmd.Parameters.Clear(); return obj; } }/// <summary>
/// 创建DbCommand /// </summary> /// <returns></returns> public virtual DbCommand CreateCommand() { DbCommand cmd = this.con.CreateCommand(); if (this.trans != null) cmd.Transaction = this.trans; return cmd; }/// <summary>
/// 创建DbCommand /// </summary> /// <returns></returns> public virtual DbCommand CreateCommand(string commandText) { DbCommand command = this.CreateCommand(); command.CommandText = commandText; return command; }/// <summary>
/// /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> protected virtual DbCommand PrepareCommand(CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { DbCommand command = this.CreateCommand(cmdText); command.CommandType = cmdType;if (cmdParms != null)
{ foreach (DbParameter parameter in cmdParms) { command.Parameters.Add(parameter); } } return command; }/// <summary>
/// 更新DataTable /// </summary> /// <param name="dataTable"></param> /// <returns></returns> public int UpdateDataTable(DataTable dataTable) { return this.UpdateDataTable(dataTable, dataTable.TableName); }/// <summary>
/// 更新DataTable /// </summary> /// <param name="dataTable"></param> /// <param name="srcTable"></param> /// <returns></returns> public virtual int UpdateDataTable(DataTable dataTable, string srcTable) { string cmdText = null; if (dataTable.ExtendedProperties.Contains(C_PROP_SQL)) { cmdText = dataTable.ExtendedProperties[C_PROP_SQL].ToString(); if (cmdText.IndexOf("select", StringComparison.OrdinalIgnoreCase) >= 0 && cmdText.IndexOf("from", StringComparison.OrdinalIgnoreCase) >= 0) { int index = cmdText.IndexOf(" where ", StringComparison.OrdinalIgnoreCase); if (index > 0) { cmdText = cmdText.Substring(0, index); } goto Flag; } }if (String.IsNullOrEmpty(srcTable) && String.IsNullOrEmpty(dataTable.TableName))
{ throw new Exception("没有设置TableName，或DataTable不是由DBConnect创建"); } if (String.IsNullOrEmpty(srcTable)) srcTable = dataTable.TableName;System.Text.StringBuilder builder = new System.Text.StringBuilder();
foreach (DataColumn column in dataTable.Columns) { builder.Append(",["); builder.Append(column.ColumnName); builder.Append("]"); } builder.Append(" From "); builder.Append(srcTable); cmdText = "Select " + builder.ToString(1, builder.Length - 1);Flag:
using (DbDataAdapter da = this.CreateDbAdapter(this.PrepareCommand(CommandType.Text, cmdText, null))) { if (da != null) { this.CreateCommandBuilder(da); return da.Update(dataTable); } } return -1; }/// <summary>
/// Return ConnectionState /// </summary> public virtual ConnectionState State { get{ return this.con.State; } }/// <summary>
/// Return or Set Connection's Trans /// </summary> public virtual DbTransaction Transaction { get { return this.trans; } set { this.trans = value; } }/// <summary>
/// Return or Set DbConnection /// </summary> public virtual DbConnection DbConnection { get { return this.con; } set { this.con = value; } }/// <summary>
/// 返回或设置连接的字符串 /// </summary> public virtual string ConnectionString { get { if (con == null) return string.Empty; return this.con.ConnectionString; } set { if (this.con != null) this.con.ConnectionString = value; } }}
这个是该类库最底层的数据操作类了， 我想大家看了就明白了！
我把该类作为基类派生出SqlDbDirect.cs、OleDbDirect.cs、OracleDbDirect.cs和MySqlDbDirect.cs目前支持这几种数据库，数据库类型枚举：
/// <summary>
/// 数据库类型枚举 /// </summary> public enum DBTypeEnum { /// <summary> /// Sql DataBase 1 /// </summary> SQL = 1, /// <summary> /// OleDb Access DataBase 2 /// </summary> OleDb = 2, /// <summary> /// Oracle DataBase 3 /// </summary> Oracle = 3, /// <summary> /// ADO Access DataBase 4 /// </summary> ADO = 4, /// <summary> /// MySql DataBase 5 /// </summary> MySql = 5 }
在以上基础上我封装了DBConnect.cs类，有兴趣的朋友可以查看帮助文档，该类进一步封装数据操作方法，增加编写代码的实用性和易操作性。贴上构造函数代码：
static readonly string Provider = ConfigurationManager.AppSettings["Provider"];
private DBAccessBase con = null;
/// <summary>
/// 构造函数 /// 配置文件 /// (appSettings) /// add key="Provider" value="sql" (Sql,OleDb,Oracle,mysql ) /// add key="sql" value="Data Source=.;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=sa" /// add key="oledb" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\sqldatabase\test.mdb" /// (appSettings) /// 无参数构造函数将自动创建连接 /// </summary> public DBConnect() { if (String.IsNullOrEmpty(Provider)) { return; }string conStr = GetConStr(); //根据配置文件获取连接字符串
if (!string.IsNullOrEmpty(conStr)) { this.dbEnum = getDBEnum(); //获取数据库类型枚举 setConnect(conStr); } }
/// <summary>
/// 构造函数 /// </summary> /// <param name="dataBaseEnum">数据库类型枚举</param> /// <param name="connString">数据库的连接字符串</param> public DBConnect(DBTypeEnum dataBaseEnum, string connString) { this.dbEnum = dataBaseEnum; setConnect(connString); }void setConnect(string connString)
{ con = DBFactory.GetDBConnection(this.dbEnum, connString); //使用工厂统一获取数据库连接对象 ConnectString = connString; }配置文件如下设置 ：
<appSettings>
<add key="Provider" value="sql" /> <add key="sql" value="Data Source=.;Initial Catalog=userinfo;Persist Security Info=True;User ID=sa;Password=sql" /> </appSettings>
至此，数据底层通用的代码编写完毕，还请大家多提建议，下一篇描述ScriptQuery.cs和实体层！
附件下载：