日韩黑丝制服一区视频播放|日韩欧美人妻丝袜视频在线观看|九九影院一级蜜桃|亚洲中文在线导航|青草草视频在线观看|婷婷五月色伊人网站|日本一区二区在线|国产AV一二三四区毛片|正在播放久草视频|亚洲色图精品一区

搜索
分享

強(qiáng)大的數(shù)據(jù)庫(kù)操作類DBHelper

 黃金屋1 2017-09-11

 本示例代碼的關(guān)鍵是利用.net庫(kù)自帶的DbProviderFactory來(lái)生產(chǎn)數(shù)據(jù)庫(kù)操作對(duì)象。

從下圖中，可以看到其的多個(gè)核心方法，這些方法將在我們的超級(jí)DBHelper中使用。



仔細(xì)研究，你會(huì)發(fā)現(xiàn)每個(gè)數(shù)據(jù)庫(kù)的官方支持dll都有一個(gè)Instance對(duì)象，這個(gè)對(duì)象都是繼承了DbProviderFactory了。

因此利用這點(diǎn)，我們就可以實(shí)現(xiàn)兼容多種數(shù)據(jù)的超級(jí)DBHelper了。

以下為示例代碼,僅供參考學(xué)習(xí)，代碼只是我的ORM框架中的一個(gè)片段（其中暫時(shí)支持了SQLSERVER、MYSQL、SQLITE三種數(shù)據(jù)庫(kù)，LoadDbProviderFactory方法是將封裝在dll中的數(shù)據(jù)庫(kù)操作dll反射加載實(shí)例化的方法。）：

[csharp] view plain copy  
  1. /// <summary>  
  2.     /// 超級(jí)數(shù)據(jù)庫(kù)操作類  
  3.     /// <para>2015年12月21日</para>  
  4.     /// <para>v1.0</para>  
  5.     /// <para>葉琪</para>  
  6.     /// </summary>  
  7.     public class DBHelper  
  8.     {  
  9.         #region 屬性  
  10.         private DbProviderFactory _DbFactory;  
  11.         private DBConfig mDBConfig;  
  12.           
  13.         /// <summary>  
  14.         /// 數(shù)據(jù)庫(kù)連接配置  
  15.         /// </summary>  
  16.         public DBConfig DBConfig  
  17.         {  
  18.             get { return mDBConfig; }  
  19.         }  
  20.   
  21.         /// <summary>  
  22.         /// 表示一組方法，這些方法用于創(chuàng)建提供程序?qū)?shù)據(jù)源類的實(shí)現(xiàn)的實(shí)例。  
  23.         /// </summary>  
  24.         public DbProviderFactory DbFactory  
  25.         {  
  26.             get { return _DbFactory; }  
  27.             set { _DbFactory = value; }  
  28.         }  
  29.         #endregion  
  30.  
  31.         #region 構(gòu)造函數(shù)  
  32.         public DBHelper(DBConfig aORMConfig)  
  33.         {  
  34.             mDBConfig = aORMConfig;  
  35.             switch (mDBConfig.DBType)  
  36.             {  
  37.                 case ORMType.DBTypes.SQLSERVER:  
  38.                     _DbFactory = System.Data.SqlClient.SqlClientFactory.Instance;  
  39.                     break;  
  40.                 case ORMType.DBTypes.MYSQL:  
  41.                     LoadDbProviderFactory("MySql.Data.dll", "MySql.Data.MySqlClient.MySqlClientFactory");  
  42.                     break;  
  43.                 case ORMType.DBTypes.SQLITE:  
  44.                     LoadDbProviderFactory("System.Data.SQLite.dll", "System.Data.SQLite.SQLiteFactory");  
  45.                     break;  
  46.             }  
  47.         }  
  48.   
  49.         /// <summary>  
  50.         /// 動(dòng)態(tài)載入數(shù)據(jù)庫(kù)封裝庫(kù)  
  51.         /// </summary>  
  52.         /// <param name="aDLLName">數(shù)據(jù)庫(kù)封裝庫(kù)文件名稱</param>  
  53.         /// <param name="aFactoryName">工廠路徑名稱</param>  
  54.         private void LoadDbProviderFactory(string aDLLName, string aFactoryName)  
  55.         {  
  56.             string dllPath = string.Empty;  
  57.             if (System.AppDomain.CurrentDomain.RelativeSearchPath != null)  
  58.             {  
  59.                 dllPath = System.AppDomain.CurrentDomain.RelativeSearchPath+"\\"+ aDLLName;  
  60.             }  
  61.             else  
  62.             {  
  63.                 dllPath = System.AppDomain.CurrentDomain.BaseDirectory + aDLLName;  
  64.             }  
  65.             if (!File.Exists(dllPath))  
  66.             {//文件不存在，從庫(kù)資源中復(fù)制輸出到基目錄下  
  67.                 FileStream fdllFile = new FileStream(dllPath,FileMode.Create);  
  68.                 byte[] dllData = null;  
  69.                 if (aDLLName == "System.Data.SQLite.dll")  
  70.                 {  
  71.                     dllData = YFmk.ORM.Properties.Resources.System_Data_SQLite;  
  72.                 }  
  73.                 else if (aDLLName == "MySql.Data.dll")  
  74.                 {  
  75.                     dllData = YFmk.ORM.Properties.Resources.MySql_Data;  
  76.                 }  
  77.                 fdllFile.Write(dllData, 0, dllData.Length);  
  78.                 fdllFile.Close();  
  79.             }  
  80.             Assembly libAssembly = Assembly.LoadFile(dllPath);  
  81.             Type type = libAssembly.GetType(aFactoryName);  
  82.             foreach (FieldInfo fi in type.GetFields(BindingFlags.Static | BindingFlags.Public))  
  83.             {  
  84.                 if (fi.Name == "Instance")  
  85.                 {  
  86.                     _DbFactory = fi.GetValue(null) as DbProviderFactory;  
  87.                     return;  
  88.                 }  
  89.             }  
  90.         }  
  91.         #endregion  
  92.  
  93.         #region 數(shù)據(jù)庫(kù)操作  
  94.         /// <summary>  
  95.         /// 執(zhí)行一條計(jì)算查詢結(jié)果語(yǔ)句，返回查詢結(jié)果  
  96.         /// </summary>  
  97.         /// <param name="aSQLWithParameter">SQL語(yǔ)句及參數(shù)</param>  
  98.         /// <returns>查詢結(jié)果（object）</returns>  
  99.         public object GetSingle(SQLWithParameter aSQLWithParameter)  
  100.         {  
  101.             using (DbConnection conn = _DbFactory.CreateConnection())  
  102.             {  
  103.                 conn.ConnectionString = mDBConfig.ConnString;  
  104.                 using (DbCommand cmd = _DbFactory.CreateCommand())  
  105.                 {  
  106.                     PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);  
  107.                     object obj = cmd.ExecuteScalar();  
  108.                     cmd.Parameters.Clear();  
  109.                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  
  110.                     {  
  111.                         return null;  
  112.                     }  
  113.                     else  
  114.                     {  
  115.                         return obj;  
  116.                     }  
  117.                 }  
  118.             }  
  119.         }  
  120.   
  121.         /// <summary>  
  122.         /// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)  
  123.         /// </summary>  
  124.         /// <param name="aSQL">SQL語(yǔ)句</param>  
  125.         /// <returns>影響的記錄數(shù)</returns>  
  126.         public int ExecuteSql(string aSQL)  
  127.         {  
  128.             using (DbConnection conn = _DbFactory.CreateConnection())  
  129.             {  
  130.                 conn.ConnectionString = mDBConfig.ConnString;  
  131.                 using (DbCommand cmd = _DbFactory.CreateCommand())  
  132.                 {  
  133.                     PrepareCommand(cmd, conn, aSQL);  
  134.                     int rows = cmd.ExecuteNonQuery();  
  135.                     cmd.Parameters.Clear();  
  136.                     return rows;  
  137.                 }  
  138.             }  
  139.         }  
  140.   
  141.         /// <summary>  
  142.         /// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)  
  143.         /// </summary>  
  144.         /// <param name="aSQLWithParameter">SQL語(yǔ)句及參數(shù)</param>  
  145.         /// <returns></returns>  
  146.         public int ExecuteSql(SQLWithParameter aSQLWithParameter)  
  147.         {  
  148.             using (DbConnection conn = _DbFactory.CreateConnection())  
  149.             {  
  150.                 conn.ConnectionString = mDBConfig.ConnString;  
  151.                 using (DbCommand cmd = _DbFactory.CreateCommand())  
  152.                 {  
  153.                     PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);  
  154.                     int rows = cmd.ExecuteNonQuery();  
  155.                     cmd.Parameters.Clear();  
  156.                     return rows;  
  157.                 }  
  158.             }  
  159.         }  
  160.   
  161.         /// <summary>  
  162.         /// 執(zhí)行多條SQL語(yǔ)句，實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)。  
  163.         /// </summary>  
  164.         /// <param name="aSQLWithParameterList">參數(shù)化的SQL語(yǔ)句結(jié)構(gòu)體對(duì)象集合</param>          
  165.         public string ExecuteSqlTran(List<SQLWithParameter> aSQLWithParameterList)  
  166.         {  
  167.             using (DbConnection conn = _DbFactory.CreateConnection())  
  168.             {  
  169.                 conn.ConnectionString = mDBConfig.ConnString;  
  170.                 conn.Open();  
  171.                 DbTransaction fSqlTransaction = conn.BeginTransaction();  
  172.                 try  
  173.                 {  
  174.                     List<DbCommand> fTranCmdList = new List<DbCommand>();  
  175.                     //創(chuàng)建新的CMD  
  176.                     DbCommand fFirstCMD = _DbFactory.CreateCommand();  
  177.                     fFirstCMD.Connection = conn;  
  178.                     fFirstCMD.Transaction = fSqlTransaction;  
  179.                     fTranCmdList.Add(fFirstCMD);  
  180.                     int NowCmdIndex = 0;//當(dāng)前執(zhí)行的CMD索引值  
  181.                     int ExecuteCount = 0;//已經(jīng)執(zhí)行的CMD次數(shù)  
  182.                     StringBuilder fSQL = new StringBuilder();  
  183.                     foreach (SQLWithParameter fSQLWithParameter in aSQLWithParameterList)  
  184.                     {  
  185.                         fSQL.Append(fSQLWithParameter.SQL.ToString() + ";");  
  186.                         fTranCmdList[NowCmdIndex].Parameters.AddRange(fSQLWithParameter.Parameters.ToArray());  
  187.                         if (fTranCmdList[NowCmdIndex].Parameters.Count > 2000)  
  188.                         { //參數(shù)達(dá)到2000個(gè)，執(zhí)行一次CMD  
  189.                             fTranCmdList[NowCmdIndex].CommandText = fSQL.ToString();  
  190.                             fTranCmdList[NowCmdIndex].ExecuteNonQuery();  
  191.                             DbCommand fNewCMD = _DbFactory.CreateCommand();  
  192.                             fNewCMD.Connection = conn;  
  193.                             fNewCMD.Transaction = fSqlTransaction;  
  194.                             fTranCmdList.Add(fNewCMD);  
  195.                             NowCmdIndex++;  
  196.                             ExecuteCount++;  
  197.                             fSQL.Clear();//清空SQL  
  198.                         }  
  199.                     }  
  200.                     if (ExecuteCount < fTranCmdList.Count)  
  201.                     {//已執(zhí)行CMD次數(shù)小于總CMD數(shù)，執(zhí)行最后一條CMD  
  202.                         fTranCmdList[fTranCmdList.Count - 1].CommandText = fSQL.ToString();  
  203.                         fTranCmdList[fTranCmdList.Count - 1].ExecuteNonQuery();  
  204.                     }  
  205.                     fSqlTransaction.Commit();  
  206.                     return null;  
  207.                 }  
  208.                 catch (Exception ex)  
  209.                 {  
  210.                     fSqlTransaction.Rollback();  
  211.                     StringBuilder fSQL = new StringBuilder();  
  212.                     foreach (SQLWithParameter fSQLWithParameter in aSQLWithParameterList)  
  213.                     {  
  214.                         fSQL.Append(fSQLWithParameter.SQL.ToString() + ";");  
  215.                     }  
  216.                     YFmk.Lib.LocalLog.WriteByDate(fSQL.ToString()+" 錯(cuò)誤："+ex.Message, "ORM");  
  217.                     return ex.Message;  
  218.                 }  
  219.             }  
  220.         }  
  221.   
  222.         /// <summary>  
  223.         /// 執(zhí)行查詢語(yǔ)句，返回DataSet  
  224.         /// </summary>  
  225.         /// <param name="SQLString">查詢語(yǔ)句</param>  
  226.         /// <returns>DataSet</returns>  
  227.         public DataSet Query(string SQLString)  
  228.         {  
  229.             using (DbConnection conn = _DbFactory.CreateConnection())  
  230.             {  
  231.                 conn.ConnectionString = mDBConfig.ConnString;  
  232.                 using (DbCommand cmd = _DbFactory.CreateCommand())  
  233.                 {  
  234.                     PrepareCommand(cmd, conn, SQLString);  
  235.                     using (DbDataAdapter da = _DbFactory.CreateDataAdapter())  
  236.                     {  
  237.                         da.SelectCommand = cmd;  
  238.                         DataSet ds = new DataSet();  
  239.                         try  
  240.                         {  
  241.                             da.Fill(ds, "ds");  
  242.                             cmd.Parameters.Clear();  
  243.                         }  
  244.                         catch (Exception ex)  
  245.                         {  
  246.                               
  247.                         }  
  248.                         return ds;  
  249.                     }  
  250.                 }  
  251.             }  
  252.         }  
  253.   
  254.         /// <summary>  
  255.         /// 執(zhí)行查詢語(yǔ)句，返回DataSet  
  256.         /// </summary>  
  257.         /// <param name="aSQLWithParameter">查詢語(yǔ)句</param>  
  258.         /// <returns>DataSet</returns>  
  259.         public DataSet Query(SQLWithParameter aSQLWithParameter)  
  260.         {  
  261.             using (DbConnection conn = _DbFactory.CreateConnection())  
  262.             {  
  263.                 conn.ConnectionString = mDBConfig.ConnString;  
  264.                 using (DbCommand cmd = _DbFactory.CreateCommand())  
  265.                 {  
  266.                     PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);  
  267.                     using (DbDataAdapter da = _DbFactory.CreateDataAdapter())  
  268.                     {  
  269.                         da.SelectCommand = cmd;  
  270.                         DataSet ds = new DataSet();  
  271.                         da.Fill(ds, "ds");  
  272.                         cmd.Parameters.Clear();  
  273.                         return ds;  
  274.                     }  
  275.                 }  
  276.             }  
  277.         }  
  278.         #endregion  
  279.  
  280.         #region 私有函數(shù)  
  281.         private void PrepareCommand(DbCommand cmd, DbConnection conn, string cmdText)  
  282.         {  
  283.             if (conn.State != ConnectionState.Open)  
  284.                 conn.Open();  
  285.             cmd.Connection = conn;  
  286.             cmd.CommandText = cmdText;  
  287.         }  
  288.   
  289.         private void PrepareCommand(DbCommand cmd, DbConnection conn, string cmdText, List<DbParameter> cmdParms)  
  290.         {  
  291.             if (conn.State != ConnectionState.Open)  
  292.                 conn.Open();  
  293.             cmd.Connection = conn;  
  294.             cmd.CommandText = cmdText;  
  295.             if (cmdParms != null && cmdParms.Count>0)  
  296.             {  
  297.                 cmd.Parameters.AddRange(cmdParms.ToArray());  
  298.             }  
  299.         }  
  300.         #endregion  

    本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。
    轉(zhuǎn)藏 分享 獻(xiàn)花（0）

    0條評(píng)論

    發(fā)表

    請(qǐng)遵守用戶 評(píng)論公約

    類似文章 更多