电子商务之数据访问层分析(七)
这里主要分析的也是怎么在这层编写事务
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
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程序集,其次这是一个事务基类所有的事务类都会继承它。
下面是一个事务类继承了上面的基类:
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
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
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
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。
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
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)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
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)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
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就可以判断是否执行成功)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
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)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
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;
}
}
}
同系列文章
- 电子商务之框架分析(一)
- 电子商务之数据库分析(二)
- 电子商务之Common类库分析(三)
- 电子商务之集成选项分析(四)
- 电子商务之数据存储流程(五)
- 电子商务之存储过程分析(六)
- 电子商务之数据访问层分析(七)
- 电子商务之购物车分析(八)
- 电子商务之表示层分析(九)