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

  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 }

 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 }

 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 }