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

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

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

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Configuration;

namespaceShop.DataAccess.Transaction
{
publicclassTransactionBase
{
protectedSqlTransactiontransaction=null;
protectedSqlConnectionconnection=null;
protectedSqlCommandcommand=null;

publicTransactionBase()
{
connection=newSqlConnection(ConfigurationManager.ConnectionStrings["db_shopConnectionString"].ToString());
connection.Open();
command=connection.CreateCommand();
}
}
}

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

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

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingShop.Common;
usingShop.DataAccess.Insert;

namespaceShop.DataAccess.Transaction
{
publicclassOrderInsertTransaction:TransactionBase
{
publicOrderInsertTransaction()
{}

publicvoidBegin(OrdersEntityorders)
{
command=connection.CreateCommand();
transaction=connection.BeginTransaction("OrderInsert");
command.Connection=connection;
command.Transaction=transaction;

OrderInsertDataorderadd=newOrderInsertData();
OrderDetailsInsertDataorderdetailsdd=newOrderDetailsInsertData();

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

for(inti=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(Exceptionex)
{
transaction.Rollback("OrderInsert");
throwex;
}
}
}
}

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

注意Begin函数：

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

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

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

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

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data.SqlClient;
usingShop.Common;

namespaceShop.DataAccess.Insert
{
publicclassOrderInsertData:DataAccessBase
{
publicOrdersEntityOrders{set;get;}
publicOrderInsertData()
{
this.StoredprocedureName=StoredProcedure.Name.Order_Insert.ToString();
}

publicvoidAdd(SqlTransactiontransaction)
{
OrderInsertDataParametersorderinsertdata=newOrderInsertDataParameters(this.Orders);
DataBaseHelperdbhelper=newDataBaseHelper(this.StoredprocedureName);
dbhelper.Parameters=orderinsertdata.Parameters;
objectid=dbhelper.RunScalar(transaction,orderinsertdata.Parameters);
this.Orders.OrderID=int.Parse(id.ToString());
}
}

publicclassOrderInsertDataParameters
{
publicOrdersEntityOrder{set;get;}
publicSqlParameter[]Parameters;
publicOrderInsertDataParameters(OrdersEntityorder)
{
this.Order=order;
Build();
}

privatevoidBuild()
{
SqlParameter[]parameter=
{
newSqlParameter("@EndUserID",this.Order.EndUserID),
newSqlParameter("@TransactionID",this.Order.TransactionID)
};
this.Parameters=parameter;
}
}
}

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

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data.SqlClient;
usingShop.Common;

namespaceShop.DataAccess.Insert
{
publicclassOrderDetailsInsertData:DataAccessBase
{
publicOrderDetailsEntityOrderDetails{set;get;}

publicOrderDetailsInsertData()
{
this.StoredprocedureName=StoredProcedure.Name.OrderDetails_Insert.ToString();
this.OrderDetails=newOrderDetailsEntity();
}

publicvoidAdd(SqlTransactiontransaction)
{
OrderDetailsInsertDataParametersorderdetailsinsertdataparameters=newOrderDetailsInsertDataParameters(this.OrderDetails);
DataBaseHelperdbhelper=newDataBaseHelper(this.StoredprocedureName);
dbhelper.Run(transaction,orderdetailsinsertdataparameters.Parameters);
}

}

publicclassOrderDetailsInsertDataParameters
{
publicOrderDetailsEntityOrderDetails{set;get;}
publicSqlParameter[]Parameters{set;get;}

publicOrderDetailsInsertDataParameters(OrderDetailsEntityorderdetails)
{
this.OrderDetails=orderdetails;
Build();
}

privatevoidBuild()
{
SqlParameter[]parameters=
{
newSqlParameter("@OrderID",this.OrderDetails.OrderID),
newSqlParameter("@ProductID",this.OrderDetails.ProductID),
newSqlParameter("@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）

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data;
usingShop.DataAccess;
usingShop.Common;
usingSystem.Data.SqlClient;

namespaceShop.DataAccess.Select
{
publicclassProductSelectByIDData:DataAccessBase
{
publicProductEntityProduct{get;set;}

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

//得到查询数据
publicDataSetGet()
{
DataSetds;

ProductSelectByIDDataParameters_productselectbyiddataparameters=
newProductSelectByIDDataParameters(this.Product);

//查询数据
DataBaseHelperdbhelper=newDataBaseHelper(StoredprocedureName);
ds=dbhelper.Run(base.ConnectionString,_productselectbyiddataparameters.Parameters);
returnds;
}
}

///<summary>
///查询参数类
///</summary>
publicclassProductSelectByIDDataParameters
{
publicProductEntityProduct{set;get;}
publicSqlParameter[]Parameters{set;get;}

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

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

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

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingShop.Common;
usingShop.DataAccess.Select;

namespaceShop.DataAccess.Select
{
publicclassOrderAllSelectData:DataAccessBase
{
publicOrderAllSelectData()
{
this.StoredprocedureName=StoredProcedure.Name.OrdersAll_Select.ToString();
}

publicDataSetGet()
{
DataSetds;
DataBaseHelperdbhelper=newDataBaseHelper(this.StoredprocedureName);
ds=dbhelper.Run(base.ConnectionString);
returnds;
}
}
}

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

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data.SqlClient;
usingShop.Common;

namespaceShop.DataAccess.Delete
{
publicclassShoppingCartDeleteData:DataAccessBase
{
publicShoppingCartEntityShoppingCart{set;get;}
privateShoppingCartDeleteDataParametersShoppingCartDeleteDataParameters{set;get;}

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

publicvoidDelete()
{
this.ShoppingCartDeleteDataParameters=newShoppingCartDeleteDataParameters(this.ShoppingCart);
DataBaseHelperdbhelper=newDataBaseHelper(this.StoredprocedureName);
dbhelper.Parameters=this.ShoppingCartDeleteDataParameters.Parameters;
dbhelper.Run();
}
}

publicclassShoppingCartDeleteDataParameters
{
publicShoppingCartEntityShoppingCart{set;get;}
publicSqlParameter[]Parameters{set;get;}

publicShoppingCartDeleteDataParameters(ShoppingCartEntityshoppingcart)
{
this.ShoppingCart=shoppingcart;
Build();
}

privatevoidBuild()
{
SqlParameter[]parameters=
{
newSqlParameter("@ShoppingCartID",this.ShoppingCart.ShoppingCartID)
};
this.Parameters=parameters;
}
}
}

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

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingShop.Common;
usingShop.DataAccess;
usingSystem.Data.SqlClient;

namespaceShop.DataAccess.Insert
{
publicclassEndUserInsertData:DataAccessBase
{
publicEndUserEntityEndUser{set;get;}

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

publicvoidAdd()
{
EndUserInsertDataParametersendinsertdataparameters=newEndUserInsertDataParameters(this.EndUser);
DataBaseHelperdbhelper=newDataBaseHelper(this.StoredprocedureName);
objectid=dbhelper.RunScalar(base.ConnectionString,endinsertdataparameters.Parameters);
//因为这里EndUserID是数据库自动生成的，所以这里可以得到它的值
EndUser.EndUserID=int.Parse(id.ToString());
}

}

publicclassEndUserInsertDataParameters
{
publicEndUserEntityEndUser{set;get;}
publicSqlParameter[]Parameters{set;get;}
publicEndUserInsertDataParameters(EndUserEntityenduser)
{
this.EndUser=enduser;
Build();
}

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