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
View Code
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
8 using System.Data.OleDb;
12 //功能：OleDb資料通路基礎類；
14 //日期：2011-12-05；
15 public class OleDbHelper
21 get { return connectionString; }
25 private OleDbConnection connection;
26
28 public OleDbHelper()
31 this.connection = new OleDbConnection(this.connectionString);
35 public OleDbHelper(string connectionString)
38 this.connection = new OleDbConnection(this.connectionString);
42 public DataTable ExecuteQuery(string selectCommandText, params OleDbParameter[] cmdParameters)
44 OleDbCommand command = new OleDbCommand();
46 OleDbDataAdapter adapter = new OleDbDataAdapter(command);
52 }
54 //執行多條SQL指令（Insert/Delete/Update），以資料庫事務方式
55 public int ExecuteNonQueryTrans(List<OleDbCmdTextAndParams> listCmdTextAndParams)
57 int rowsCountAffected = 0;
59 OleDbTransaction trans = BeginTransaction();
60 OleDbCommand command = new OleDbCommand();
64 foreach (OleDbCmdTextAndParams cmdTextAndParams in listCmdTextAndParams)
66 PrepareCommand(command, trans, CommandType.Text, cmdTextAndParams.CommandText, cmdTextAndParams.CommandParameters);
84 public OleDbTransaction BeginTransaction()
88 OleDbTransaction trans = connection.BeginTransaction();
94 public int ExecuteNonQuery(string cmdText, params OleDbParameter[] cmdParameters)
102 public int ExecuteNonQuery(OleDbTransaction inTransaction, string cmdText, params OleDbParameter[] cmdParameters)
105 OleDbCommand command = new OleDbCommand();
115 public Object ExecuteScalar(string selectCommandText, params OleDbParameter[] cmdParameters)
123 public Object ExecuteScalar(OleDbTransaction inTransaction, string selectCommandText, params OleDbParameter[] cmdParameters)
125 OleDbCommand command = new OleDbCommand();
137 if (connection.State != ConnectionState.Open)
142
144 private void PrepareCommand(OleDbCommand command, OleDbTransaction inTransation,
145 CommandType cmdType, string cmdText, OleDbParameter[] cmdParameters)
159 foreach (OleDbParameter param in cmdParameters)
163 }
OleDbCmdTextAndParams.cs
<a></a>
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 }