.NET数据库编程求索之路7.使用ADO.NET实现（工厂模式实现多数据库切换）（1）

7.使用ADO.NET实现（工厂模式-实现多数据库切换）（1）

7.1 解决方案框架

解决方案(.sln)包含以下几个项目：

（1）类库项目HomeShop.DbUtility，数据访问实用工具；

（2）类库项目HomeShop.Model，实体层；【同5】

（3）类库项目HomeShop.DALFactory，数据访问层工厂；

（4）类库项目HomeShop.DALInterface，数据访问层接口；

（5）类库项目HomeShop.DALOfSql，SQLServer数据访问层；

（6）类库项目HomeShop.DALOfAccess，Access数据访问层；

（4）类库项目HomeShop.BLL，业务逻辑层；

（5）WinForm项目HomeShop.WinForm，界面层。

 7.2 数据访问实用工具HomeShop.DbUtility

SqlDbHelper.cs

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 //新添命名空间
  6 using System.Data;
  7 using System.Configuration;
  8 using System.Data.SqlClient;
  9 
 10 namespace HomeShop.DbUtility
 11 {
 12     //功能：SQL Server数据访问基础类；
 13     //作者：夏春涛；
 14     //日期：2011-11-30；
 15     public class SqlDbHelper
 16     {
 17         //数据库连接字符串
 18         private string connectionString = "";
 19         public string ConnectionString
 20         {
 21             get {return connectionString; }
 22         }
 23 
 24         //数据库连接对象
 25         private SqlConnection connection;
 26         
 27         //默认构造函数，从应用程序配置文件中读取第1个数据库连接字符串
 28         public SqlDbHelper()
 29         {
 30             this.connectionString = ConfigurationManager.ConnectionStrings[1].ConnectionString;
 31             this.connection = new SqlConnection(this.connectionString);            
 32         }
 33 
 34         //重载构造函数，根据入参初始化数据库连接字符串
 35         public SqlDbHelper(string connectionString)
 36         {
 37             this.connectionString = connectionString;
 38             this.connection = new SqlConnection(this.connectionString);
 39         }
 40 
 41         //执行SQL命令（Select），获取返回数据
 42         public DataTable ExecuteQuery(string selectCommandText, params SqlParameter[] cmdParameters)
 43         {
 44             SqlCommand command = new SqlCommand();
 45             PrepareCommand(command, null, CommandType.Text, selectCommandText, cmdParameters);
 46             SqlDataAdapter adapter = new SqlDataAdapter(command);
 47             DataTable dataTable = new DataTable();
 48             adapter.Fill(dataTable);
 49             connection.Close();
 50 
 51             return dataTable;
 52         }        
 53 
 54         //执行多条SQL（Insert/Delete/Update），以数据库事务方式
 55         public int ExecuteNonQueryTrans(List<SqlCmdTextAndParams> listCmdTextAndParams)
 56         {
 57             int rowsCountAffected = 0;
 58             //开始数据库事务
 59             SqlTransaction trans = BeginTransaction();
 60             SqlCommand command = new SqlCommand();
 61             try
 62             {
 63                 //循环执行每一个CommandTextAndParameters对象
 64                 foreach (SqlCmdTextAndParams cmdTextAndParams in listCmdTextAndParams)
 65                 {
 66                     PrepareCommand(command, trans, CommandType.Text, cmdTextAndParams.CommandText, cmdTextAndParams.CommandParameters);
 67                     rowsCountAffected += command.ExecuteNonQuery();
 68                     //清空参数列表，避免参数重名问题
 69                     command.Parameters.Clear();
 70                 }
 71                 trans.Commit();//提交数据库事务
 72             }
 73             catch
 74             {
 75                 trans.Rollback();//回滚数据库事务
 76                 throw;
 77             }
 78             connection.Close();
 79 
 80             return rowsCountAffected;
 81         }
 82 
 83         //开始（生成）一个数据库事务
 84         public SqlTransaction BeginTransaction()
 85         {
 86             if (connection.State != ConnectionState.Open)
 87                 connection.Open();
 88             SqlTransaction trans = connection.BeginTransaction();
 89 
 90             return trans;
 91         }
 92 
 93         //执行SQL命令（Insert/Delete/Update）
 94         public int ExecuteNonQuery(string cmdText, params SqlParameter[] cmdParameters)
 95         {
 96             int rowsCountAffected = ExecuteNonQuery(null, cmdText, cmdParameters);
 97             connection.Close();
 98             return rowsCountAffected;
 99         }
100 
101         //在指定的数据库事务中执行SQL命令，最后要手工提交事务
102         public int ExecuteNonQuery(SqlTransaction inTransaction, string cmdText, params SqlParameter[] cmdParameters)
103         {
104             int rowsCountAffected = 0;
105             SqlCommand command = new SqlCommand();
106             PrepareCommand(command, inTransaction, CommandType.Text, cmdText, cmdParameters);
107             rowsCountAffected += command.ExecuteNonQuery();
108             command.Parameters.Clear();//清空参数列表，避免参数重名问题
109             //connection.Close();//在数据库事务结束时才能关闭连接
110 
111             return rowsCountAffected;
112         }
113 
114         //获取查询结果集中的第一行的第一列内容
115         public Object ExecuteScalar(string selectCommandText, params SqlParameter[] cmdParameters)
116         {
117             Object obj = ExecuteScalar(null, selectCommandText, cmdParameters);
118             connection.Close();
119             return obj;
120         }
121 
122         //获取查询结果集中的第一行的第一列内容
123         public Object ExecuteScalar(SqlTransaction inTransaction, string selectCommandText, params SqlParameter[] cmdParameters)
124         {
125             SqlCommand command = new SqlCommand();
126             PrepareCommand(command, inTransaction, CommandType.Text, selectCommandText, cmdParameters);
127             Object obj = command.ExecuteScalar();
128             command.Parameters.Clear();//清空参数列表，避免参数重名问题
129             //connection.Close();//在数据库事务结束时才能关闭连接
130 
131             return obj;
132         }
133 
134         //关闭数据库连接
135         public void Close()
136         {
137             if (connection.State != ConnectionState.Closed )
138                 connection.Close();
139         }
140 
141         /****************************************************************************************/
142 
143         //预处理Command对象
144         private void PrepareCommand(SqlCommand command, SqlTransaction inTransation,
145             CommandType cmdType, string cmdText, SqlParameter[] cmdParameters)
146         {
147             if (connection.State != ConnectionState.Open)
148                 connection.Open();
149 
150             command.Connection = connection;
151             command.CommandType = cmdType;
152             command.CommandText = cmdText;
153 
154             if (inTransation != null)
155                 command.Transaction = inTransation;
156 
157             if (cmdParameters != null)
158             {
159                 foreach (SqlParameter param in cmdParameters)
160                     command.Parameters.Add(param);
161             }
162         }
163     } 
164 }

 SqlCmdTextAndParams.cs

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Data.SqlClient;//新添命名空间
 6 
 7 namespace HomeShop.DbUtility
 8 {
 9     //用于保存一条SQL命令及对应参数的类
10     public class SqlCmdTextAndParams
11     {
12         public SqlCmdTextAndParams(string commandText, params SqlParameter[] commandParamters)
13         {
14             this.CommandText = commandText;
15             this.CommandParameters = commandParamters;
16         }
17 
18         public string CommandText { set; get; }
19 
20         public SqlParameter[] CommandParameters { set; get; }
21     }
22 }

 //===============================================================

//支持Access访问的OleDbHelper.cs

 OleDbHelper.cs

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 //新添命名空间
  6 using System.Data;
  7 using System.Configuration;
  8 using System.Data.OleDb;
  9 
 10 namespace HomeShop.DbUtility
 11 {
 12     //功能：OleDb数据访问基础类；
 13     //作者：夏春涛；
 14     //日期：2011-12-05；
 15     public class OleDbHelper
 16     {
 17         //数据库连接字符串
 18         private string connectionString = "";
 19         public string ConnectionString
 20         {
 21             get { return connectionString; }
 22         }
 23 
 24         //数据库连接对象
 25         private OleDbConnection connection;
 26 
 27         //默认构造函数，从应用程序配置文件中读取第1个数据库连接字符串
 28         public OleDbHelper()
 29         {
 30             this.connectionString = ConfigurationManager.ConnectionStrings[1].ConnectionString;
 31             this.connection = new OleDbConnection(this.connectionString);
 32         }
 33 
 34         //重载构造函数，根据入参初始化数据库连接字符串
 35         public OleDbHelper(string connectionString)
 36         {
 37             this.connectionString = connectionString;
 38             this.connection = new OleDbConnection(this.connectionString);
 39         }
 40 
 41         //执行SQL命令（Select），获取返回数据
 42         public DataTable ExecuteQuery(string selectCommandText, params OleDbParameter[] cmdParameters)
 43         {
 44             OleDbCommand command = new OleDbCommand();
 45             PrepareCommand(command, null, CommandType.Text, selectCommandText, cmdParameters);
 46             OleDbDataAdapter adapter = new OleDbDataAdapter(command);
 47             DataTable dataTable = new DataTable();
 48             adapter.Fill(dataTable);
 49             connection.Close();
 50 
 51             return dataTable;
 52         }
 53 
 54         //执行多条SQL命令（Insert/Delete/Update），以数据库事务方式
 55         public int ExecuteNonQueryTrans(List<OleDbCmdTextAndParams> listCmdTextAndParams)
 56         {
 57             int rowsCountAffected = 0;            
 58             //开始数据库事务
 59             OleDbTransaction trans = BeginTransaction();
 60             OleDbCommand command = new OleDbCommand();            
 61             try
 62             {
 63                 //循环执行每一个CommandTextAndParameters对象
 64                 foreach (OleDbCmdTextAndParams cmdTextAndParams in listCmdTextAndParams)
 65                 {
 66                     PrepareCommand(command, trans, CommandType.Text, cmdTextAndParams.CommandText, cmdTextAndParams.CommandParameters);                    
 67                     rowsCountAffected += command.ExecuteNonQuery();
 68                     //清空参数列表，避免参数重名问题
 69                     command.Parameters.Clear();
 70                 }
 71                 trans.Commit();//提交数据库事务
 72             }
 73             catch
 74             {
 75                 trans.Rollback();//回滚数据库事务
 76                 throw;
 77             }
 78             connection.Close();
 79 
 80             return rowsCountAffected;
 81         }
 82 
 83         //开始（生成）一个数据库事务
 84         public OleDbTransaction BeginTransaction()
 85         {
 86             if (connection.State != ConnectionState.Open)
 87                 connection.Open();
 88             OleDbTransaction trans = connection.BeginTransaction();
 89 
 90             return trans;
 91         }
 92 
 93         //执行SQL命令（Insert/Delete/Update）
 94         public int ExecuteNonQuery(string cmdText, params OleDbParameter[] cmdParameters)
 95         {
 96             int rowsCountAffected = ExecuteNonQuery(null, cmdText, cmdParameters);
 97             connection.Close();
 98             return rowsCountAffected;
 99         }
100 
101         //在指定的数据库事务中执行SQL命令，最后要手工提交事务
102         public int ExecuteNonQuery(OleDbTransaction inTransaction, string cmdText, params OleDbParameter[] cmdParameters)
103         {
104             int rowsCountAffected = 0;
105             OleDbCommand command = new OleDbCommand();
106             PrepareCommand(command, inTransaction, CommandType.Text, cmdText, cmdParameters);
107             rowsCountAffected += command.ExecuteNonQuery();
108             command.Parameters.Clear();//清空参数列表，避免参数重名问题
109             //connection.Close();//在数据库事务结束时才能关闭连接
110 
111             return rowsCountAffected;
112         }
113 
114         //获取查询结果集中的第一行的第一列内容
115         public Object ExecuteScalar(string selectCommandText, params OleDbParameter[] cmdParameters)
116         {
117             Object obj = ExecuteScalar(null, selectCommandText, cmdParameters);
118             connection.Close();
119             return obj;
120         }
121 
122         //获取查询结果集中的第一行的第一列内容
123         public Object ExecuteScalar(OleDbTransaction inTransaction, string selectCommandText, params OleDbParameter[] cmdParameters)
124         {
125             OleDbCommand command = new OleDbCommand();
126             PrepareCommand(command, inTransaction, CommandType.Text, selectCommandText, cmdParameters);
127             Object obj = command.ExecuteScalar();
128             command.Parameters.Clear();//清空参数列表，避免参数重名问题
129             //connection.Close();//在数据库事务结束时才能关闭连接
130 
131             return obj;
132         }
133 
134         //关闭数据库连接
135         public void Close()
136         {
137             if (connection.State != ConnectionState.Open)
138                 connection.Close();
139         }
140 
141         /****************************************************************************************/
142        
143         //预处理Command对象
144         private void PrepareCommand(OleDbCommand command, OleDbTransaction inTransation, 
145             CommandType cmdType, string cmdText, OleDbParameter[] cmdParameters)
146         {
147             if (connection.State != ConnectionState.Open)
148                 connection.Open();
149 
150             command.Connection = connection;
151             command.CommandType = cmdType;
152             command.CommandText = cmdText;
153 
154             if (inTransation != null)
155                 command.Transaction = inTransation;
156 
157             if (cmdParameters != null)
158             {
159                 foreach (OleDbParameter param in cmdParameters)
160                     command.Parameters.Add(param);
161             }
162         }
163     }
164 }

OleDbCmdTextAndParams.cs

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 //新添命名空间
 6 using System.Data.OleDb;
 7 
 8 namespace HomeShop.DbUtility
 9 {
10     //用于保存一条SQL命令及对应参数的类
11     public class OleDbCmdTextAndParams
12     {
13         public OleDbCmdTextAndParams(string commandText, params OleDbParameter[] commandParamters)
14         {
15             this.CommandText = commandText;
16             this.CommandParameters = commandParamters;
17         }
18 
19         public string CommandText { set; get; }
20 
21         public OleDbParameter[] CommandParameters { set; get; }
22     }
23 }

源码下载：/Files/SummerRain/NetDbDevRoad/7使用ADONET实现工厂模式DB切换.rar

数据下载：/Files/SummerRain/NetDbDevRoad/HomeShopDB.rar