订单提交设计

订单提交设计
数据表结构
订单提交涉及到两张表,订单明细表T_OrderHead和订单主表T_OrderDetails
--订单主表
create table T_OrderHead
(
OrderID Char(14) not null primary key,
OrderStatus int not null,
SubTotal Decimal(18,2) not null,
UserName Varchar(50) not null references T_User(UserName),
SubmitTime DateTime not null,
OrditTime DateTime null,
Orditer Varchar(50) null references T_User(UserName)

)
--订单明细表
create table T_OrderDetails
(
ProductID Varchar(50) not null references T_Product(ProductID),
OrderID Char(14) not null references T_OrderHead(OrderID),
OrderQty int not null,
SubTotal Decimal(18,2) not null
constraint PK_T_OrderDetails_ProductID_OrderID primary key(ProductID,OrderID)
)
提交订单时，应同时向这两张表中插入记录，要么成功，要么失败, 因此需要用到事务进行提交.

代码设计

1 生成订单编号
订单号生成规则：订单号由4位年号+2位月号+2位日期+6位流水号组成
private string GenOrderId() {
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["DHDBConnectionString"].ConnectionString);
try
{
conn.Open();
string today = string.Format("{0:yyyyMMdd}", DateTime.Now);
//从数据库中查询今日的订单中最新的订单6位流水号
string sql = string.Format("select isnull(right(max(OrderID),6),'000000') from T_OrderHead
where OrderID like '{0}%'", today);
SqlCommand cmd = new SqlCommand(sql, conn);
//新的订单流水号增加 1
int nid = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
//生成新的订单编号
string orderId = string.Format("{0}{1:000000}", today, nid);
return orderId;
}
catch (Exception ex) {
throw ex;
}
}

2 保存提交的订单

protected void btnCheckout_Click(object sender, EventArgs e) //结账
{
ShoppingCar car = (ShoppingCar)Session[ShoppingCar.ShoppingCarID];
//检查购物车中是否有商品
if(car==null || car.CarData.Count==0){
Utils.ShowMessage(this,"你的购物车中没有商品,请订购商器后再提交!");
return;
}
//将购物车中的商品保存到订单表
string orderId = GenOrderId();
string userId = "admin"; //得到登录的用户名，实在操作时应该将登录用户名放在Session中
int orderStatus = Utils.OrderStatus_Submitted;
float subTotals = float.Parse( lblTotals.Text);

SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["DHDBConnectionString"].ConnectionString);

SqlTransaction trans = null;
try
{
conn.Open();
trans = conn.BeginTransaction(); //开始事务

//在订单头表中插入一条记录
string sql = string.Format("INSERT T_OrderHead(OrderID,OrderStatus,SubTotal,UserName,SubmitTime)"+
" VALUES('{0}',{1},{2},'{3}',getdate())",orderId, orderStatus, subTotals, userId);
SqlCommand cmd = new SqlCommand(sql, conn, trans);
cmd.ExecuteNonQuery();

//将购物车中订购的商品信息添加到订单明细表
foreach (Product p in car.CarData)
{ //遍历购物车中的所有商品
sql = string.Format("INSERT T_OrderDetails(ProductID,OrderID,OrderQty,SubTotal)
VALUES('{0}','{1}',{2},{3})", p.ProductId, orderId, p.Qty, p.Price * p.Qty);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
trans.Commit(); //提交事务
car.Clear(); //清空购物车
Utils.ShowMessage(this, "订单已成功提交!");
}
catch (Exception ex)
{
trans.Rollback(); //回滚事务
Utils.ShowMessage(this, "提交失败!错误:" + ex.Message);
}
finally {
conn.Close();
}
}