做开发也有不少年月了，刚入行时，只知道不断重复着写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和实体层！
附件下载：DevNet类库及帮助文档
