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

7.使用ADO.NET实现（工厂模式-实现多数据库切换）（3）

 

7.5 SQL数据访问层HomeShop.DALOfSql

 

OrderDAO.cs

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;
        }

    }
}

 

OrderItemDAO.cs

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;
        }
    }
}

 

OrderStateDAO.cs

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;
        }
    }
}

 

7.6 Access数据访问层HomeShop.DALOfAccess

 

OrderDAO.cs

 

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;
        }
    }
}

 

OrderItemDAO.cs

 

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;
        }
    }
}

 

OrderStateDAO.cs

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;
        }
    }
}

 

 

源码下载：/Files/SummerRain/NetDbDevRoad/7使用ADONET实现工厂模式DB切换.rar

数据下载：/Files/SummerRain/NetDbDevRoad/HomeShopDB.rar