適用於關係型資料庫的抽象工廠的資料庫訪問類

適用於SQLite、Access、MySQL、MSSQLServer、Oracle這類別關係型資料庫，不適於非關係型資料庫，例如MongoDB。

下面是代碼：

  1 using System;  2 using System.Configuration;  3 using System.Data;  4 using System.Data.Common;  5 using System.Data.SQLite;  6   7 namespace WinFormTest  8 {  9     public static class DbHelperSQL 10     { 11         private static readonly object Obj4Lock = new object(); 12         private static readonly string ConnectionString = ConfigurationManager. 13             ConnectionStrings["ConnectionString"].ConnectionString; 14  15         /// <summary> 16         ///  程式每次運行都只建立一個工廠 17         /// </summary> 18         private static DbProviderFactory DBProviderFactory 19         { 20             get 21             { 22                 var providerFactoryString = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName; 23                 if (providerFactoryString == "System.Data.SQLite") 24                 { 25                     return SQLiteFactory.Instance; 26                 } 27  28                 var dbProviderFactory = DbProviderFactories.GetFactory(providerFactoryString); 29                 if (dbProviderFactory == null) 30                 { 31                     throw new Exception("dbProviderFactory==null"); 32                 } 33  34                 return dbProviderFactory; 35             } 36         } 37  38  39         public static int ExecuteNonQuery(string commandText, bool isStoredProcedure) 40         { 41             if (commandText == null) 42             { 43                 throw new ArgumentNullException("commandText"); 44             } 45  46             int result; 47  48             lock (Obj4Lock) 49             { 50                 using (var connection = CreateConnection()) 51                 { 52                     using (var transaction = CreateTransaction(connection)) 53                     { 54                         using (var command = CreateCommand(commandText, isStoredProcedure, connection, transaction)) 55                         { 56                             try 57                             { 58                                 result = command.ExecuteNonQuery(); 59                                 command.Parameters.Clear(); 60                                 transaction.Commit(); 61                             } 62                             catch 63                             { 64                                 transaction.Rollback(); 65                                 return 0; 66                             } 67                         } 68                     } 69                 } 70             } 71  72             return result; 73         } 74  75         public static object ExecuteScalar(string commandText, bool isStoredProcedure) 76         { 77             if (commandText == null) 78             { 79                 throw new ArgumentNullException("commandText"); 80             } 81  82             object result; 83  84             lock (Obj4Lock) 85             { 86                 using (var connection = CreateConnection()) 87                 { 88                     using (var transaction = CreateTransaction(connection)) 89                     { 90                         using (var command = CreateCommand(commandText, isStoredProcedure, connection, transaction)) 91                         { 92                             try 93                             { 94                                 result = command.ExecuteScalar(); 95                                 command.Parameters.Clear(); 96                                 transaction.Commit(); 97                             } 98                             catch 99                             {100                                 transaction.Rollback();101                                 return null;102                             }103                         }104                     }105                 }106             }107 108             return result;109         }110 111         public static IDataReader ExecuteReader(string commandText, bool isStoredProcedure)112         {113             if (commandText == null)114             {115                 throw new ArgumentNullException("commandText");116             }117 118             lock (Obj4Lock)119             {120                 var connection = CreateConnection();121 122                 var command = CreateCommand(commandText, isStoredProcedure, connection, null);123 124                 try125                 {126                     var myReader = command.ExecuteReader(CommandBehavior.CloseConnection);127                     command.Parameters.Clear();128                     return myReader;129                 }130                 catch131                 {132                     connection.Close();133                     throw;134                 }135             }136         }137 138         public static DataSet Query(string commandText, bool isStoredProcedure)139         {140             if (commandText == null)141             {142                 throw new ArgumentNullException("commandText");143             }144 145             var dataSet = new DataSet();146 147             lock (Obj4Lock)148             {149                 using (var connection = CreateConnection())150                 {151                     using (var transaction = CreateTransaction(connection))152                     {153                         using (var command = CreateCommand(commandText, isStoredProcedure, connection, transaction))154                         {155                             using (var dataAdapter = DBProviderFactory.CreateDataAdapter())156                             {157                                 if (dataAdapter == null)158                                 {159                                     throw new ArgumentNullException("commandText");160                                 }161 162                                 try163                                 {164                                     dataAdapter.Fill(dataSet, "dataSet");165                                     command.Parameters.Clear();166                                     transaction.Commit();167                                 }168                                 catch169                                 {170                                     transaction.Rollback();171                                     return null;172                                 }173                             }174                         }175                     }176                 }177             }178 179             return dataSet;180         }181 182 183         private static IDbConnection CreateConnection()184         {185             IDbConnection connection = DBProviderFactory.CreateConnection();186             if (connection == null)187             {188                 throw new Exception("connection == null");189             }190 191             connection.ConnectionString = ConnectionString;192 193             if (connection.State != ConnectionState.Open)194             {195                 connection.Open();196             }197 198             return connection;199         }200 201         private static IDbTransaction CreateTransaction(IDbConnection connection)202         {203             var transaction = connection.BeginTransaction();204             if (transaction == null)205             {206                 throw new ArgumentNullException("connection");207             }208             return transaction;209         }210 211         private static IDbCommand CreateCommand(string commandText, bool isStoredProcedure,212             IDbConnection connection, IDbTransaction transaction)213         {214             IDbCommand command = DBProviderFactory.CreateCommand();215             if (command == null)216             {217                 throw new Exception("command == null");218             }219 220             command.Connection = connection;221             command.CommandType = isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;222             command.CommandText = commandText;223             command.Transaction = transaction;224 225             return command;226         }227     }228 }

 

解釋：

1、Obj4Lock用於鎖定代碼塊，保證在多線程程式中，只有一個資料庫連接存在。

2、外部介面只有ExecuteNonQuery、ExecuteScalar、ExecuteReader、Query四個方法，可以使用預存程序和事務。

3、為什麼不用參數化查詢？一是因為面向介面編程的原因，程式中不能使用具體資料庫相關的參數對象，只能使用IDbParameter和DbParameter，這兩個是介面和抽象類別，不能夠執行個體化，可以寫在工廠中，但是沒法使用。二是我做的程式中不需要參數化查詢。

4、代碼可讀性良好，不必注釋了。

下面是設定檔：

1 <?xml version="1.0" encoding="utf-8" ?>2 <configuration>3   <connectionStrings>4     <!--<add name="ConnectionString" connectionString="Data Source=Test" providerName="System.Data.SQLite"/>-->5     <!--<add name="ConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=TestAccess.accdb" providerName="System.Data.OleDb"/>-->6     <add name="ConnectionString" connectionString="server=127.0.0.1;database=testDB;uid=root;pwd=123456" providerName="MySql.Data.MySqlClient"/>7   </connectionStrings>8 </configuration>

通過更改設定檔，就可以實現對資料庫種類的屏蔽了。

下面是用了抽象工廠的資料庫訪問類和普通資料庫訪問類的效率差異：

資料庫	類型	2	5	10	20	50	100	200
SQLite	檔案	0.197s	0.285s	0.547s	1.099s	2.509s	5.632s	11.386s
SQLite（抽象工廠）	檔案	0.246s	0.362s	0.499s	1.191s	2.539s	6.692s	10.464s
Access	檔案	0.199s	0.280s	0.521s	1.071s	2.604s	5.320s	10.760s
Access（抽象工廠）	檔案	0.363s	0.510s	0.780s	1.326s	2.805s	5.569s	10.648s
MySql	伺服器	0.217s	0.106s	0.226s	0.331s	0.952s	1.545s	2.666s
MySql（抽象工廠）	伺服器	0.310s	0.323s	0.403s	0.545s	0.942s	1.623s	2.746s

表中的數字是線程數，每個線程只執行一條語句。

All Comments are Welcome.