|
本示例代碼的關鍵是利用.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
|
