.NET数据库编程求索之路--7.使用ADO.NET实现（工厂模式-实现多数据库切换）（3）

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( " @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),
                     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 ) // 新增
                {
                     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.OrderItems[i].OrderID;
                    parametersX[ 1].Value = order.OrderItems[i].Product;
                    parametersX[ 2].Value = order.OrderItems[i].UnitPrice;
                    parametersX[ 3].Value = order.OrderItems[i].Quantity;
                    listCmdTextAndParams.Add( new SqlCmdTextAndParams(sqlX, parametersX));
                }
                 else // 修改
                {
                     string sqlX =  @" UPDATE [OrderItem]
                                                SET [OrderID] = @OrderID,
                                                    [Product] = @Product,
                                                    [UnitPrice] = @UnitPrice,
                                                    [Quantity] = @Quantity
                                                WHERE [OrderItemID] = @OrderItemID ";
                    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),
                         new SqlParameter( " @OrderItemID ", SqlDbType.Int, 4)};
                    parametersX[ 0].Value = order.OrderItems[i].OrderID;
                    parametersX[ 1].Value = order.OrderItems[i].Product;
                    parametersX[ 2].Value = order.OrderItems[i].UnitPrice;
                    parametersX[ 3].Value = order.OrderItems[i].Quantity;
                    parametersX[ 4].Value = order.OrderItems[i].OrderItemID;
                    listCmdTextAndParams.Add( new SqlCmdTextAndParams(sqlX, parametersX));
                }
            }

            rowsCountAffected = dbHelper.ExecuteNonQueryTrans(listCmdTextAndParams);
             return rowsCountAffected;
        }
        
         // 删除
         public  int Delete( int orderID)
        {
            List<SqlCmdTextAndParams> listCmdTextAndParams =  new List<SqlCmdTextAndParams>();

             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  ";
            listCmdTextAndParams.Add( new SqlCmdTextAndParams(sql, parameter));

             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)
        {
             string predicate =  "  OrderID = @OrderID  ";
            SqlParameter parameter =  new SqlParameter( " @OrderID ",SqlDbType.Int,  4);
            parameter.Value = 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();
             // ----子表----
            OrderItemDAO orderItemDAO =  new OrderItemDAO();
            order.OrderItems = orderItemDAO.GetList(order.OrderID);
            
             return order;
        }

    }
}

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 OrderItemDAO : IOrderItemDAO
    {
         private SqlDbHelper dbHelper =  new SqlDbHelper();

         // 获取实体对象列表
         public List<OrderItem> GetList( int orderID)
        {
             string predicate =  "  OrderID = @OrderID  ";
            SqlParameter parameter =  new SqlParameter( " @OrderID ", SqlDbType.Int,  4);
            parameter.Value = orderID;

             return GetList(predicate, parameter);
        }

         // 获取实体对象列表
         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]));
            }

             return list;
        }

         // 获取DataTable
         private DataTable GetTable( string predicate,  params SqlParameter[] parameters)
        {
             string sql =  @" SELECT [OrderItemID],
                                  [OrderID],
                                  [Product],
                                  [UnitPrice],
                                  [Quantity],
                                  [UnitPrice]*[Quantity] AS SubTotal
                           FROM [OrderItem] ";
             if ( null != predicate &&  "" != predicate.Trim())
            {
                sql +=  "   WHERE   " + predicate;
            }
             return dbHelper.ExecuteQuery(sql, parameters);
        }

         // 将DataRow转换为实体对象
         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;
        }
    }
}

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 OrderStateDAO : IOrderStateDAO
    {
         private SqlDbHelper dbHelper =  new SqlDbHelper();

         // 获取实体对象列表
         public List<OrderState> GetList()
        {
             return GetList( null,  null);
        }

         // 获取实体对象列表
         public List<OrderState> GetList( string predicate,  params SqlParameter[] parameters)
        {
            List<OrderState> list =  new List<OrderState>();
            DataTable dataTable = GetTable(predicate, parameters);
             for ( int i =  0; i < dataTable.Rows.Count; i++)
            {
                list.Add(RowToModel(dataTable.Rows[i]));
            }

             return list;
        }

         // 获取DataTable
         private DataTable GetTable( string predicate,  params SqlParameter[] parameters)
        {
             string sql =  @" SELECT * FROM [OrderState] ";
             if ( null != predicate &&  "" != predicate.Trim())
            {
                sql +=  "   WHERE   " + predicate;
            }
            sql +=  "  ORDER BY [Code]  ";
             return dbHelper.ExecuteQuery(sql, parameters);
        }

         // 将DataRow转换为实体对象
         private OrderState RowToModel(DataRow row)
        {
            OrderState orderState =  new OrderState();
            orderState.Code = row[ " Code "].ToString();
            orderState.Name = row[ " Name "].ToString();
             return orderState;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
// 新添命名空间
using System.Data;
using System.Data.OleDb;
using HomeShop.DbUtility;
using HomeShop.Model;
using HomeShop.DALInterface;

namespace HomeShop.DALOfAccess
{
     public  class OrderDAO : IOrderDAO
    {
         private OleDbHelper dbHelper =  new OleDbHelper();

         // 添加
         public  int Add(Order order)
        {
             int rowsCountAffected =  0;
            OleDbTransaction trans = dbHelper.BeginTransaction();
             try
            {
                 // 新增订单基本信息
                 string sql =  @" INSERT INTO [Order]([OrderTime],
                                                   [OrderStateCode],
                                                   [CustomerName],
                                                   [CustomerPhoneNo],
                                                   [CustomerAddress])
                               VALUES(@OrderTime,
                                      @OrderStateCode,
                                      @CustomerName,
                                      @CustomerPhoneNo,
                                      @CustomerAddress) ";
                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)};
                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) ";
                    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)};
                    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<OleDbCmdTextAndParams> listCmdTextAndParams =  new List<OleDbCmdTextAndParams>();
             // 修改订单基本信息
             string sql =  @"  UPDATE [Order]
                            SET [OrderTime] = @OrderTime,
                                [OrderStateCode] = @OrderStateCode,
                                [CustomerName] = @CustomerName,
                                [CustomerPhoneNo] = @CustomerPhoneNo,
                                [CustomerAddress] = @CustomerAddress
                            WHERE [OrderID] = @OrderID ";
            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),
                     new OleDbParameter( " @OrderID ", OleDbType.Integer, 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 OleDbCmdTextAndParams(sql, parameters));
             // -----------------------------------------------------------
             // 循环将订购商品信息列表同步更新到数据库中
             // 删除
             string predicate =  "  OrderID = @OrderID  ";
            OleDbParameter parameter =  new OleDbParameter( " @OrderID ", OleDbType.Integer,  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 ";
                OleDbParameter parameterX =  new OleDbParameter( " @OrderItemID ", OleDbType.Integer,  4);
                parameterX.Value = originalOrder.OrderItems[i].OrderItemID;
                listCmdTextAndParams.Add( new OleDbCmdTextAndParams(sqlX, parameterX));
            }
             // 新增/修改
            OrderItemDAO orderItemDAO =  new OrderItemDAO();
             for ( int i =  0; i < order.OrderItems.Count; i++)
            {
                 if ( 0 >= order.OrderItems[i].OrderItemID) // 新增
                {
                     string sqlX =  @" INSERT INTO [OrderItem]([OrderID],
                                                            [Product],
                                                            [UnitPrice],
                                                            [Quantity])
                                    VALUES( @OrderID,
                                            @Product,
                                            @UnitPrice,
                                            @Quantity) ";
                    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)};
                    parametersX[ 0].Value = order.OrderItems[i].OrderID;
                    parametersX[ 1].Value = order.OrderItems[i].Product;
                    parametersX[ 2].Value = order.OrderItems[i].UnitPrice;
                    parametersX[ 3].Value = order.OrderItems[i].Quantity;
                    listCmdTextAndParams.Add( new OleDbCmdTextAndParams(sqlX, parametersX));
                }
                 else // 修改
                {
                     string sqlX =  @" UPDATE [OrderItem]
                                                SET [OrderID] = @OrderID,
                                                    [Product] = @Product,
                                                    [UnitPrice] = @UnitPrice,
                                                    [Quantity] = @Quantity
                                                WHERE [OrderItemID] = @OrderItemID ";
                    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),
                         new OleDbParameter( " @OrderItemID ", OleDbType.Integer,  4)};
                    parametersX[ 0].Value = order.OrderItems[i].OrderID;
                    parametersX[ 1].Value = order.OrderItems[i].Product;
                    parametersX[ 2].Value = order.OrderItems[i].UnitPrice;
                    parametersX[ 3].Value = order.OrderItems[i].Quantity;
                    parametersX[ 4].Value = order.OrderItems[i].OrderItemID;
                    listCmdTextAndParams.Add( new OleDbCmdTextAndParams(sqlX, parametersX));
                }
            }
            rowsCountAffected = dbHelper.ExecuteNonQueryTrans(listCmdTextAndParams);
             return rowsCountAffected;
        }

         // 删除
         public  int Delete( int orderID)
        {
            List<OleDbCmdTextAndParams> listCmdTextAndParams =  new List<OleDbCmdTextAndParams>();

             string sql =  @" DELETE FROM [OrderItem]
                           WHERE [OrderID] = @OrderID ";
            OleDbParameter parameter =  new OleDbParameter( " @OrderID ", orderID);
            listCmdTextAndParams.Add( new OleDbCmdTextAndParams(sql, parameter));

            sql =  @"        DELETE FROM [Order]
                           WHERE [OrderID] = @OrderID  ";
            listCmdTextAndParams.Add( new OleDbCmdTextAndParams(sql, parameter));

             return dbHelper.ExecuteNonQueryTrans(listCmdTextAndParams);
        }

         // 获取实体对象列表
         public List<Order> GetList()
        {
             return GetList( null,  null);
        }

         // 获取实体对象列表
         public List<Order> GetList( string customerName)
        {
             string predicate =  "  CustomerName LIKE '%' + @CustomerName + '%' ";
            OleDbParameter parameter =  new OleDbParameter( " @CustomerName ", OleDbType.VarChar,  30);
            parameter.Value = customerName;

             return GetList(predicate, parameter);
        }

         // 获取实体对象列表
         public List<Order> GetList( string predicate,  params OleDbParameter[] 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)
        {
             string predicate =  "  OrderID = @OrderID  ";
            OleDbParameter parameter =  new OleDbParameter( " @OrderID ", OleDbType.Integer,  4);
            parameter.Value = orderID;

             return GetSingle(predicate, parameter);
        }

         // 获取单一实体对象
         private Order GetSingle( string predicate,  params OleDbParameter[] 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 OleDbParameter[] 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();
             // ----子表----
            OrderItemDAO orderItemDAO =  new OrderItemDAO();
            order.OrderItems = orderItemDAO.GetList(order.OrderID);

             return order;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
// 新添命名空间
using System.Data;
using System.Data.OleDb;
using HomeShop.DbUtility;
using HomeShop.Model;
using HomeShop.DALInterface;

namespace HomeShop.DALOfAccess
{
     public  class OrderItemDAO : IOrderItemDAO
    {
         private OleDbHelper dbHelper =  new OleDbHelper();

         // 获取实体对象列表
         public List<OrderItem> GetList( int orderID)
        {
             string predicate =  "  OrderID = @OrderID  ";
            OleDbParameter parameter =  new OleDbParameter( " @OrderID ", OleDbType.Integer,  4);
            parameter.Value = orderID;

             return GetList(predicate, parameter);
        }

         // 获取实体对象列表
         private List<OrderItem> GetList( string predicate,  params OleDbParameter[] 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]));
            }

             return list;
        }

         // 获取DataTable
         private DataTable GetTable( string predicate,  params OleDbParameter[] parameters)
        {
             string sql =  @" SELECT [OrderItemID],
                                  [OrderID],
                                  [Product],
                                  [UnitPrice],
                                  [Quantity],
                                  [UnitPrice]*[Quantity] AS SubTotal
                           FROM [OrderItem] ";
             if ( null != predicate &&  "" != predicate.Trim())
            {
                sql +=  "   WHERE   " + predicate;
            }
             return dbHelper.ExecuteQuery(sql, parameters);
        }

         // 将DataRow转换为实体对象
         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;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
// 新添命名空间
using System.Data;
using System.Data.OleDb;
using HomeShop.DbUtility;
using HomeShop.Model;
using HomeShop.DALInterface;

namespace HomeShop.DALOfAccess
{
     public  class OrderStateDAO : IOrderStateDAO
    {
         private OleDbHelper dbHelper =  new OleDbHelper();

         // 获取实体对象列表
         public List<OrderState> GetList()
        {
             return GetList( null,  null);
        }

         // 获取实体对象列表
         public List<OrderState> GetList( string predicate,  params OleDbParameter[] parameters)
        {
            List<OrderState> list =  new List<OrderState>();
            DataTable dataTable = GetTable(predicate, parameters);
             for ( int i =  0; i < dataTable.Rows.Count; i++)
            {
                list.Add(RowToModel(dataTable.Rows[i]));
            }

             return list;
        }

         // 获取DataTable
         private DataTable GetTable( string predicate,  params OleDbParameter[] parameters)
        {
             string sql =  @" SELECT * FROM [OrderState] ";
             if ( null != predicate &&  "" != predicate.Trim())
            {
                sql +=  "   WHERE   " + predicate;
            }
            sql +=  "  ORDER BY [Code]  ";
             return dbHelper.ExecuteQuery(sql, parameters);
        }

         // 将DataRow转换为实体对象
         private OrderState RowToModel(DataRow row)
        {
            OrderState orderState =  new OrderState();
            orderState.Code = row[ " Code "].ToString();
            orderState.Name = row[ " Name "].ToString();
             return orderState;
        }
    }
}