.NET数据库编程求索之路--4.使用ADO.NET实现（三层架构篇-使用Table传递数据）（3）
Posted on 2012-07-25 22:59 SummerRain 阅读(348) 评论(0) 编辑 收藏 举报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 }
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 }
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 }
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
