电子商务之数据访问层分析（七）

这里主要分析的也是怎么在这层编写事务

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Shop.DataAccess.Transaction
{
    public class TransactionBase
    {
        protected SqlTransaction transaction = null;
        protected SqlConnection connection = null;
        protected SqlCommand command = null;

        public TransactionBase()
        {
            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["db_shopConnectionString"].ToString());
            connection.Open();
            command = connection.CreateCommand();
        }
    }
}

上面的代码注意首先要引用System.Configuration程序集，其次这是一个事务基类所有的事务类都会继承它。

下面是一个事务类继承了上面的基类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Shop.Common;
using Shop.DataAccess.Insert;

namespace Shop.DataAccess.Transaction
{
    public class OrderInsertTransaction:TransactionBase
    {
        public OrderInsertTransaction()
        { }

        public void Begin(OrdersEntity orders)
        {
            command = connection.CreateCommand();
            transaction = connection.BeginTransaction("OrderInsert");
            command.Connection = connection;
            command.Transaction = transaction;

            OrderInsertData orderadd = new OrderInsertData();
            OrderDetailsInsertData orderdetailsdd = new OrderDetailsInsertData();

            try
            {
                orderadd.Orders = orders;
                orderadd.Add(transaction);

                for (int i = 0; i < orders.OrderDetails.Products.Length; i++)
                {
                    orderdetailsdd.OrderDetails.OrderID = orders.OrderID;
                    orderdetailsdd.OrderDetails.ProductID = orders.OrderDetails.Products[i].ProductID;
                    orderdetailsdd.OrderDetails.Quantity = orders.OrderDetails.Products[i].Quantity;
                    orderdetailsdd.Add(transaction);
                }
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback("OrderInsert");
                throw ex;
            }
        }
    }
}

首先说明下要完成的功能：插入一个订单表的数据后，同时还要插入几张详细订单表。

注意Begin函数：

开始用connection.CreateCommand()构建一个command对象.

在用connction.BeginTransaction("stringname")构建一个名为stringname事务

再分别赋值command.Connection和command.Transantion

在下面分别构造两个对象：orderadd和orderdetailsdd

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Shop.Common;

namespace Shop.DataAccess.Insert
{
    public class OrderInsertData:DataAccessBase
    {
        public OrdersEntity Orders { set; get; }
        public OrderInsertData()
        {
            this.StoredprocedureName = StoredProcedure.Name.Order_Insert.ToString();
        }

        public void Add(SqlTransaction transaction)
        {
            OrderInsertDataParameters orderinsertdata = new OrderInsertDataParameters(this.Orders);
            DataBaseHelper dbhelper = new DataBaseHelper(this.StoredprocedureName);
            dbhelper.Parameters = orderinsertdata.Parameters;
            object id = dbhelper.RunScalar(transaction, orderinsertdata.Parameters);
            this.Orders.OrderID = int.Parse(id.ToString());
        }
    }

    public class OrderInsertDataParameters
    {
        public OrdersEntity Order { set; get; }
        public SqlParameter[] Parameters;
        public OrderInsertDataParameters(OrdersEntity order)
        {
            this.Order = order;
            Build();
        }

        private void Build()
        {
            SqlParameter[] parameter =
            {
                new SqlParameter("@EndUserID",this.Order.EndUserID),
                new SqlParameter("@TransactionID",this.Order.TransactionID)         
            };
            this.Parameters = parameter;
        }
    }
}

这上面类封装了插入order表的操作。注意这里Add()方法参数是Trancation。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Shop.Common;

namespace Shop.DataAccess.Insert
{
    public class OrderDetailsInsertData:DataAccessBase
    {
        public OrderDetailsEntity OrderDetails { set; get; }

        public OrderDetailsInsertData()
        {
            this.StoredprocedureName = StoredProcedure.Name.OrderDetails_Insert.ToString();
            this.OrderDetails = new OrderDetailsEntity();
        }

        public void Add(SqlTransaction transaction)
        {
            OrderDetailsInsertDataParameters orderdetailsinsertdataparameters = new OrderDetailsInsertDataParameters(this.OrderDetails);
            DataBaseHelper dbhelper = new DataBaseHelper(this.StoredprocedureName);
            dbhelper.Run(transaction, orderdetailsinsertdataparameters.Parameters);
        }
       
    }

    public class OrderDetailsInsertDataParameters
    {
        public OrderDetailsEntity OrderDetails { set; get; }
        public SqlParameter[] Parameters { set; get; }

        public OrderDetailsInsertDataParameters(OrderDetailsEntity orderdetails)
        {
            this.OrderDetails = orderdetails;
            Build();
        }

        private void Build()
        {
            SqlParameter[] parameters = 
            {
                new SqlParameter("@OrderID",this.OrderDetails.OrderID),
                new SqlParameter("@ProductID",this.OrderDetails.ProductID),
                new SqlParameter("@Quantity",this.OrderDetails.Quantity)
            };
            this.Parameters = parameters;
        }

    }
}

这里分装了插入orderdetail表的操作。

 

这两个orderadd执行了将数据插入order表，然后根据刚刚插入order表数据中的product种类数量循环将一件件不同种类的product插入到详细订单当中去.运用try...catch 抓取异常或错误，如果发生异常或错误就会发生回滚，还原数据库初始状态。

 

在说说这层结构吧，这里将与数据库的操作分为了5类：select,delete,insert,update,transaction于是将相同的操作放在同一个文件下面。

这层一般的写法，一个是获得数据类和一个构造Sql参数类，在获得数据类中通过赋值不同的参数来重载Run函数从而得到所要的结果。

我就给出几种不同的重载Run函数的例子吧

1.带参数，返回结果（DataSet）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Shop.DataAccess;
using Shop.Common;
using System.Data.SqlClient;

namespace Shop.DataAccess.Select
{
    public class ProductSelectByIDData : DataAccessBase
    {
        public ProductEntity Product { get; set; }

        //赋值存储过程
        public ProductSelectByIDData()
        {
            //得到存储过程的名称
            this.StoredprocedureName = StoredProcedure.Name.ProductByID_Select.ToString();
        }

        //得到查询数据
        public DataSet Get()
        {
            DataSet ds;

            ProductSelectByIDDataParameters _productselectbyiddataparameters =
                new ProductSelectByIDDataParameters(this.Product);

            //查询数据
            DataBaseHelper dbhelper = new DataBaseHelper(StoredprocedureName);
            ds = dbhelper.Run(base.ConnectionString, _productselectbyiddataparameters.Parameters);
            return ds;
        }
    }

    /// <summary>
    /// 查询参数类
    /// </summary>
    public class ProductSelectByIDDataParameters
    {
        public ProductEntity Product { set; get; }
        public SqlParameter[] Parameters { set; get; }

        /// <summary>
        /// 构造函数给它两个属性初始化
        /// </summary>
        /// <param name="product"></param>
        public ProductSelectByIDDataParameters(ProductEntity product)
        {
            this.Product = product;
            Build();
        }

        private void Build()
        {
            //传入产品编号作为参数
            SqlParameter[] parameters =
            {
                new SqlParameter("@ProductID",this.Product.ProductID)
            };
            this.Parameters = parameters;
        }
    }
}

 2.不带参数，返回结果（DataSet）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Shop.Common;
using Shop.DataAccess.Select;

namespace Shop.DataAccess.Select
{
    public class OrderAllSelectData:DataAccessBase
    {
        public OrderAllSelectData()
        {
            this.StoredprocedureName = StoredProcedure.Name.OrdersAll_Select.ToString();
        }

        public DataSet Get()
        {
            DataSet ds;
            DataBaseHelper dbhelper = new DataBaseHelper(this.StoredprocedureName);
            ds = dbhelper.Run(base.ConnectionString);
            return ds;
        }
    }
}

3.带参数，不返回结果（这里其实会返回受影响的行数，但是这里通过在表示层里 try...catch就可以判断是否执行成功）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Shop.Common;

namespace Shop.DataAccess.Delete
{
    public class ShoppingCartDeleteData:DataAccessBase
    {
        public ShoppingCartEntity ShoppingCart { set; get; }
        private ShoppingCartDeleteDataParameters ShoppingCartDeleteDataParameters { set; get; }

        public ShoppingCartDeleteData()
        {
            this.StoredprocedureName = StoredProcedure.Name.ShoppingCart_Delete.ToString();
        }

        public void Delete()
        {
            this.ShoppingCartDeleteDataParameters = new ShoppingCartDeleteDataParameters(this.ShoppingCart);
            DataBaseHelper dbhelper = new DataBaseHelper(this.StoredprocedureName);
            dbhelper.Parameters = this.ShoppingCartDeleteDataParameters.Parameters;
            dbhelper.Run();
        }
    }

    public class ShoppingCartDeleteDataParameters
    {
        public ShoppingCartEntity ShoppingCart { set; get; }
        public SqlParameter[] Parameters { set; get; }

        public ShoppingCartDeleteDataParameters(ShoppingCartEntity shoppingcart)
        {
            this.ShoppingCart = shoppingcart;
            Build();
        }

        private void Build()
        {
            SqlParameter[] parameters=
            {
                new SqlParameter("@ShoppingCartID",this.ShoppingCart.ShoppingCartID)
            };
            this.Parameters=parameters;
        }
    }
}

4.带参数，返回结果（object）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Shop.Common;
using Shop.DataAccess;
using System.Data.SqlClient;

namespace Shop.DataAccess.Insert
{
    public class EndUserInsertData:DataAccessBase
    {
        public EndUserEntity EndUser { set; get; }

        public EndUserInsertData()
        {
            this.StoredprocedureName = StoredProcedure.Name.EndUser_Insert.ToString();
        }

        public void Add()
        {
            EndUserInsertDataParameters endinsertdataparameters = new EndUserInsertDataParameters(this.EndUser);
            DataBaseHelper dbhelper = new DataBaseHelper(this.StoredprocedureName);
            object id = dbhelper.RunScalar(base.ConnectionString, endinsertdataparameters.Parameters);
            //因为这里EndUserID是数据库自动生成的，所以这里可以得到它的值
            EndUser.EndUserID = int.Parse(id.ToString());
        }

    }

    public class EndUserInsertDataParameters
    {
        public EndUserEntity EndUser { set; get; }
        public SqlParameter[] Parameters { set; get; }
        public EndUserInsertDataParameters(EndUserEntity enduser)
        {
            this.EndUser = enduser;
            Build();
        }

        private void Build()
        {
            SqlParameter[] parameters=
            {
                new SqlParameter("@UserName",EndUser.UserName),
                new SqlParameter("@AddressLine",EndUser.UserAddress.AddressLine),
                new SqlParameter("@AddressLine2",EndUser.UserAddress.AddressLine2),
                new SqlParameter("@City",EndUser.UserAddress.City),
                new SqlParameter("@Province",EndUser.UserAddress.Province),
                new SqlParameter("@PostalCode",EndUser.UserAddress.PostalCode),
                new SqlParameter("@Phone",EndUser.UserContactInformation.Phone),
                new SqlParameter("@Phone2",EndUser.UserContactInformation.Phone2),
                new SqlParameter("@Fax",EndUser.UserContactInformation.Fax),
                new SqlParameter("@Email",EndUser.UserContactInformation.Email),
                new SqlParameter("@EndUserTypeID",EndUser.EndUserTypeID),
                new SqlParameter("@Password",EndUser.Password),
                new SqlParameter("@IsSubscribed",EndUser.IsSubscribed)
            };
            this.Parameters=parameters;
        }
    }
}