7.使用ADO.NET實作（工廠模式-實作多資料庫切換）（3）
7.5 SQL資料通路層HomeShop.DALOfSql
OrderDAO.cs
View Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//新添命名空間
using System.Data;
using System.Data.SqlClient;
using HomeShop.DbUtility;
using HomeShop.Model;
using HomeShop.DALInterface;
namespace HomeShop.DALOfSql
{
public class OrderDAO : IOrderDAO
{
private SqlDbHelper dbHelper = new SqlDbHelper();
//添加
public int Add(Order order)
{
int rowsCountAffected = 0;
SqlTransaction trans = dbHelper.BeginTransaction();
try
{
//新增訂單基本資訊
string sql = @"INSERT INTO [Order]([OrderTime],
[OrderStateCode],
[CustomerName],
[CustomerPhoneNo],
[CustomerAddress])
VALUES(@OrderTime,
@OrderStateCode,
@CustomerName,
@CustomerPhoneNo,
@CustomerAddress)";
SqlParameter[] parameters = {
new SqlParameter("@OrderTime", SqlDbType.DateTime),
new SqlParameter("@OrderStateCode", SqlDbType.Char, 1),
new SqlParameter("@CustomerName", SqlDbType.VarChar, 30),
new SqlParameter("@CustomerPhoneNo", SqlDbType.VarChar, 15),
new SqlParameter("@CustomerAddress", SqlDbType.VarChar, 60)};
parameters[0].Value = order.OrderTime;
parameters[1].Value = order.OrderStateCode;
parameters[2].Value = order.CustomerName;
parameters[3].Value = order.CustomerPhoneNo;
parameters[4].Value = order.CustomerAddress;
rowsCountAffected += dbHelper.ExecuteNonQuery(trans, sql, parameters);
//擷取新增訂單的ID
order.OrderID = Convert.ToInt32(dbHelper.ExecuteScalar(trans, "SELECT @@IDENTITY"));
//-----------------------------------------------------------
//循環添加訂購商品資訊
for (int i = 0; i < order.OrderItems.Count; i++)
{
string sqlX = @"INSERT INTO [OrderItem]([OrderID],
[Product],
[UnitPrice],
[Quantity])
VALUES( @OrderID,
@Product,
@UnitPrice,
@Quantity)";
SqlParameter[] parametersX = {
new SqlParameter("@OrderID", SqlDbType.Int, 4),
new SqlParameter("@Product", SqlDbType.VarChar, 30),
new SqlParameter("@UnitPrice", SqlDbType.Decimal, 9),
new SqlParameter("@Quantity", SqlDbType.Int, 4)};
parametersX[0].Value = order.OrderID;
parametersX[1].Value = order.OrderItems[i].Product;
parametersX[2].Value = order.OrderItems[i].UnitPrice;
parametersX[3].Value = order.OrderItems[i].Quantity;
rowsCountAffected += dbHelper.ExecuteNonQuery(trans, sqlX, parametersX);
}
trans.Commit();//送出資料庫事務
}
catch
trans.Rollback();//復原資料庫事務
throw;
dbHelper.Close();
return rowsCountAffected;
}
//修改
public int Update(Order order)
int rowsCountAffected = 0;
List<SqlCmdTextAndParams> listCmdTextAndParams = new List<SqlCmdTextAndParams>();
//修改訂單基本資訊
string sql = @" UPDATE [Order]
SET [OrderTime] = @OrderTime,
[OrderStateCode] = @OrderStateCode,
[CustomerName] = @CustomerName,
[CustomerPhoneNo] = @CustomerPhoneNo,
[CustomerAddress] = @CustomerAddress
WHERE [OrderID] = @OrderID";
SqlParameter[] parameters = {
new SqlParameter("@CustomerAddress", SqlDbType.VarChar, 60),
new SqlParameter("@OrderID", SqlDbType.Int,4)};
parameters[0].Value = order.OrderTime;
parameters[1].Value = order.OrderStateCode;
parameters[2].Value = order.CustomerName;
parameters[3].Value = order.CustomerPhoneNo;
parameters[4].Value = order.CustomerAddress;
parameters[5].Value = order.OrderID;
listCmdTextAndParams.Add(new SqlCmdTextAndParams(sql, parameters));
//-----------------------------------------------------------
//循環将訂購商品資訊清單同步更新到資料庫中
//删除
string predicate = " OrderID = @OrderID ";
SqlParameter parameter = new SqlParameter("@OrderID",SqlDbType.Int, 4);
parameter.Value = order.OrderID;
Order originalOrder = this.GetSingle(predicate, parameter);
for(int i=0;i<originalOrder.OrderItems.Count;i++)
bool exists = order.OrderItems.Exists(
delegate(OrderItem item){ return (item.OrderItemID == originalOrder.OrderItems[i].OrderItemID);});
if (exists) continue;
string sqlX = @"DELETE FROM [OrderItem]
WHERE [OrderItemID] = @OrderItemID";
SqlParameter parameterX = new SqlParameter("@OrderItemID", SqlDbType.Int, 4);
parameterX.Value = originalOrder.OrderItems[i].OrderItemID;
listCmdTextAndParams.Add(new SqlCmdTextAndParams(sqlX, parameterX));
//新增/修改
OrderItemDAO orderItemDAO = new OrderItemDAO();
for (int i = 0; i < order.OrderItems.Count; i++)
if (0 >= order.OrderItems[i].OrderItemID )//新增
[Product],
[UnitPrice],
[Quantity])
new SqlParameter("@OrderID", SqlDbType.Int,4),
new SqlParameter("@Product", SqlDbType.VarChar,30),
new SqlParameter("@UnitPrice", SqlDbType.Decimal,9),
new SqlParameter("@Quantity", SqlDbType.Int,4)};
parametersX[0].Value = order.OrderItems[i].OrderID;
listCmdTextAndParams.Add(new SqlCmdTextAndParams(sqlX, parametersX));
else//修改
string sqlX = @"UPDATE [OrderItem]
SET [OrderID] = @OrderID,
[Product] = @Product,
[UnitPrice] = @UnitPrice,
[Quantity] = @Quantity
WHERE [OrderItemID] = @OrderItemID";
new SqlParameter("@Quantity", SqlDbType.Int,4),
new SqlParameter("@OrderItemID", SqlDbType.Int,4)};
parametersX[4].Value = order.OrderItems[i].OrderItemID;
rowsCountAffected = dbHelper.ExecuteNonQueryTrans(listCmdTextAndParams);
//删除
public int Delete(int orderID)
string sql = @"DELETE FROM [OrderItem]
WHERE [OrderID] = @OrderID";
SqlParameter parameter = new SqlParameter("@OrderID", SqlDbType.Int, 4);
parameter.Value = orderID;
listCmdTextAndParams.Add(new SqlCmdTextAndParams(sql, parameter));
sql = @" DELETE FROM [Order]
WHERE [OrderID] = @OrderID ";
return dbHelper.ExecuteNonQueryTrans(listCmdTextAndParams);
//擷取實體對象清單
public List<Order> GetList()
return GetList(null, null);
public List<Order> GetList(string customerName)
string predicate = " CustomerName LIKE '%' + @CustomerName + '%'";
SqlParameter parameter = new SqlParameter("@CustomerName", SqlDbType.VarChar, 30);
parameter.Value = customerName;
return GetList(predicate, parameter);
public List<Order> GetList(string predicate, params SqlParameter[] parameters)
List<Order> list = new List<Order>();
DataTable table = GetTable(predicate, parameters);
for (int i = 0; i < table.Rows.Count; i++)
list.Add(RowToModel(table.Rows[i]));
return list;
//擷取單一實體對象
public Order GetSingle(int orderID)
return GetSingle(predicate,parameter);
private Order GetSingle(string predicate, params SqlParameter[] parameters)
List<Order> list = GetList(predicate, parameters);
if (list.Count == 1)
return list[0];
else if (list.Count == 0)
return null;
else
Exception ex = new Exception("滿足條件的實體多于1個。");
throw ex;
//擷取DataTable
private DataTable GetTable(string predicate, params SqlParameter[] parameters)
string sql = @"SELECT [OrderID],
[CustomerName],
[CustomerPhoneNo],
[CustomerAddress],
[OrderTime],
[OrderStateCode],
[OrderState].[Name] AS [OrderState]
FROM [Order]
LEFT OUTER JOIN [OrderState]
ON [Order].[OrderStateCode] = [OrderState].[Code]";
if (null != predicate && "" != predicate.Trim())
sql += " WHERE " + predicate;
sql += " ORDER BY [OrderID] DESC ";
return dbHelper.ExecuteQuery(sql, parameters);
//将DataRow轉換為實體對象
private Order RowToModel(DataRow row)
//----父表----
Order order = new Order();
order.OrderID = (int)row["OrderID"];
order.CustomerName = row["CustomerName"].ToString();
order.CustomerPhoneNo = row["CustomerPhoneNo"].ToString();
order.CustomerAddress = row["CustomerAddress"].ToString();
order.OrderTime = Convert.ToDateTime(row["OrderTime"]);
order.OrderStateCode = row["OrderStateCode"].ToString();
//----子表----
order.OrderItems = orderItemDAO.GetList(order.OrderID);
return order;
}
}
OrderItemDAO.cs
public class OrderItemDAO : IOrderItemDAO
{
public List<OrderItem> GetList(int orderID)
private List<OrderItem> GetList(string predicate, params SqlParameter[] parameters)
List<OrderItem> list = new List<OrderItem>();
DataTable dataTable = GetTable(predicate, parameters);
for (int i = 0; i < dataTable.Rows.Count; i++)
list.Add(RowToModel(dataTable.Rows[i]));
string sql = @"SELECT [OrderItemID],
[OrderID],
[Product],
[UnitPrice],
[Quantity],
[UnitPrice]*[Quantity] AS SubTotal
FROM [OrderItem]";
private OrderItem RowToModel(DataRow row)
OrderItem orderItem = new OrderItem();
orderItem.OrderID = (int)row["OrderID"];
orderItem.OrderItemID = (int)row["OrderItemID"];
orderItem.Product = row["Product"].ToString();
orderItem.Quantity = (int)row["Quantity"];
orderItem.UnitPrice = (decimal)row["UnitPrice"];
return orderItem;
OrderStateDAO.cs
public class OrderStateDAO : IOrderStateDAO
public List<OrderState> GetList()
public List<OrderState> GetList(string predicate, params SqlParameter[] parameters)
List<OrderState> list = new List<OrderState>();
string sql = @"SELECT * FROM [OrderState]";
sql += " ORDER BY [Code] ";
private OrderState RowToModel(DataRow row)
OrderState orderState = new OrderState();
orderState.Code = row["Code"].ToString();
orderState.Name = row["Name"].ToString();
return orderState;
7.6 Access資料通路層HomeShop.DALOfAccess
using System.Data.OleDb;
namespace HomeShop.DALOfAccess
private OleDbHelper dbHelper = new OleDbHelper();
OleDbTransaction trans = dbHelper.BeginTransaction();
OleDbParameter[] parameters = {
new OleDbParameter("@OrderTime", OleDbType.Date),
new OleDbParameter("@OrderStateCode", OleDbType.Char, 1),
new OleDbParameter("@CustomerName", OleDbType.VarChar, 30),
new OleDbParameter("@CustomerPhoneNo", OleDbType.VarChar, 15),
new OleDbParameter("@CustomerAddress", OleDbType.VarChar, 60)};
rowsCountAffected += dbHelper.ExecuteNonQuery(trans, sql, parameters);
OleDbParameter[] parametersX = {
new OleDbParameter("@OrderID", OleDbType.Integer, 4),
new OleDbParameter("@Product", OleDbType.VarChar, 30),
new OleDbParameter("@UnitPrice", OleDbType.Decimal, 9),
new OleDbParameter("@Quantity", OleDbType.Integer, 4)};
List<OleDbCmdTextAndParams> listCmdTextAndParams = new List<OleDbCmdTextAndParams>();
OleDbParameter[] parameters = {
new OleDbParameter("@CustomerAddress", OleDbType.VarChar, 60),
new OleDbParameter("@OrderID", OleDbType.Integer,4)};
listCmdTextAndParams.Add(new OleDbCmdTextAndParams(sql, parameters));
OleDbParameter parameter = new OleDbParameter("@OrderID", OleDbType.Integer, 4);
for (int i = 0; i < originalOrder.OrderItems.Count; i++)
delegate(OrderItem item) { return (item.OrderItemID == originalOrder.OrderItems[i].OrderItemID); });
OleDbParameter parameterX = new OleDbParameter("@OrderItemID", OleDbType.Integer, 4);
listCmdTextAndParams.Add(new OleDbCmdTextAndParams(sqlX, parameterX));
if (0 >= order.OrderItems[i].OrderItemID)//新增
listCmdTextAndParams.Add(new OleDbCmdTextAndParams(sqlX, parametersX));
new OleDbParameter("@Quantity", OleDbType.Integer, 4),
new OleDbParameter("@OrderItemID", OleDbType.Integer, 4)};
OleDbParameter parameter = new OleDbParameter("@OrderID", orderID);
listCmdTextAndParams.Add(new OleDbCmdTextAndParams(sql, parameter));
OleDbParameter parameter = new OleDbParameter("@CustomerName", OleDbType.VarChar, 30);
public List<Order> GetList(string predicate, params OleDbParameter[] parameters)
return GetSingle(predicate, parameter);
private Order GetSingle(string predicate, params OleDbParameter[] parameters)
private DataTable GetTable(string predicate, params OleDbParameter[] parameters)
private List<OrderItem> GetList(string predicate, params OleDbParameter[] parameters)
public List<OrderState> GetList(string predicate, params OleDbParameter[] parameters)