几年前写的一个支持多数据库切换的设计

前言

最新心血来潮，决定把以前做的一些东西做一些整理，于是就有这一篇文章，这次决定写的是大学时期做的一个小项目，里面关于数据库访问的SqlHelper类，项目采用CS结构，支持多种数据库切换，其中也参考了一些网上的东西，如果有版权问题，请私下联系我处理，项目比较简单，对入门还是有一定帮助的，大神请绕道。

正文

1、先看SqlServer数据处理部分

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 using System.Data;
  5 using System.Data.SqlClient;
  6 using System.Data.Common;
  7 
  8 namespace DrugShop.Date.SqlServer
  9 {
 10     internal class DataAccessUtil
 11     {
 12         /// <summary>
 13         /// 返回上次操作的第一行、第一列
 14         /// </summary>
 15         /// <param name="sql"></param>
 16         /// <param name="paramList"></param>
 17         /// <param name="conn"></param>
 18         /// <returns></returns>
 19         public static object ExecuteScalar(string sql, List<SqlParameter> paramList, SqlConnection  conn)
 20         {
 21             if (conn.State!=ConnectionState.Open)
 22             {
 23                 conn.Open();
 24             }
 25             SqlCommand cmd = conn.CreateCommand();
 26             cmd.CommandText = sql;
 27             cmd.Parameters.AddRange(paramList.ToArray());
 28             return cmd.ExecuteScalar();
 29         }
 30         /// <summary>
 31         /// 返回上次操作的第一行、第一列
 32         /// </summary>
 33         /// <param name="sql"></param>
 34         /// <param name="paramList"></param>
 35         /// <param name="trans"></param>
 36         /// <returns></returns>
 37         public static object ExecuteScalar(string sql, List<SqlParameter> paramList, SqlTransaction trans)
 38         {
 39             SqlCommand cmd = trans.Connection.CreateCommand();
 40             cmd.Transaction = trans;
 41             cmd.CommandText = sql;
 42             cmd.Parameters.AddRange(paramList.ToArray());
 43             return cmd.ExecuteScalar();
 44         }
 45         #region 执行非查询式数据库操作
 46         
 47         /// <summary>
 48         /// 执行传入连接的非查询SQL语句
 49         /// </summary>
 50         /// <param name="sql"></param>
 51         /// <param name="paramList"></param>
 52         /// <param name="conn"></param>
 53         public static void ExecuteNonQuery(string sql, List<SqlParameter> paramList,SqlConnection conn)
 54         {
 55             ExecuteNonQuery(sql, paramList, CommandType.Text,conn);
 56         }
 57        
 58         /// <summary>
 59         /// 执行传入连接的非查询SQL语句或存储过程
 60         /// </summary>
 61         /// <param name="sql"></param>
 62         /// <param name="paramList"></param>
 63         /// <param name="type"></param>
 64         /// <param name="conn"></param>
 65         public static void ExecuteNonQuery(string sql, List<SqlParameter> paramList, CommandType type, SqlConnection conn)
 66         {
 67             if (conn.State!=ConnectionState.Open)
 68             {
 69                 conn.Open();
 70             }
 71             SqlCommand cmd = conn.CreateCommand();
 72             cmd.CommandText = sql;
 73             cmd.CommandType = type;
 74             cmd.Parameters.AddRange(paramList.ToArray());
 75             cmd.ExecuteNonQuery();
 76         }
 77         /// <summary>
 78         /// 支持事物的非查询式SQL或存储过程
 79         /// </summary>
 80         /// <param name="sql"></param>
 81         /// <param name="paramList"></param>
 82         /// <param name="trans"></param>
 83         public static void ExecuteNonQuery(string sql, List<SqlParameter> paramList, SqlTransaction trans)
 84         {
 85             ExecuteNonQuery(sql, paramList, CommandType.Text, trans);
 86         }
 87         
 88         /// <summary>
 89         /// 支持事物的非查询式SQL或存储过程
 90         /// </summary>
 91         /// <param name="sqlProcedure"></param>
 92         /// <param name="paramList"></param>
 93         /// <param name="type"></param>
 94         /// <param name="trans"></param>
 95         public static void ExecuteNonQuery(string sqlProcedure, List<SqlParameter> paramList, CommandType type, SqlTransaction trans)
 96         {
 97             SqlCommand cmd = trans.Connection.CreateCommand();
 98             cmd.Transaction = trans;
 99             cmd.CommandText = sqlProcedure;
100             cmd.Parameters.AddRange(paramList.ToArray());
101             cmd.CommandType = type;
102             cmd.ExecuteNonQuery();
103         }
104         
105         public static void ExecuteNonQuery(string sqlProcedure, List<SqlParameter> paramList, CommandType type, SqlConnection conn,SqlTransaction trans)
106         {
107             SqlCommand cmd = trans.Connection.CreateCommand();
108             cmd.Transaction = trans;
109             cmd.CommandText = sqlProcedure;
110             cmd.Parameters.AddRange(paramList.ToArray());
111             cmd.CommandType = type;
112             cmd.ExecuteNonQuery();
113         }
114         #endregion
115         #region 执行查询式数据库操作
116         /// <summary>
117         ///执行查询式SQL语句
118         /// </summary>
119         /// <param name="sql"></param>
120         /// <param name="paramList"></param>
121         /// <returns></returns>
122         public static SqlDataReader ExecuteReader(string sql, List<SqlParameter> paramList,SqlConnection conn)
123         {
124             return ExecuteReader(sql, paramList, CommandType.Text,conn);
125         }
126         /// <summary>
127         /// 执行查询式SQL语句或者存储过程
128         /// </summary>
129         /// <param name="sqlOrProcedure"></param>
130         /// <param name="paramList"></param>
131         /// <param name="type"></param>
132         /// <returns></returns>
133         public static SqlDataReader ExecuteReader(string sqlOrProcedure, List<SqlParameter> paramList, CommandType type,SqlConnection conn)
134         {
135             if (conn.State!=ConnectionState.Open)
136             {
137                 conn.Open();
138             }
139             SqlCommand cmd = conn.CreateCommand();
140             cmd.CommandText = sqlOrProcedure;
141             cmd.Parameters.AddRange(paramList.ToArray());
142             cmd.CommandType = type;
143             return cmd.ExecuteReader(CommandBehavior.CloseConnection);
144         }
145         public static SqlDataReader ExecuteReader(string sql, List<SqlParameter> paramList, SqlTransaction trans)
146         {
147             SqlConnection conn = trans.Connection;
148             SqlCommand cmd = conn.CreateCommand();
149             cmd.Transaction = trans;
150             cmd.CommandText = sql;
151             cmd.Parameters.AddRange(paramList.ToArray());
152             return cmd.ExecuteReader();
153         }
154         public static DataSet ExecuteDataSet(string sql, List<SqlParameter> paramList,SqlConnection conn)
155         {
156             return ExecuteDataSet(sql, paramList, CommandType.Text,conn);
157         }
158         public static DataSet ExecuteDataSet(string sqlOrProcedure, List<SqlParameter> paramList, CommandType type,SqlConnection conn)
159         {
160             if (conn.State!=ConnectionState.Open )
161             {
162                 conn.Open();
163             }
164             SqlCommand cmd = conn.CreateCommand();
165             cmd.CommandText = sqlOrProcedure;
166             cmd.Parameters.AddRange(paramList.ToArray());
167             cmd.CommandType = type;
168             DataSet ds = new DataSet();
169             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
170             adapter.Fill(ds);
171             return ds;
172         }
173         public static DataTable ExecuteDataTable(string sql, List<SqlParameter> paramList,SqlConnection conn)
174         {
175             return ExecuteDataTable(sql, paramList, CommandType.Text,conn);
176         }
177         public static DataTable ExecuteDataTable(string sqlOrProcedure, List<SqlParameter> paramList, CommandType type,SqlConnection conn)
178         {
179             if (conn.State!=ConnectionState.Open)
180             {
181                 conn.Open();
182             }
183             SqlCommand cmd = conn.CreateCommand();
184             cmd.CommandText = sqlOrProcedure;
185             cmd.Parameters.AddRange(paramList.ToArray());
186             cmd.CommandType = type;
187             DataTable dt = new DataTable();
188             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
189             adapter.Fill(dt);
190             return dt;
191         }
192         #endregion
193     }
194 }

View Code

其他的数据库就不写了，因为只实现了Sql Server部分，只是设计上可以支持数据库类型的切换，实际上并没有实现，其他的数据库可以参考SqlServer的代码实现。

2、通过工厂模式来打开不同的数据库

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 using System.Data;
  5 using System.Configuration;
  6 
  7 namespace DrugShop.Date
  8 {
  9     public class DAOFactory
 10     {
 11         private enum DatabaseType
 12         {
 13             SqlServer,
 14             MySql,
 15             Oracole,
 16             DB2,
 17             Postgre,
 18             SQLite
 19         }
 20         private DatabaseType _DbType;
 21         private string _ProviderName;
 22         private string _ConnectionString;
 23         private DAOFactory()
 24         {
 25             try
 26             {
 27                 string connKey = "Dbconn";
 28                 _ProviderName = ConfigurationManager.ConnectionStrings[connKey].ProviderName;
 29                 _ConnectionString = ConfigurationManager.ConnectionStrings[connKey].ConnectionString;
 30                 if (_ProviderName == "System.Data.SqlClient")
 31                 {
 32                     _DbType = DatabaseType.SqlServer;
 33                 }
 34                 else if (_ProviderName == "System.Data.Oracole")
 35                 {
 36                     _DbType = DatabaseType.Oracole;
 37                 }
 38                 else if (_ProviderName == "System.Data.MySql")
 39                 {
 40                     _DbType = DatabaseType.MySql;
 41                 }
 42                 else if (_ProviderName == "System.Data.DB2")
 43                 {
 44                     _DbType = DatabaseType.DB2;
 45                 }
 46                 else if (_ProviderName == "System.Data.Postgre")
 47                 {
 48                     _DbType = DatabaseType.Postgre;
 49                 }
 50                 else if (_ProviderName == "System.Data.SQLite")
 51                 {
 52                     _DbType = DatabaseType.SQLite;
 53                 }
 54             }
 55             catch (Exception)
 56             {
 57                 throw new ApplicationException("读取数据库配置文件错误，获取数据库文件失败。");
 58             }
 59             
 60 
 61         }
 62         private static DAOFactory _Instance=null;
 63         public static DAOFactory Instance
 64         {
 65             get {
 66                 if (_Instance==null)
 67                 {
 68                     _Instance = new DAOFactory();
 69                 }
 70                 return _Instance;
 71             }
 72         }
 73         public IEventLogDAO CreateEventLogDAO()
 74         {
 75             IEventLogDAO dao = null;
 76             if (_DbType==DatabaseType.SqlServer )
 77             {
 78                 dao = new SqlServer.EventLogDAO();
 79             }
 80             else if (_DbType == DatabaseType.MySql)
 81             {
 82                 dao = new MySql.EventLogDAO();
 83             }
 84             else if (_DbType == DatabaseType.Oracole)
 85             {
 86                 dao = new Oracle.EventLogDAO();
 87             }
 88             else if (_DbType == DatabaseType.DB2)
 89             {
 90                 dao = new DB2.EventLogDAO();
 91             }
 92             else if (_DbType == DatabaseType.Postgre)
 93             {
 94                 dao = new Postgre.EventLogDAO();
 95             }
 96             return dao;
 97         }
 98         public IBillsDAO CreateBillsDAO()
 99         {
100             IBillsDAO dao = null;
101             if (_DbType == DatabaseType.SqlServer)
102             {
103                 dao = new SqlServer.BillsDAO();
104             }
105             else if (_DbType == DatabaseType.MySql)
106             {
107                 dao = new MySql.BillsDAO();
108             }
109             else if (_DbType == DatabaseType.Oracole)
110             {
111                 dao = new Oracole.BillsDAO();
112             }
113             else if (_DbType == DatabaseType.DB2)
114             {
115                 dao = new DB2.BillsDAO();
116             }
117             else if (_DbType == DatabaseType.Postgre)
118             {
119                 dao = new Postgre.BillsDAO();
120             }
121             return dao;
122         }
123         public IBillsTypeDAO CreateBillsTypeDAO()
124         {
125             IBillsTypeDAO dao = null;
126             if (_DbType == DatabaseType.SqlServer)
127             {
128                 dao = new SqlServer.BillsTypeDAO();
129             }
130             else if (_DbType == DatabaseType.MySql)
131             {
132                 dao = new MySql.BillsTypeDAO();
133             }
134             else if (_DbType == DatabaseType.Oracole)
135             {
136                 dao = new Oracole.BillsTypeDAO();
137             }
138             else if (_DbType == DatabaseType.DB2)
139             {
140                 dao = new DB2.BillsTypeDAO();
141             }
142             else if (_DbType == DatabaseType.Postgre)
143             {
144                 dao = new Postgre.BillsTypeDAO();
145             }
146             return dao;
147         }
148         public ICompanyDAO CreateCompanyDAO()
149         {
150             ICompanyDAO dao = null;
151             if (_DbType == DatabaseType.SqlServer)
152             {
153                 dao = new SqlServer.CompanyDAO();
154             }
155             else if (_DbType == DatabaseType.MySql)
156             {
157                 dao = new MySql.CompanyDAO();
158             }
159             else if (_DbType == DatabaseType.Oracole)
160             {
161                 dao = new Oracole.CompanyDAO();
162             }
163             else if (_DbType == DatabaseType.DB2)
164             {
165                 dao = new DB2.CompanyDAO();
166             }
167             else if (_DbType == DatabaseType.Postgre)
168             {
169                 dao = new Postgre.CompanyDAO();
170             }
171             return dao;
172         }
173         public ICompanyTypeDAO CreateCompanyTypeDAO()
174         {
175             ICompanyTypeDAO dao = null;
176             if (_DbType == DatabaseType.SqlServer)
177             {
178                 dao = new SqlServer.CompanyTypeDAO();
179             }
180             else if (_DbType == DatabaseType.MySql)
181             {
182                 dao = new MySql.CompanyTypeDAO();
183             }
184             else if (_DbType == DatabaseType.Oracole)
185             {
186                 dao = new Oracole.CompanyTypeDAO();
187             }
188             else if (_DbType == DatabaseType.DB2)
189             {
190                 dao = new DB2.CompanyTypeDAO();
191             }
192             else if (_DbType == DatabaseType.Postgre)
193             {
194                 dao = new Postgre.CompanyTypeDAO();
195             }
196             return dao;
197         }
198         public IDrugCategoryDAO CreateDrugCategoryDAO()
199         {
200             IDrugCategoryDAO dao = null;
201             if (_DbType == DatabaseType.SqlServer)
202             {
203                 dao = new SqlServer.DrugCategoryDAO();
204             }
205             else if (_DbType == DatabaseType.MySql)
206             {
207                 dao = new MySql.DrugCategoryDAO();
208             }
209             else if (_DbType == DatabaseType.Oracole)
210             {
211                 dao = new Oracole.DrugCategoryDAO();
212             }
213             else if (_DbType == DatabaseType.DB2)
214             {
215                 dao = new DB2.DrugCategoryDAO();
216             }
217             else if (_DbType == DatabaseType.Postgre)
218             {
219                 dao = new Postgre.DrugCategoryDAO();
220             }
221             return dao;
222         }
223         public IDrugFromDAO CreateDrugFromDAO()
224         {
225             IDrugFromDAO dao = null;
226             if (_DbType == DatabaseType.SqlServer)
227             {
228                 dao = new SqlServer.DrugFromDAO();
229             }
230             else if (_DbType == DatabaseType.MySql)
231             {
232                 dao = new MySql.DrugFromDAO();
233             }
234             else if (_DbType == DatabaseType.Oracole)
235             {
236                 dao = new Oracole.DrugFromDAO();
237             }
238             else if (_DbType == DatabaseType.DB2)
239             {
240                 dao = new DB2.DrugFromDAO();
241             }
242             else if (_DbType == DatabaseType.Postgre)
243             {
244                 dao = new Postgre.DrugFromDAO();
245             }
246             return dao;
247         }
248         public IDrugsDAO CreateDrugsDAO()
249         {
250             IDrugsDAO dao = null;
251             if (_DbType == DatabaseType.SqlServer)
252             {
253                 dao = new SqlServer.DrugsDAO();
254             }
255             else if (_DbType == DatabaseType.MySql)
256             {
257                 dao = new MySql.DrugsDAO();
258             }
259             else if (_DbType == DatabaseType.Oracole)
260             {
261                 dao = new Oracole.DrugsDAO();
262             }
263             else if (_DbType == DatabaseType.DB2)
264             {
265                 dao = new DB2.DrugsDAO();
266             }
267             else if (_DbType == DatabaseType.Postgre)
268             {
269                 dao = new Postgre.DrugsDAO();
270             }
271             return dao;
272         }
273         public IResourceDAO CreateResourceDAO()
274         {
275             IResourceDAO dao = null;
276             if (_DbType == DatabaseType.SqlServer)
277             {
278                 dao = new SqlServer.ResourceDAO();
279             }
280             else if (_DbType == DatabaseType.MySql)
281             {
282                 dao = new MySql.ResourceDAO();
283             }
284             else if (_DbType == DatabaseType.Oracole)
285             {
286                 dao = new Oracole.ResourceDAO();
287             }
288             else if (_DbType == DatabaseType.DB2)
289             {
290                 dao = new DB2.ResourceDAO();
291             }
292             else if (_DbType == DatabaseType.Postgre)
293             {
294                 dao = new Postgre.ResourceDAO();
295             }
296             return dao;
297         }
298         public IStockDAO CreateStockDAO()
299         {
300             IStockDAO dao = null;
301             if (_DbType == DatabaseType.SqlServer)
302             {
303                 dao = new SqlServer.StockDAO();
304             }
305             else if (_DbType == DatabaseType.MySql)
306             {
307                 dao = new MySql.StockDAO();
308             }
309             else if (_DbType == DatabaseType.Oracole)
310             {
311                 dao = new Oracole.StockDAO();
312             }
313             else if (_DbType == DatabaseType.DB2)
314             {
315                 dao = new DB2.StockDAO();
316             }
317             else if (_DbType == DatabaseType.Postgre)
318             {
319                 dao = new Postgre.StockDAO();
320             }
321             return dao;
322         }
323         public IStorehouseDAO CreateStorehouseDAO()
324         {
325             IStorehouseDAO dao = null;
326             if (_DbType == DatabaseType.SqlServer)
327             {
328                 dao = new SqlServer.StorehouseDAO();
329             }
330             else if (_DbType == DatabaseType.MySql)
331             {
332                 dao = new MySql.StorehouseDAO();
333             }
334             else if (_DbType == DatabaseType.Oracole)
335             {
336                 dao = new Oracole.StorehouseDAO();
337             }
338             else if (_DbType == DatabaseType.DB2)
339             {
340                 dao = new DB2.StorehouseDAO();
341             }
342             else if (_DbType == DatabaseType.Postgre)
343             {
344                 dao = new Postgre.StorehouseDAO();
345             }
346             return dao;
347         }
348         public IUserDAO CreateUserDAO()
349         {
350             IUserDAO dao = null;
351             if (_DbType == DatabaseType.SqlServer)
352             {
353                 dao = new SqlServer.UserDAO();
354             }
355             else if (_DbType == DatabaseType.MySql)
356             {
357                 dao = new MySql.UserDAO();
358             }
359             else if (_DbType == DatabaseType.Oracole)
360             {
361                 dao = new Oracole.UserDAO();
362             }
363             else if (_DbType == DatabaseType.DB2)
364             {
365                 dao = new DB2.UserDAO();
366             }
367             else if (_DbType == DatabaseType.Postgre)
368             {
369                 dao = new Postgre.UserDAO();
370             }
371             return dao;
372         }
373         public IDbConnection OpenConnection()
374         {
375             IDbConnection conn = System.Data.Common.DbProviderFactories.GetFactory(_ProviderName).CreateConnection();
376             conn.ConnectionString = _ConnectionString;
377             conn.Open();
378             return conn;
379         }
380     }
381 }

View Code

3、调用方式

 1 #region 药品
 2         /// <summary>
 3         /// 增加一个药品信息
 4         /// </summary>
 5         /// <param name="drug"></param>
 6         /// <param name="creator"></param>
 7         public void CreateDrug(Drugs drug,string creator)
 8         {
 9             using (IDbConnection conn=DAOFactory.Instance.OpenConnection())
10             {
11                 IDrugsDAO dao = DAOFactory.Instance.CreateDrugsDAO();
12                 dao.InsertDrug(drug,conn);
13             }
14         }
15         /// <summary>
16         /// 保存修改过的药品信息
17         /// </summary>
18         /// <param name="drug"></param>
19         /// <param name="Modifier"></param>
20         public void SaveDrug(Drugs drug,string Modifier)
21         {
22             using (IDbConnection conn=DAOFactory.Instance.OpenConnection())
23             {
24                 IDrugsDAO dao = DAOFactory.Instance.CreateDrugsDAO();
25                 dao.UpdateDrug(drug ,conn );
26             }
27         }
28         /// <summary>
29         /// 删除一个药品信息
30         /// </summary>
31         /// <param name="drug"></param>
32         /// <param name="Deleter"></param>
33         public void DeleteDrug(Drugs drug, string Deleter)
34         {
35             using (IDbConnection conn=DAOFactory.Instance.OpenConnection())
36             {
37                 IDrugsDAO dao = DAOFactory.Instance.CreateDrugsDAO();
38                 dao.DeleteDrug(drug.ID,conn);
39             }
40         }
41         /// <summary>
42         /// 获取所有药品信息
43         /// </summary>
44         /// <returns></returns>
45         public List<Drugs> GetAllDrugs()
46         {
47             using (IDbConnection conn=DAOFactory.Instance.OpenConnection())
48             {
49                 IDrugsDAO dao = DAOFactory.Instance.CreateDrugsDAO();
50                 return dao.SelectAllDrugs(conn);
51             }
52         }
53         /// <summary>
54         /// 根据ID查找一个药品信息
55         /// </summary>
56         /// <param name="id"></param>
57         /// <returns></returns>
58         public Drugs RetrieveDrug(int id)
59         {
60             using (IDbConnection conn=DAOFactory.Instance.OpenConnection())
61             {
62                 IDrugsDAO dao = DAOFactory.Instance.CreateDrugsDAO();
63                 return dao.SelectDrug(id,conn);
64             }
65         }
66         /// <summary>
67         /// 获取指定编码的药品
68         /// </summary>
69         /// <param name="code"></param>
70         /// <returns></returns>
71         public Drugs GetDrugsByCode(string code)
72         {
73             using (IDbConnection conn=DAOFactory.Instance.OpenConnection())
74             {
75                 IDrugsDAO dao = DAOFactory.Instance.CreateDrugsDAO();
76                 return dao.SelectDrug(code,conn);
77             }
78         }
79         /// <summary>
80         /// 根据条件进行模糊搜索
81         /// </summary>
82         /// <param name="searchCond">搜索条件</param>
83         /// <returns></returns>
84         public List<Drugs> SearchDrugs(string searchCond)
85         {
86             using (IDbConnection conn = DAOFactory.Instance.OpenConnection())
87             {
88                 IDrugsDAO dao = DAOFactory.Instance.CreateDrugsDAO();
89                 SearchDrugsTemplate template = new SearchDrugsTemplate();
90                 template.Code = searchCond;
91                 template.Name = searchCond;
92                 template.From = searchCond;
93                 template.Category = searchCond;
94                 return dao.SearchDrugs(template, conn);
95             }
96         }
97         #endregion

View Code

结尾

因为当时水平很菜，所以这个设计的也存在一些问题，后面发现动软代码生成器生成的架构采用了抽象工厂模式，耦合度更低。