.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

img_405b18b4b6584ae338e0f6ecaf736533.gif 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

img_405b18b4b6584ae338e0f6ecaf736533.gif 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

img_405b18b4b6584ae338e0f6ecaf736533.gif 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

 

img_405b18b4b6584ae338e0f6ecaf736533.gif 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

 

img_405b18b4b6584ae338e0f6ecaf736533.gif 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

img_405b18b4b6584ae338e0f6ecaf736533.gif 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

 

目录
相关文章
|
19天前
|
存储 JSON 开发工具
Visual Studio编程效率提升技巧集(提高.NET编程效率)
Visual Studio编程效率提升技巧集(提高.NET编程效率)
Visual Studio编程效率提升技巧集(提高.NET编程效率)
|
2月前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(五):PL/SQL编程
【Oracle】玩转Oracle数据库(五):PL/SQL编程
97 8
|
2月前
|
SQL 存储 Java
Java数据库编程知识点总结
【4月更文挑战第6天】Java数据库编程涉及使用JDBC API与关系型数据库交互。关键知识点包括:JDBC作为标准API用于数据库访问;加载驱动并用DriverManager建立数据库连接;掌握SQL语句的编写与执行;使用PreparedStatement和CallableStatement执行预编译SQL及存储过程;ResultSet处理查询结果;理解事务管理确保数据一致性;批处理优化多SQL执行;连接池提升性能稳定性;以及异常处理如SQLException和ClassNotFoundException的捕获。
33 6
|
1月前
|
SQL XML Java
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
37 3
|
10天前
|
数据库管理 Python
在停车场管理系统工程中,我们可能会涉及到硬件设计、软件编程、数据库管理、用户界面设计等多个方面
在停车场管理系统工程中,我们可能会涉及到硬件设计、软件编程、数据库管理、用户界面设计等多个方面
|
1月前
|
SQL Java 关系型数据库
Java数据库编程的详细介绍
Java数据库编程的详细介绍
16 1
|
2月前
|
SQL Java 关系型数据库
Java之JDBC数据库编程
Java之JDBC数据库编程
25 2
|
2月前
|
存储 Java 分布式数据库
【分布式计算框架】HBase数据库编程实践
【分布式计算框架】HBase数据库编程实践
39 1
|
2月前
|
SQL Java 关系型数据库
JavaWeb(JDBC编程)看这一篇就够了 —— 如何使用Java操作mysql数据库
JavaWeb(JDBC编程)看这一篇就够了 —— 如何使用Java操作mysql数据库
42 0
|
2月前
|
JSON 编解码 Go
Golang深入浅出之-HTTP客户端编程:使用net/http包发起请求
【4月更文挑战第25天】Go语言`net/http`包提供HTTP客户端和服务器功能,简化高性能网络应用开发。本文探讨如何发起HTTP请求,常见问题及解决策略。示例展示GET和POST请求的实现。注意响应体关闭、错误处理、内容类型设置、超时管理和并发控制。最佳实践包括重用`http.Client`,使用`context.Context`,处理JSON以及记录错误日志。通过实践这些技巧,提升HTTP编程技能。
43 1