.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

 

目录
相关文章
|
2月前
|
SQL 开发框架 数据库
".NET开发者的超能力:AgileEAS.NET ORM带你穿越数据库的迷宫,让数据操作变得轻松又神奇!"
【8月更文挑战第16天】AgileEAS.NET是面向.NET平台的企业应用开发框架,核心功能包括数据关系映射(ORM),允许以面向对象方式操作数据库,无需编写复杂SQL。通过继承`AgileEAS.Data.Entity`创建实体类对应数据库表,利用ORM简化数据访问层编码。支持基本的CRUD操作及复杂查询如条件筛选、排序和分页,并可通过导航属性实现多表关联。此外,提供了事务管理功能确保数据一致性。AgileEAS.NET的ORM简化了数据库操作,提升了开发效率和代码可维护性。
46 5
|
1月前
|
SQL 存储 关系型数据库
C#一分钟浅谈:使用 ADO.NET 进行数据库访问
【9月更文挑战第3天】在.NET开发中,与数据库交互至关重要。ADO.NET是Microsoft提供的用于访问关系型数据库的类库,包含连接数据库、执行SQL命令等功能。本文从基础入手,介绍如何使用ADO.NET进行数据库访问,并提供示例代码,同时讨论常见问题及其解决方案,如连接字符串错误、SQL注入风险和资源泄露等,帮助开发者更好地利用ADO.NET提升应用的安全性和稳定性。
66 6
|
2月前
|
Java 数据库连接 网络安全
JDBC数据库编程(java实训报告)
这篇文章是关于JDBC数据库编程的实训报告,涵盖了实验要求、实验环境、实验内容和总结。文中详细介绍了如何使用Java JDBC技术连接数据库,并进行增删改查等基本操作。实验内容包括建立数据库连接、查询、添加、删除和修改数据,每个部分都提供了相应的Java代码示例和操作测试结果截图。作者在总结中分享了在实验过程中遇到的问题和解决方案,以及对Java与数据库连接操作的掌握情况。
JDBC数据库编程(java实训报告)
|
11天前
|
存储 数据库 Python
python的对象数据库ZODB的使用(python3经典编程案例)
该文章介绍了如何使用Python的对象数据库ZODB来进行数据存储,包括ZODB的基本操作如创建数据库、存储和检索对象等,并提供了示例代码。
16 0
|
11天前
|
JSON NoSQL 数据库
和SQLite数据库对应的NoSQL数据库:TinyDB的详细使用(python3经典编程案例)
该文章详细介绍了TinyDB这一轻量级NoSQL数据库的使用方法,包括如何在Python3环境中安装、创建数据库、插入数据、查询、更新以及删除记录等操作,并提供了多个编程案例。
24 0
|
2月前
|
大数据 开发工具 开发者
从零到英雄:.NET核心技术带你踏上编程之旅,构建首个应用,开启你的数字世界探险!
【8月更文挑战第28天】本文带领读者从零开始,使用强大的.NET平台搭建首个控制台应用。无论你是新手还是希望扩展技能的开发者,都能通过本文逐步掌握.NET的核心技术。从环境搭建到创建项目,再到编写和运行代码,详细步骤助你轻松上手。通过计算两数之和的小项目,你不仅能快速入门,还能为未来开发更复杂的应用奠定基础。希望本文为你的.NET学习之旅开启新篇章!
29 1
|
2月前
|
存储 C#
揭秘C#.Net编程秘宝:结构体类型Struct,让你的数据结构秒变高效战斗机,编程界的新星就是你!
【8月更文挑战第4天】在C#编程中,结构体(`struct`)是一种整合多种数据类型的复合数据类型。与类不同,结构体是值类型,意味着数据被直接复制而非引用。这使其适合表示小型、固定的数据结构如点坐标。结构体默认私有成员且不可变,除非明确指定。通过`struct`关键字定义,可以包含字段、构造函数及方法。例如,定义一个表示二维点的结构体,并实现计算距离原点的方法。使用时如同普通类型,可通过实例化并调用其成员。设计时推荐保持结构体不可变以避免副作用,并注意装箱拆箱可能导致的性能影响。掌握结构体有助于构建高效的应用程序。
61 7
|
2月前
|
数据库 C# 开发者
WPF开发者必读:揭秘ADO.NET与Entity Framework数据库交互秘籍,轻松实现企业级应用!
【8月更文挑战第31天】在现代软件开发中,WPF 与数据库的交互对于构建企业级应用至关重要。本文介绍了如何利用 ADO.NET 和 Entity Framework 在 WPF 应用中访问和操作数据库。ADO.NET 是 .NET Framework 中用于访问各类数据库(如 SQL Server、MySQL 等)的类库;Entity Framework 则是一种 ORM 框架,支持面向对象的数据操作。文章通过示例展示了如何在 WPF 应用中集成这两种技术,提高开发效率。
41 0
|
2月前
|
Java Spring 自然语言处理
Spring 框架里竟藏着神秘魔法?国际化与本地化的奇妙之旅等你来揭开谜底!
【8月更文挑战第31天】在软件开发中,国际化(I18N)与本地化(L10N)对于满足不同地区用户需求至关重要。Spring框架提供了强大支持,利用资源文件和`MessageSource`实现多语言文本管理。通过配置日期格式和货币符号,进一步完善本地化功能。合理应用这些特性,可显著提升应用的多地区适应性和用户体验。
32 0
下一篇
无影云桌面