适用于关系型数据库的抽象工厂的数据库访问类

适用于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                 try
125                 {
126                     var myReader = command.ExecuteReader(CommandBehavior.CloseConnection);
127                     command.Parameters.Clear();
128                     return myReader;
129                 }
130                 catch
131                 {
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                                 try
163                                 {
164                                     dataAdapter.Fill(dataSet, "dataSet");
165                                     command.Parameters.Clear();
166                                     transaction.Commit();
167                                 }
168                                 catch
169                                 {
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.