本示例代码的关键是利用.net库自带的DbProviderFactory来生产数据库操作对象。
从下图中,可以看到其的多个核心方法,这些方法将在我们的超级DBHelper中使用。
仔细研究,你会发现每个数据库的官方支持dll都有一个Instance对象,这个对象都是继承了DbProviderFactory了。
因此利用这点,我们就可以实现兼容多种数据的超级DBHelper了。
以下为示例代码,仅供参考学习,代码只是我的ORM框架中的一个片段(其中暂时支持了SQLSERVER、MYSQL、SQLITE三种数据库,LoadDbProviderFactory方法是将封装在dll中的数据库操作dll反射加载实例化的方法。):
- /// <summary>
- /// 超级数据库操作类
- /// <para>2015年12月21日</para>
- /// <para>v1.0</para>
- /// <para>叶琪</para>
- /// </summary>
- public class DBHelper
- {
- #region 属性
- private DbProviderFactory _DbFactory;
- private DBConfig mDBConfig;
- /// <summary>
- /// 数据库连接配置
- /// </summary>
- public DBConfig DBConfig
- {
- get { return mDBConfig; }
- }
- /// <summary>
- /// 表示一组方法,这些方法用于创建提供程序对数据源类的实现的实例。
- /// </summary>
- public DbProviderFactory DbFactory
- {
- get { return _DbFactory; }
- set { _DbFactory = value; }
- }
- #endregion
- #region 构造函数
- public DBHelper(DBConfig aORMConfig)
- {
- mDBConfig = aORMConfig;
- switch (mDBConfig.DBType)
- {
- case ORMType.DBTypes.SQLSERVER:
- _DbFactory = System.Data.SqlClient.SqlClientFactory.Instance;
- break;
- case ORMType.DBTypes.MYSQL:
- LoadDbProviderFactory("MySql.Data.dll", "MySql.Data.MySqlClient.MySqlClientFactory");
- break;
- case ORMType.DBTypes.SQLITE:
- LoadDbProviderFactory("System.Data.SQLite.dll", "System.Data.SQLite.SQLiteFactory");
- break;
- }
- }
- /// <summary>
- /// 动态载入数据库封装库
- /// </summary>
- /// <param name="aDLLName">数据库封装库文件名称</param>
- /// <param name="aFactoryName">工厂路径名称</param>
- private void LoadDbProviderFactory(string aDLLName, string aFactoryName)
- {
- string dllPath = string.Empty;
- if (System.AppDomain.CurrentDomain.RelativeSearchPath != null)
- {
- dllPath = System.AppDomain.CurrentDomain.RelativeSearchPath+"\\"+ aDLLName;
- }
- else
- {
- dllPath = System.AppDomain.CurrentDomain.BaseDirectory + aDLLName;
- }
- if (!File.Exists(dllPath))
- {//文件不存在,从库资源中复制输出到基目录下
- FileStream fdllFile = new FileStream(dllPath,FileMode.Create);
- byte[] dllData = null;
- if (aDLLName == "System.Data.SQLite.dll")
- {
- dllData = YFmk.ORM.Properties.Resources.System_Data_SQLite;
- }
- else if (aDLLName == "MySql.Data.dll")
- {
- dllData = YFmk.ORM.Properties.Resources.MySql_Data;
- }
- fdllFile.Write(dllData, 0, dllData.Length);
- fdllFile.Close();
- }
- Assembly libAssembly = Assembly.LoadFile(dllPath);
- Type type = libAssembly.GetType(aFactoryName);
- foreach (FieldInfo fi in type.GetFields(BindingFlags.Static | BindingFlags.Public))
- {
- if (fi.Name == "Instance")
- {
- _DbFactory = fi.GetValue(null) as DbProviderFactory;
- return;
- }
- }
- }
- #endregion
- #region 数据库操作
- /// <summary>
- /// 执行一条计算查询结果语句,返回查询结果
- /// </summary>
- /// <param name="aSQLWithParameter">SQL语句及参数</param>
- /// <returns>查询结果(object)</returns>
- public object GetSingle(SQLWithParameter aSQLWithParameter)
- {
- using (DbConnection conn = _DbFactory.CreateConnection())
- {
- conn.ConnectionString = mDBConfig.ConnString;
- using (DbCommand cmd = _DbFactory.CreateCommand())
- {
- PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);
- object obj = cmd.ExecuteScalar();
- cmd.Parameters.Clear();
- if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
- {
- return null;
- }
- else
- {
- return obj;
- }
- }
- }
- }
- /// <summary>
- /// 执行SQL语句,返回影响的记录数
- /// </summary>
- /// <param name="aSQL">SQL语句</param>
- /// <returns>影响的记录数</returns>
- public int ExecuteSql(string aSQL)
- {
- using (DbConnection conn = _DbFactory.CreateConnection())
- {
- conn.ConnectionString = mDBConfig.ConnString;
- using (DbCommand cmd = _DbFactory.CreateCommand())
- {
- PrepareCommand(cmd, conn, aSQL);
- int rows = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- return rows;
- }
- }
- }
- /// <summary>
- /// 执行SQL语句,返回影响的记录数
- /// </summary>
- /// <param name="aSQLWithParameter">SQL语句及参数</param>
- /// <returns></returns>
- public int ExecuteSql(SQLWithParameter aSQLWithParameter)
- {
- using (DbConnection conn = _DbFactory.CreateConnection())
- {
- conn.ConnectionString = mDBConfig.ConnString;
- using (DbCommand cmd = _DbFactory.CreateCommand())
- {
- PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);
- int rows = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- return rows;
- }
- }
- }
- /// <summary>
- /// 执行多条SQL语句,实现数据库事务。
- /// </summary>
- /// <param name="aSQLWithParameterList">参数化的SQL语句结构体对象集合</param>
- public string ExecuteSqlTran(List<SQLWithParameter> aSQLWithParameterList)
- {
- using (DbConnection conn = _DbFactory.CreateConnection())
- {
- conn.ConnectionString = mDBConfig.ConnString;
- conn.Open();
- DbTransaction fSqlTransaction = conn.BeginTransaction();
- try
- {
- List<DbCommand> fTranCmdList = new List<DbCommand>();
- //创建新的CMD
- DbCommand fFirstCMD = _DbFactory.CreateCommand();
- fFirstCMD.Connection = conn;
- fFirstCMD.Transaction = fSqlTransaction;
- fTranCmdList.Add(fFirstCMD);
- int NowCmdIndex = 0;//当前执行的CMD索引值
- int ExecuteCount = 0;//已经执行的CMD次数
- StringBuilder fSQL = new StringBuilder();
- foreach (SQLWithParameter fSQLWithParameter in aSQLWithParameterList)
- {
- fSQL.Append(fSQLWithParameter.SQL.ToString() + ";");
- fTranCmdList[NowCmdIndex].Parameters.AddRange(fSQLWithParameter.Parameters.ToArray());
- if (fTranCmdList[NowCmdIndex].Parameters.Count > 2000)
- { //参数达到2000个,执行一次CMD
- fTranCmdList[NowCmdIndex].CommandText = fSQL.ToString();
- fTranCmdList[NowCmdIndex].ExecuteNonQuery();
- DbCommand fNewCMD = _DbFactory.CreateCommand();
- fNewCMD.Connection = conn;
- fNewCMD.Transaction = fSqlTransaction;
- fTranCmdList.Add(fNewCMD);
- NowCmdIndex++;
- ExecuteCount++;
- fSQL.Clear();//清空SQL
- }
- }
- if (ExecuteCount < fTranCmdList.Count)
- {//已执行CMD次数小于总CMD数,执行最后一条CMD
- fTranCmdList[fTranCmdList.Count - 1].CommandText = fSQL.ToString();
- fTranCmdList[fTranCmdList.Count - 1].ExecuteNonQuery();
- }
- fSqlTransaction.Commit();
- return null;
- }
- catch (Exception ex)
- {
- fSqlTransaction.Rollback();
- StringBuilder fSQL = new StringBuilder();
- foreach (SQLWithParameter fSQLWithParameter in aSQLWithParameterList)
- {
- fSQL.Append(fSQLWithParameter.SQL.ToString() + ";");
- }
- YFmk.Lib.LocalLog.WriteByDate(fSQL.ToString()+" 错误:"+ex.Message, "ORM");
- return ex.Message;
- }
- }
- }
- /// <summary>
- /// 执行查询语句,返回DataSet
- /// </summary>
- /// <param name="SQLString">查询语句</param>
- /// <returns>DataSet</returns>
- public DataSet Query(string SQLString)
- {
- using (DbConnection conn = _DbFactory.CreateConnection())
- {
- conn.ConnectionString = mDBConfig.ConnString;
- using (DbCommand cmd = _DbFactory.CreateCommand())
- {
- PrepareCommand(cmd, conn, SQLString);
- using (DbDataAdapter da = _DbFactory.CreateDataAdapter())
- {
- da.SelectCommand = cmd;
- DataSet ds = new DataSet();
- try
- {
- da.Fill(ds, "ds");
- cmd.Parameters.Clear();
- }
- catch (Exception ex)
- {
- }
- return ds;
- }
- }
- }
- }
- /// <summary>
- /// 执行查询语句,返回DataSet
- /// </summary>
- /// <param name="aSQLWithParameter">查询语句</param>
- /// <returns>DataSet</returns>
- public DataSet Query(SQLWithParameter aSQLWithParameter)
- {
- using (DbConnection conn = _DbFactory.CreateConnection())
- {
- conn.ConnectionString = mDBConfig.ConnString;
- using (DbCommand cmd = _DbFactory.CreateCommand())
- {
- PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);
- using (DbDataAdapter da = _DbFactory.CreateDataAdapter())
- {
- da.SelectCommand = cmd;
- DataSet ds = new DataSet();
- da.Fill(ds, "ds");
- cmd.Parameters.Clear();
- return ds;
- }
- }
- }
- }
- #endregion
- #region 私有函数
- private void PrepareCommand(DbCommand cmd, DbConnection conn, string cmdText)
- {
- if (conn.State != ConnectionState.Open)
- conn.Open();
- cmd.Connection = conn;
- cmd.CommandText = cmdText;
- }
- private void PrepareCommand(DbCommand cmd, DbConnection conn, string cmdText, List<DbParameter> cmdParms)
- {
- if (conn.State != ConnectionState.Open)
- conn.Open();
- cmd.Connection = conn;
- cmd.CommandText = cmdText;
- if (cmdParms != null && cmdParms.Count>0)
- {
- cmd.Parameters.AddRange(cmdParms.ToArray());
- }
- }
- #endregion