.NET資料庫程式設計求索之路--6.使用ADO.NET實作（三層架構篇-使用List傳遞資料-基于存儲過程）（1）

6.使用ADO.NET實作（三層架構篇-使用List傳遞資料-基于存儲過程）（1）

6.1 解決方案架構

解決方案(.sln)包含以下幾個項目：

（1）類庫項目HomeShop.DbUtility，資料通路實用工具；【同5】

（2）類庫項目HomeShop.Model，實體層；【同5】

（3）類庫項目HomeShop.DAL，資料通路層；

（4）類庫項目HomeShop.BLL，業務邏輯層；【同5】

（5）WinForm項目HomeShop.WinForm，界面層。【同5】

6.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         #region 添加

 23         public int Add(Order order)

 24         {

 25             int rowsCountAffected = 0;

 26             SqlTransaction trans = dbHelper.BeginTransaction();

 27             try

 28             {

 29                 //新增訂單基本資訊---------------------------------------------                

 30                 //@OrderID作為傳出參數，用于擷取新增訂單的ID

 31                 SqlParameter paramOrderID = new SqlParameter("@OrderID", SqlDbType.Int);

 32                 paramOrderID.Direction = ParameterDirection.Output;

 33                 SqlParameter[] parameters = {

 34                     paramOrderID,

 35                     new SqlParameter("@OrderTime", order.OrderTime),

 36                     new SqlParameter("@OrderStateCode", order.OrderStateCode),

 37                     new SqlParameter("@CustomerName", order.CustomerName),

 38                     new SqlParameter("@CustomerPhoneNo", order.CustomerPhoneNo),

 39                     new SqlParameter("@CustomerAddress", order.CustomerAddress)};

 40                 rowsCountAffected += dbHelper.ExecuteNonQueryTrans(trans, "Order_Insert", CommandType.StoredProcedure, parameters);

 41                 order.OrderID = (int)paramOrderID.Value;

 42                 //-----------------------------------------------------------

 43                 //循環添加訂購商品資訊

 44                 for (int i = 0; i < order.OrderItemList.Count; i++)

 45                 {

 46                     SqlParameter paramOrderItemID = new SqlParameter("@OrderItemID", SqlDbType.Int);

 47                     paramOrderItemID.Direction = ParameterDirection.Output;

 48                     SqlParameter[] parametersX = {

 49                         paramOrderItemID,

 50                         new SqlParameter("@OrderID", order.OrderID),

 51                         new SqlParameter("@Product", order.OrderItemList[i].Product),

 52                         new SqlParameter("@UnitPrice", order.OrderItemList[i].UnitPrice),

 53                         new SqlParameter("@Quantity", order.OrderItemList[i].Quantity)};

 54                     rowsCountAffected += dbHelper.ExecuteNonQueryTrans(trans, "OrderItem_Insert", CommandType.StoredProcedure, parametersX);

 55                 }

 56                 trans.Commit();//送出資料庫事務

 57                 dbHelper.Close();

 58             }

 59             catch

 60             {

 61                 trans.Rollback();//復原資料庫事務

 62                 dbHelper.Close();

 63                 throw;

 64             }            

 65 

 66             return rowsCountAffected;

 67         }

 68         #endregion

 69 

 70         #region 修改

 71         public int Update(Order order)

 72         {

 73             int rowsCountAffected = 0;        

 74 

 75             List<SqlCmdTextAndParams> listCmdTextAndParams = new List<SqlCmdTextAndParams>();

 76             //修改訂單基本資訊

 77             SqlParameter[] parameters = {

 78                         new SqlParameter("@OrderTime", order.OrderTime),

 79                         new SqlParameter("@OrderStateCode", order.OrderStateCode),

 80                         new SqlParameter("@CustomerName", order.CustomerName),

 81                         new SqlParameter("@CustomerPhoneNo", order.CustomerPhoneNo),

 82                         new SqlParameter("@CustomerAddress", order.CustomerAddress),

 83                         new SqlParameter("@OrderID", order.OrderID)};

 84             listCmdTextAndParams.Add(new SqlCmdTextAndParams("Order_Update", CommandType.StoredProcedure, parameters));

 85             //-----------------------------------------------------------

 86             //循環将訂購商品資訊清單同步更新到資料庫中

 87             //删除

 88             Order originalOrder = this.GetSingle(order.OrderID);

 89             for (int i = 0; i < originalOrder.OrderItemList.Count; i++)

 90             {

 91                 bool exists = order.OrderItemList.Exists(

 92                     delegate(OrderItem item) { return (item.OrderItemID == originalOrder.OrderItemList[i].OrderItemID); });

 93                 if (exists) continue;

 94 

 95                 SqlParameter[] parametersX = {

 96                                 new SqlParameter("@OrderItemID", originalOrder.OrderItemList[i].OrderItemID)};

 97                 listCmdTextAndParams.Add(new SqlCmdTextAndParams("OrderItem_Delete", CommandType.StoredProcedure, parametersX));                

 98             }

 99             //新增/修改

100             OrderItemDAO orderItemDAO = new OrderItemDAO();

101             for (int i = 0; i < order.OrderItemList.Count; i++)

102             {

103                 if (0 >= order.OrderItemList[i].OrderItemID)//新增

104                 {

105                     SqlParameter paramOrderItemID = new SqlParameter("@OrderItemID", SqlDbType.Int);

106                     paramOrderItemID.Direction = ParameterDirection.Output;

107                     SqlParameter[] parametersX = {

108                         paramOrderItemID,

109                         new SqlParameter("@OrderID", order.OrderID),

110                         new SqlParameter("@Product", order.OrderItemList[i].Product),

111                         new SqlParameter("@UnitPrice", order.OrderItemList[i].UnitPrice),

112                         new SqlParameter("@Quantity", order.OrderItemList[i].Quantity)};

113                     listCmdTextAndParams.Add(new SqlCmdTextAndParams("OrderItem_Insert", CommandType.StoredProcedure, parametersX));

114                 }

115                 else//修改

116                 {

117                     SqlParameter[] parametersX = {

118                         new SqlParameter("@OrderItemID", order.OrderItemList[i].OrderItemID),

119                         new SqlParameter("@OrderID", order.OrderID),

120                         new SqlParameter("@Product", order.OrderItemList[i].Product),

121                         new SqlParameter("@UnitPrice", order.OrderItemList[i].UnitPrice),

122                         new SqlParameter("@Quantity", order.OrderItemList[i].Quantity)};                                    

123                     listCmdTextAndParams.Add(new SqlCmdTextAndParams("OrderItem_Update", CommandType.StoredProcedure, parametersX));

124                 }

125             }

126             rowsCountAffected = dbHelper.ExecuteNonQueryTrans(listCmdTextAndParams);

127             return rowsCountAffected;

128         }

129         #endregion

130 

131         //删除

132         public void Delete(int orderID)

133         {

134             SqlParameter parameter = new SqlParameter("@OrderID",orderID);

135             dbHelper.ExecuteNonQuery("Order_Delete", CommandType.StoredProcedure, parameter);

136         }

137 

138         //擷取實體對象清單

139         public List<Order> GetList()

140         {

141             string predicate = "";

142             DataTable table = GetTable(predicate);

143             return TableToList(table);

144         }

145         

146         //擷取實體對象清單

147         public List<Order> GetList(string customerName)

148         {            

149             string predicate = " [CustomerName] LIKE '%" + customerName + "%'";

150             DataTable table = GetTable(predicate);

151             return TableToList(table);

152         }

153 

154         //擷取單一實體對象

155         public Order GetSingle(int orderID)

156         {

157             string predicate = " [OrderID] = " + orderID.ToString();

158             DataTable table = GetTable(predicate);

159             List<Order> list = TableToList(table);

160             if (list.Count == 1)

161             {

162                 //return list[0];

163                 Order order = list[0];

164                 //----子表----

165                 OrderItemDAO orderItemDAO = new OrderItemDAO();

166                 order.OrderItemList = orderItemDAO.GetList(order.OrderID);

167                 return order;

168             }

169             else if (list.Count == 0)

170                 return null;

171             else

172             {

173                 Exception ex = new Exception("滿足條件的實體多于1個。");

174                 throw ex;

175             }

176         }

177 

178         //将DataTable轉換為List

179         private List<Order> TableToList(DataTable table)

180         {

181             List<Order> list = new List<Order>();

182             for (int i = 0; i < table.Rows.Count; i++)

183             {

184                 list.Add(RowToModel(table.Rows[i]));

185             }

186             return list;

187         }

188 

189         //擷取DataTable

190         private DataTable GetTable(string predicate)

191         {

192             SqlParameter parameter = new SqlParameter("@predicate", predicate);

193             return dbHelper.ExecuteQuery("Order_Select", CommandType.StoredProcedure, parameter);

194         }

195         

196         //将DataRow轉換為實體對象

197         private Order RowToModel(DataRow row)

198         {

199             //----父表----

200             Order order = new Order();

201             order.OrderID = (int)row["OrderID"];

202             order.CustomerName = row["CustomerName"].ToString();

203             order.CustomerPhoneNo = row["CustomerPhoneNo"].ToString();

204             order.CustomerAddress = row["CustomerAddress"].ToString();

205             order.OrderTime = Convert.ToDateTime(row["OrderTime"]);

206             order.OrderStateCode = row["OrderStateCode"].ToString();

207             order.OrderStateName = row["OrderStateName"].ToString();

208             //----子表----

209             //OrderItemDAO orderItemDAO = new OrderItemDAO();

210             //order.OrderItemList = orderItemDAO.GetList(order.OrderID);

211             

212             return order;

213         }

214 

215     }

216 }

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         //擷取實體對象清單

23         public List<OrderItem> GetList(int orderID)

24         {

25             DataTable table = GetTable(orderID);

26             return TableToList(table);

27         }

28 

29         //将DataTable轉換為List

30         private List<OrderItem> TableToList(DataTable table)

31         {

32             List<OrderItem> list = new List<OrderItem>();

33             for (int i = 0; i < table.Rows.Count; i++)

34             {

35                 list.Add(RowToModel(table.Rows[i]));

36             }

37             return list;

38         }

39 

40         //擷取DataTable

41         private DataTable GetTable(int orderID)

42         {

43             SqlParameter parameter = new SqlParameter("@OrderID", orderID);

44             return dbHelper.ExecuteQuery("OrderItem_Select", CommandType.StoredProcedure, parameter);

45         }

46 

47         //将DataRow轉換為實體對象

48         private OrderItem RowToModel(DataRow row)

49         {

50             OrderItem orderItem = new OrderItem();

51             orderItem.OrderID = (int)row["OrderID"];

52             orderItem.OrderItemID = (int)row["OrderItemID"];

53             orderItem.Product = row["Product"].ToString();

54             orderItem.Quantity = (int)row["Quantity"];

55             orderItem.UnitPrice = (decimal)row["UnitPrice"];

56 

57             return orderItem;

58         }

59     }

60 }

OrderStateDAO.cs

13     public class OrderStateDAO

17         public OrderStateDAO()

23         public List<OrderState> GetList()

25             DataTable table = GetTable();

30         private List<OrderState> TableToList(DataTable table)

32             List<OrderState> list = new List<OrderState>();

41         private DataTable GetTable()

43             return dbHelper.ExecuteQuery("OrderState_Select", CommandType.StoredProcedure, null);

44         }

45 

46         //将DataRow轉換為實體對象

47         private OrderState RowToModel(DataRow row)

48         {

49             OrderState orderState = new OrderState();

50             orderState.Code = row["Code"].ToString();

51             orderState.Name = row["Name"].ToString();

52             return orderState;

53         }

54     }

55 }