开发者社区> 夏春涛> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

.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

 

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

相关文章
.Net普通三层->工厂模式->线程内唯一+单元工作模式->WebService分布式三层
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq1010885678/article/details/38023225 在...
612 0
.NET数据库编程求索之路--7.使用ADO.NET实现(工厂模式-实现多数据库切换)(4)
7.使用ADO.NET实现(工厂模式-实现多数据库切换)(4) 7.7 业务逻辑层HomeShop.BLL OrderBiz.cs View Code  1 using System; 2 using System.
636 0
.NET数据库编程求索之路--7.使用ADO.NET实现(工厂模式-实现多数据库切换)(2)
7.使用ADO.NET实现(工厂模式-实现多数据库切换)(2)     7.3 数据访问层工厂HomeShop.DALFactory      1 using System; 2 using System.
722 0
.NET数据库编程求索之路--7.使用ADO.NET实现(工厂模式-实现多数据库切换)(1)
7.使用ADO.NET实现(工厂模式-实现多数据库切换)(1)     7.1 解决方案框架 解决方案(.sln)包含以下几个项目: (1)类库项目HomeShop.DbUtility,数据访问实用工具; (2)类库项目HomeShop.
651 0
.NET简谈设计模式之(工厂模式)
文章开头本人申明一点,本人写的文章力求让看的人能理解精髓而不是专业化,博客园的好多人在写文章的时候总想着怎么怎么专业化,巴不得看的人都看不懂才能体现出他的技术,他的牛B,技术不是用来显威风用的,在你掌握了某种技术的时候,不要想着横扫博客园之类的思想,有点技术就开始浮躁连说话都开始像李莲英;我不是想这...
707 0
.Net线程同步技术解读
C#开发者(面试者)都会遇到lock(Monitor),Mutex,Semaphore,SemaphoreSlim这四个与锁相关的C#类型,本文期望以最简洁明了的方式阐述四种对象的区别。
0 0
.Net5发布在即,当心技术断层
.Net5发布在即,当心技术断层
0 0
.Net Mirco Framework 2007技术大会
最近公司很多项目都有大量嵌入式设备使用,由于WinCE系统相对较大,对硬件平台要求过高,所以对.Net MF一直比较关注。今天总算大开眼界了
801 0
[.NET]使用十年股价对比各种序列化技术
原文:[.NET]使用十年股价对比各种序列化技术 1. 前言 上一家公司有搞股票,当时很任性地直接从服务器读取一个股票10年份的股价(还有各种指标)在客户端的图表上显示,而且因为是桌面客户端,传输的数据也是简单粗暴地使用Soap序列化。
730 0
+关注
夏春涛
曾任教于信息工程大学16年,长期从事计算机软件与信息安全教学科研工作,目前任职于某软件公司从事技术管理工作,擅长架构设计、开发管理,对大数据、分布式颇感兴趣。
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载