.NET数据库编程求索之路4.使用ADO.NET实现（三层架构篇使用Table传递数据）（3）

4.使用ADO.NET实现（三层架构篇-使用Table传递数据）（3）

作者：夏春涛 xchunta@163.com

转载请注明来源： http://www.cnblogs.com/SummerRain/archive/2012/07/25/2609144.html

4.4 数据访问层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             Order originalOrder = this.GetSingle(order.OrderID);
115             for(int i=0;i<originalOrder.OrderItems.Count;i++)
116             {
117                 bool exists = order.OrderItems.Exists(
118                     delegate(OrderItem item){ return (item.OrderItemID == originalOrder.OrderItems[i].OrderItemID);});
119                 if (exists) continue;
120                 
121                 string sqlX = @"DELETE FROM [OrderItem]
122                                 WHERE [OrderItemID] = @OrderItemID";
123                 SqlParameter[] parametersX = {
124                                 new SqlParameter("@OrderItemID", originalOrder.OrderItems[i].OrderItemID)};
125                 listCmdTextAndParams.Add(new SqlCmdTextAndParams(sqlX, parametersX));                
126             }
127             //新增/修改
128             OrderItemDAO orderItemDAO = new OrderItemDAO();
129             for (int i = 0; i < order.OrderItems.Count; i++)
130             {
131                 if (0 >= order.OrderItems[i].OrderItemID )//新增
132                 {
133                     string sqlX = @"INSERT INTO [OrderItem]([OrderID],
134                                                             [Product],
135                                                             [UnitPrice],
136                                                             [Quantity])
137                                     VALUES( @OrderID,
138                                             @Product,
139                                             @UnitPrice,
140                                             @Quantity)";
141                     SqlParameter[] parametersX = {
142                                     new SqlParameter("@OrderID", order.OrderID),
143                                     new SqlParameter("@Product", order.OrderItems[i].Product),
144                                     new SqlParameter("@UnitPrice", order.OrderItems[i].UnitPrice),
145                                     new SqlParameter("@Quantity", order.OrderItems[i].Quantity)};
146                     listCmdTextAndParams.Add(new SqlCmdTextAndParams(sqlX, parametersX));
147                 }
148                 else//修改
149                 {
150                     string sqlX = @"UPDATE [OrderItem]
151                                                 SET [OrderID] = @OrderID,
152                                                     [Product] = @Product,
153                                                     [UnitPrice] = @UnitPrice,
154                                                     [Quantity] = @Quantity
155                                                 WHERE [OrderItemID] = @OrderItemID";
156                     SqlParameter[] parametersX = {
157                                     new SqlParameter("@OrderID", order.OrderID),
158                                     new SqlParameter("@Product", order.OrderItems[i].Product),
159                                     new SqlParameter("@UnitPrice", order.OrderItems[i].UnitPrice),
160                                     new SqlParameter("@Quantity", order.OrderItems[i].Quantity),
161                                     new SqlParameter("@OrderItemID", order.OrderItems[i].OrderItemID)};
162                     listCmdTextAndParams.Add(new SqlCmdTextAndParams(sqlX, parametersX));
163                 }
164             }
165             rowsCountAffected = dbHelper.ExecuteNonQueryTrans(listCmdTextAndParams);
166             return rowsCountAffected;
167         }
168         
169         //删除
170         public int Delete(int orderID)
171         {
172             string sql = @"DELETE FROM [OrderItem]
173                            WHERE [OrderID] = @OrderID
174                            
175                            DELETE FROM [Order]
176                            WHERE [OrderID] = @OrderID ";
177             return dbHelper.ExecuteNonQueryTrans(sql, new SqlParameter("@OrderID", orderID));
178         }
179 
180         //获取实体对象
181         public Order GetSingle(int orderID)
182         {
183             string sql = @"SELECT * FROM [Order]
184                            WHERE [OrderID] = @OrderID";
185             SqlParameter param = new SqlParameter("@OrderID", orderID);
186             DataTable dataTable = dbHelper.ExecuteQuery(sql, param);
187 
188             return RowToModel(dataTable.Rows[0]);
189         }
190 
191         //获取DataTable
192         public DataTable GetTable(string predicate, params SqlParameter[] parameters)
193         {
194             string sql = @"SELECT [OrderID],
195                                   [CustomerName],
196                                   [CustomerPhoneNo],
197                                   [CustomerAddress],
198                                   [OrderTime],
199                                   [OrderStateCode],
200                                   [OrderState].[Name] AS [OrderState]
201                             FROM [Order]
202                             LEFT OUTER JOIN [OrderState]
203                             ON [Order].[OrderStateCode] = [OrderState].[Code]";
204             if (null != predicate && "" != predicate.Trim())
205             {
206                 sql += "  WHERE  " + predicate;
207             }
208             sql += " ORDER BY [OrderID] DESC ";
209             return dbHelper.ExecuteQuery(sql, parameters);
210         }
211 
212         //获取DataTable
213         public DataTable GetTable()
214         {
215             return GetTable(null, null);
216         }
217 
218         //将DataRow转换为实体对象
219         private Order RowToModel(DataRow row)
220         {
221             //----父表
222             Order order = new Order();
223             order.OrderID = (int)row["OrderID"];
224             order.CustomerName = row["CustomerName"].ToString();
225             order.CustomerPhoneNo = row["CustomerPhoneNo"].ToString();
226             order.CustomerAddress = row["CustomerAddress"].ToString();
227             order.OrderTime = Convert.ToDateTime(row["OrderTime"]);
228             order.OrderStateCode = row["OrderStateCode"].ToString();
229             //----子表----
230             OrderItemDAO orderItemDAO = new OrderItemDAO();
231             order.OrderItems = orderItemDAO.GetList("OrderID = @OrderID",
232                                                     new SqlParameter("@OrderID", order.OrderID));
233             
234             return order;
235         }
236 
237     }
238 }

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         public bool Exists(int orderItemID)
28         {
29             DataTable dataTable = GetTable("OrderItemID = @OrderItemID", new SqlParameter("@OrderItemID", orderItemID));
30             return (0 != dataTable.Rows.Count);
31         }
32 
33         //获取实体对象列表
34         public List<OrderItem> GetList(string predicate, params SqlParameter[] parameters)
35         {
36             List<OrderItem> list = new List<OrderItem>();
37             DataTable dataTable = GetTable(predicate, parameters);
38             for (int i = 0; i < dataTable.Rows.Count; i++)
39             {
40                 list.Add(RowToModel(dataTable.Rows[i]));
41             }
42 
43             return list;
44         }
45 
46         //获取DataTable
47         public DataTable GetTable(string predicate, params SqlParameter[] parameters)
48         {
49             string sql = @"SELECT [OrderItemID],
50                                   [OrderID],
51                                   [Product],
52                                   [UnitPrice],
53                                   [Quantity],
54                                   [UnitPrice]*[Quantity] AS SubTotal
55                            FROM [OrderItem]";
56             if (null != predicate && "" != predicate.Trim())
57             {
58                 sql += "  WHERE  " + predicate;
59             }
60             return dbHelper.ExecuteQuery(sql, parameters);
61         }
62 
63         //获取DataTable
64         public DataTable GetTable()
65         {
66             return GetTable(null, null);
67         }
68 
69         //将DataRow转换为实体对象
70         private OrderItem RowToModel(DataRow row)
71         {
72             OrderItem orderItem = new OrderItem();
73             orderItem.OrderID = (int)row["OrderID"];
74             orderItem.OrderItemID = (int)row["OrderItemID"];
75             orderItem.Product = row["Product"].ToString();
76             orderItem.Quantity = (int)row["Quantity"];
77             orderItem.UnitPrice = (decimal)row["UnitPrice"];
78 
79             return orderItem;
80         }
81     }
82 }

OrderStateDAO.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 OrderStateDAO
14     {
15         private SqlDbHelper dbHelper;
16 
17         public OrderStateDAO()
18         { 
19             this.dbHelper  = new SqlDbHelper();
20         }
21 
22         public OrderStateDAO(string connectionString)
23         {
24             this.dbHelper = new SqlDbHelper(connectionString);
25         }
26 
27         //获取DataTable
28         public DataTable GetTable(string predicate, params SqlParameter[] parameters)
29         {
30             string sql = @"SELECT * FROM [OrderState]";
31             if (null != predicate && "" != predicate.Trim())
32             {
33                 sql += "  WHERE  " + predicate;
34             }
35             sql += " ORDER BY [Code] ";
36             return dbHelper.ExecuteQuery(sql, parameters);
37         }
38 
39         //获取DataTable
40         public DataTable GetTable()
41         {
42             return GetTable(null, null);
43         }
44     }
45 }

 

数据库文件：/Files/SummerRain/NetDbDevRoad/HomeShopDB.rar

完整源代码：/Files/SummerRain/NetDbDevRoad/4使用ADONET实现三层架构Table.rar