5.使用ADO.NET實作（三層架構篇-使用List傳遞資料）（1）
5.1 解決方案架構
解決方案(.sln)包含以下幾個項目：
（1）類庫項目HomeShop.DbUtility，資料通路實用工具；【同4.2】
（2）類庫項目HomeShop.Model，實體層；【同4.3】
（3）類庫項目HomeShop.DAL，資料通路層；
（4）類庫項目HomeShop.BLL，業務邏輯層；
（5）WinForm項目HomeShop.WinForm，界面層。
5.2 資料通路層HomeShop.DAL
OrderDAO.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.Data.SqlClient;
8 using HomeShop.DbUtility;
9 using HomeShop.Model;
10
11 namespace HomeShop.DAL
12 {
13 public class OrderDAO
14 {
15 protected SqlDbHelper dbHelper;
16
17 public OrderDAO()
18 {
19 this.dbHelper = new SqlDbHelper();
20 }
21
22 public OrderDAO(string connectionString)
23 {
24 this.dbHelper = new SqlDbHelper(connectionString);
25 }
26
27 //添加
28 public int Add(Order order)
29 {
30 int rowsCountAffected = 0;
31 SqlTransaction trans = dbHelper.BeginTransaction();
32 try
33 {
34 //新增訂單基本資訊，并使用SQL的系統函數@@IDENTITY擷取新增訂單的ID
35 string sql = @"INSERT INTO [Order]([OrderTime],
36 [OrderStateCode],
37 [CustomerName],
38 [CustomerPhoneNo],
39 [CustomerAddress])
40 VALUES(@OrderTime,
41 @OrderStateCode,
42 @CustomerName,
43 @CustomerPhoneNo,
44 @CustomerAddress)
45 SET @OrderID = @@IDENTITY ";
46 //@OrderID作為傳出參數，用于擷取新增訂單的ID
47 SqlParameter paramOrderID = new SqlParameter("@OrderID", SqlDbType.Int);
48 paramOrderID.Direction = ParameterDirection.Output;
49 SqlParameter[] parameters = {
50 new SqlParameter("@OrderTime", order.OrderTime),
51 new SqlParameter("@OrderStateCode", order.OrderStateCode),
52 new SqlParameter("@CustomerName", order.CustomerName),
53 new SqlParameter("@CustomerPhoneNo", order.CustomerPhoneNo),
54 new SqlParameter("@CustomerAddress", order.CustomerAddress),
55 paramOrderID};
56 rowsCountAffected += dbHelper.ExecuteNonQueryTrans(trans, sql, parameters);
57 order.OrderID = (int)paramOrderID.Value;
58 //-----------------------------------------------------------
59 //循環添加訂購商品資訊
60 for (int i = 0; i < order.OrderItems.Count; i++)
61 {
62 string sqlX = @"INSERT INTO [OrderItem]([OrderID],
63 [Product],
64 [UnitPrice],
65 [Quantity])
66 VALUES( @OrderID,
67 @Product,
68 @UnitPrice,
69 @Quantity)";
70 SqlParameter[] parametersX = {
71 new SqlParameter("@OrderID", order.OrderID),
72 new SqlParameter("@Product", order.OrderItems[i].Product),
73 new SqlParameter("@UnitPrice", order.OrderItems[i].UnitPrice),
74 new SqlParameter("@Quantity", order.OrderItems[i].Quantity)};
75 rowsCountAffected += dbHelper.ExecuteNonQueryTrans(trans, sqlX, parametersX);
76 }
77 trans.Commit();//送出資料庫事務
78 }
79 catch
80 {
81 trans.Rollback();//復原資料庫事務
82 throw;
83 }
84 dbHelper.Close();
85
86 return rowsCountAffected;
87 }
88
89 //修改
90 public int Update(Order order)
91 {
92 int rowsCountAffected = 0;
93
94 List<SqlCmdTextAndParams> listCmdTextAndParams = new List<SqlCmdTextAndParams>();
95 //修改訂單基本資訊
96 string sql = @" UPDATE [Order]
97 SET [OrderTime] = @OrderTime,
98 [OrderStateCode] = @OrderStateCode,
99 [CustomerName] = @CustomerName,
100 [CustomerPhoneNo] = @CustomerPhoneNo,
101 [CustomerAddress] = @CustomerAddress
102 WHERE [OrderID] = @OrderID";
103 SqlParameter[] parameters = {
104 new SqlParameter("@OrderTime", order.OrderTime),
105 new SqlParameter("@OrderStateCode", order.OrderStateCode),
106 new SqlParameter("@CustomerName", order.CustomerName),
107 new SqlParameter("@CustomerPhoneNo", order.CustomerPhoneNo),
108 new SqlParameter("@CustomerAddress", order.CustomerAddress),
109 new SqlParameter("@OrderID", order.OrderID)};
110 listCmdTextAndParams.Add(new SqlCmdTextAndParams(sql, parameters));
111 //-----------------------------------------------------------
112 //循環将訂購商品資訊清單同步更新到資料庫中
113 //删除
114 string predicate = " OrderID = @OrderID ";
115 SqlParameter param = new SqlParameter("@OrderID",order.OrderID);
116 Order originalOrder = this.GetSingle(predicate, param);
117 for(int i=0;i<originalOrder.OrderItems.Count;i++)
118 {
119 bool exists = order.OrderItems.Exists(
120 delegate(OrderItem item){ return (item.OrderItemID == originalOrder.OrderItems[i].OrderItemID);});
121 if (exists) continue;
122
123 string sqlX = @"DELETE FROM [OrderItem]
124 WHERE [OrderItemID] = @OrderItemID";
125 SqlParameter[] parametersX = {
126 new SqlParameter("@OrderItemID", originalOrder.OrderItems[i].OrderItemID)};
127 listCmdTextAndParams.Add(new SqlCmdTextAndParams(sqlX, parametersX));
128 }
129 //新增/修改
130 OrderItemDAO orderItemDAO = new OrderItemDAO();
131 for (int i = 0; i < order.OrderItems.Count; i++)
132 {
133 if (0 >= order.OrderItems[i].OrderItemID )//新增
134 {
135 string sqlX = @"INSERT INTO [OrderItem]([OrderID],
136 [Product],
137 [UnitPrice],
138 [Quantity])
139 VALUES( @OrderID,
140 @Product,
141 @UnitPrice,
142 @Quantity)";
143 SqlParameter[] parametersX = {
144 new SqlParameter("@OrderID", order.OrderID),
145 new SqlParameter("@Product", order.OrderItems[i].Product),
146 new SqlParameter("@UnitPrice", order.OrderItems[i].UnitPrice),
147 new SqlParameter("@Quantity", order.OrderItems[i].Quantity)};
148 listCmdTextAndParams.Add(new SqlCmdTextAndParams(sqlX, parametersX));
149 }
150 else//修改
151 {
152 string sqlX = @"UPDATE [OrderItem]
153 SET [OrderID] = @OrderID,
154 [Product] = @Product,
155 [UnitPrice] = @UnitPrice,
156 [Quantity] = @Quantity
157 WHERE [OrderItemID] = @OrderItemID";
158 SqlParameter[] parametersX = {
159 new SqlParameter("@OrderID", order.OrderID),
160 new SqlParameter("@Product", order.OrderItems[i].Product),
161 new SqlParameter("@UnitPrice", order.OrderItems[i].UnitPrice),
162 new SqlParameter("@Quantity", order.OrderItems[i].Quantity),
163 new SqlParameter("@OrderItemID", order.OrderItems[i].OrderItemID)};
164 listCmdTextAndParams.Add(new SqlCmdTextAndParams(sqlX, parametersX));
165 }
166 }
167 rowsCountAffected = dbHelper.ExecuteNonQueryTrans(listCmdTextAndParams);
168 return rowsCountAffected;
169 }
170
171 //删除
172 public int Delete(Order order)
173 {
174 string sql = @"DELETE FROM [OrderItem]
175 WHERE [OrderID] = @OrderID
176
177 DELETE FROM [Order]
178 WHERE [OrderID] = @OrderID ";
179 return dbHelper.ExecuteNonQueryTrans(sql, new SqlParameter("@OrderID", order.OrderID));
180 }
181
182 //擷取實體對象清單
183 public List<Order> GetList(string predicate, params SqlParameter[] parameters)
184 {
185 List<Order> list = new List<Order>();
186 DataTable table = GetTable(predicate, parameters);
187 for (int i = 0; i < table.Rows.Count; i++)
188 {
189 list.Add(RowToModel(table.Rows[i]));
190 }
191 return list;
192 }
193
194 //擷取單一實體對象
195 public Order GetSingle(string predicate, params SqlParameter[] parameters)
196 {
197 List<Order> list = GetList(predicate, parameters);
198 if (list.Count == 1)
199 return list[0];
200 else if (list.Count == 0)
201 return null;
202 else
203 {
204 Exception ex = new Exception("滿足條件的實體多于1個。");
205 throw ex;
206 }
207 }
208
209 //擷取DataTable
210 private DataTable GetTable(string predicate, params SqlParameter[] parameters)
211 {
212 string sql = @"SELECT [OrderID],
213 [CustomerName],
214 [CustomerPhoneNo],
215 [CustomerAddress],
216 [OrderTime],
217 [OrderStateCode],
218 [OrderState].[Name] AS [OrderState]
219 FROM [Order]
220 LEFT OUTER JOIN [OrderState]
221 ON [Order].[OrderStateCode] = [OrderState].[Code]";
222 if (null != predicate && "" != predicate.Trim())
223 {
224 sql += " WHERE " + predicate;
225 }
226 sql += " ORDER BY [OrderID] DESC ";
227 return dbHelper.ExecuteQuery(sql, parameters);
228 }
229
230 //将DataRow轉換為實體對象
231 private Order RowToModel(DataRow row)
232 {
233 //----父表----
234 Order order = new Order();
235 order.OrderID = (int)row["OrderID"];
236 order.CustomerName = row["CustomerName"].ToString();
237 order.CustomerPhoneNo = row["CustomerPhoneNo"].ToString();
238 order.CustomerAddress = row["CustomerAddress"].ToString();
239 order.OrderTime = Convert.ToDateTime(row["OrderTime"]);
240 order.OrderStateCode = row["OrderStateCode"].ToString();
241 //----子表----
242 OrderItemDAO orderItemDAO = new OrderItemDAO();
243 order.OrderItems = orderItemDAO.GetList("OrderID = @OrderID",
244 new SqlParameter("@OrderID", order.OrderID));
245
246 return order;
247 }
248
249 }
250 }
OrderItemDAO.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.Data.SqlClient;
8 using HomeShop.DbUtility;
9 using HomeShop.Model;
10
11 namespace HomeShop.DAL
12 {
13 public class OrderItemDAO
14 {
15 private SqlDbHelper dbHelper;
16
17 public OrderItemDAO()
18 {
19 this.dbHelper = new SqlDbHelper();
20 }
21
22 public OrderItemDAO(string connectionString)
23 {
24 this.dbHelper = new SqlDbHelper(connectionString);
25 }
26
27 //擷取實體對象清單
28 public List<OrderItem> GetList(string predicate, params SqlParameter[] parameters)
29 {
30 List<OrderItem> list = new List<OrderItem>();
31 DataTable dataTable = GetTable(predicate, parameters);
32 for (int i = 0; i < dataTable.Rows.Count; i++)
33 {
34 list.Add(RowToModel(dataTable.Rows[i]));
35 }
36
37 return list;
38 }
39
40 //擷取DataTable
41 private DataTable GetTable(string predicate, params SqlParameter[] parameters)
42 {
43 string sql = @"SELECT [OrderItemID],
44 [OrderID],
45 [Product],
46 [UnitPrice],
47 [Quantity],
48 [UnitPrice]*[Quantity] AS SubTotal
49 FROM [OrderItem]";
50 if (null != predicate && "" != predicate.Trim())
51 {
52 sql += " WHERE " + predicate;
53 }
54 return dbHelper.ExecuteQuery(sql, parameters);
55 }
56
57 //将DataRow轉換為實體對象
58 private OrderItem RowToModel(DataRow row)
59 {
60 OrderItem orderItem = new OrderItem();
61 orderItem.OrderID = (int)row["OrderID"];
62 orderItem.OrderItemID = (int)row["OrderItemID"];
63 orderItem.Product = row["Product"].ToString();
64 orderItem.Quantity = (int)row["Quantity"];
65 orderItem.UnitPrice = (decimal)row["UnitPrice"];
66
67 return orderItem;
68 }
69 }
70 }
OrderStateDAO.cs
13 public class OrderStateDAO
17 public OrderStateDAO()
22 public OrderStateDAO(string connectionString)
28 public List<OrderState> GetList(string predicate, params SqlParameter[] parameters)
30 List<OrderState> list = new List<OrderState>();
43 string sql = @"SELECT * FROM [OrderState]";
44 if (null != predicate && "" != predicate.Trim())
45 {
46 sql += " WHERE " + predicate;
47 }
48 sql += " ORDER BY [Code] ";
49 return dbHelper.ExecuteQuery(sql, parameters);
50 }
51
52 //将DataRow轉換為實體對象
53 private OrderState RowToModel(DataRow row)
54 {
55 OrderState orderState = new OrderState();
56 orderState.Code = row["Code"].ToString();
57 orderState.Name = row["Name"].ToString();
58 return orderState;
59 }
60 }
61 }
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)