.NET数据库编程求索之路--7.使用ADO.NET实现(工厂模式-实现多数据库切换)(3)-阿里云开发者社区

开发者社区> 夏春涛> 正文

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

简介: 7.使用ADO.NET实现(工厂模式-实现多数据库切换)(3)   7.5 SQL数据访问层HomeShop.DALOfSql   OrderDAO.cs View Code using System;using System.
+关注继续查看

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("@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(nullnull);
        }

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

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

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

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

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

 

View Code
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(nullnull);
        }

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

 

View Code
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

View Code
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(nullnull);
        }

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

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
使用WCF实现SOA面向服务编程—— 架构设计
本文转载:http://kb.cnblogs.com/page/96479/  SOA本身就是一种面向企业级服务的系统架构,简单来说,SOA就是一种进行系统开发的新的体系架构,在基于SOA架构的系统中,具体应用程序的功 能是由 一些松耦合并且具有统一接口定义方式的组件(也就是service)组合构建起来的。
735 0
.NET数据库编程求索之路--8.使用LinqToSQL实现
8.使用LinqToSQL实现   源码下载:     /Files/SummerRain/NetDbDevRoad/8使用LinqToSql实现1ORM手工简单三层.rar     /Files/SummerRain/NetDbDevRoad/8使用LinqToSql实现2ORM手工工厂模式.
629 0
使用WCF实现SOA面向服务编程——使用AJAX+WCF服务“.NET技术”页面开发
  在上一篇讲到,如果将BLL层的每一个类都转化为*.svc,这是一个不实在的想法。它会使服务变化复杂,难于管理。   这时候,我们第一时间想到的是23个简单开发模式中的Factory,在这里,Factory正好派上用场。
869 0
.NET数据库编程求索之路--7.使用ADO.NET实现(工厂模式-实现多数据库切换)(2)
7.使用ADO.NET实现(工厂模式-实现多数据库切换)(2)     7.3 数据访问层工厂HomeShop.DALFactory      1 using System; 2 using System.
889 0
.NET数据库编程求索之路--7.使用ADO.NET实现(工厂模式-实现多数据库切换)(4)
7.使用ADO.NET实现(工厂模式-实现多数据库切换)(4) 7.7 业务逻辑层HomeShop.BLL OrderBiz.cs View Code  1 using System; 2 using System.
775 0
.NET数据库编程求索之路--7.使用ADO.NET实现(工厂模式-实现多数据库切换)(3)
7.使用ADO.NET实现(工厂模式-实现多数据库切换)(3)   7.5 SQL数据访问层HomeShop.DALOfSql   OrderDAO.cs View Code using System;using System.
669 0
+关注
夏春涛
曾任教于信息工程大学16年,长期从事计算机软件与信息安全教学科研工作,目前任职于某软件公司从事技术管理工作,擅长架构设计、开发管理,对大数据、分布式颇感兴趣。
271
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载