数据库事务,是指单个逻辑工作单元执行的一系列操作,要么完全执行,要么完全不执行。
订餐中,购买了食物,食物库存减少了,库存的记录也应该被记录。要更新,要有记录。要不就更新不进去。
其实我也看的不是很懂,大概就是先获得事务,全部成功就提交,否则回滚,最后就是关闭连接。
我把代码给大家看看。
看一下b层代码
//购买零食添加记录进行库存量的减少 运用事务
#region 添加事务
public bool doubleaddsocklogupdataSockdetail(SockCost scost)
{
shopsock card=new shopsock();
DBTransactionBLL DbTran = new DBTransactionBLL();
//获得连接
SqlConnection conn = DbTran.GetConnection();
//开始事务
SqlTransaction trans = DbTran.GetTransaction(conn);
try
{
//填写充值记录
if (card.addsocklog(scost, conn, trans))
{
}
else
{
return false;
}
//填写零食表更新记录
if (card.updataSockdetail(scost, conn, trans))
{
}
else
{
return false;
}
//全部成功
DbTran.Commit(trans);
return true;
}
catch (Exception)
{
//事务回滚
DbTran.Rollback(trans);
return false;
}
finally
{
//关闭连接
DbTran.Close(conn);
}
}
看一下 DBTransactionBLL
public class DBTransactionBLL
{
private DBTransactionDAL DBTranDal = new DBTransactionDAL();
#region 获取数据库连接
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns>数据库连接</returns>
public SqlConnection GetConnection()
{
return DBTranDal.GetConnection();
}
#endregion
#region 获取事务
/// <summary>
/// 获取事务
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
public SqlTransaction GetTransaction(SqlConnection conn)
{
return conn.BeginTransaction();
}
#endregion
#region 提交事务
/// <summary>
/// 提交事务
/// </summary>
public void Commit(SqlTransaction sqlTransaction)
{
sqlTransaction.Commit();
}
#endregion
#region 回滚事务
/// <summary>
/// 回滚事务
/// </summary>
public void Rollback(SqlTransaction sqlTransaction)
{
sqlTransaction.Rollback();
}
#endregion
#region 关闭连接
/// <summary>
/// 关闭连接
/// </summary>
public void Close(SqlConnection conn)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
#endregion
}
看一下DBTransactionDAL
public class DBTransactionDAL
{
private SQLHelper helper = null;
#region 构造方法
/// <summary>
/// 构造方法
/// </summary>
public DBTransactionDAL()
{
helper = new SQLHelper();
}
#endregion
#region 获取数据库连接
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns></returns>
public SqlConnection GetConnection()
{
return helper.GetConn ();
}
#endregion
}
看一下d层
#region 购买零食后进行添加记录
public bool addsocklog(SockCost scost,SqlConnection conn,SqlTransaction trans)
{
bool flag = false;
string cmdtext = "insert into SockCost (SockID,cardId,Sockcount,Sockmoney,Socktime,Sockwrite,Sockhistory) values(@SockID,@cardId,@Sockcount,@Sockmoney,@Socktime,@Sockwrite,@Sockhistory)";
SqlParameter[] paras = new SqlParameter[]{
new SqlParameter("@SockID",scost.SockID),
new SqlParameter("@cardId",scost.cardId),
new SqlParameter("@Sockcount",scost.Sockcount ),
new SqlParameter("@Sockmoney",scost.Sockmoney ),
new SqlParameter ("@Socktime",scost.Socktime ),
new SqlParameter ("@Sockwrite",scost.Sockwrite ),
new SqlParameter ("@Sockhistory",scost.Sockhistory)
};
int res = SQLHelper.ExecuteSqlRow(cmdtext.ToString(), conn, trans, paras);
if (res>0)
{
flag = true;
}
return flag;
}
#endregion
#region 购买零食后进行库存量的减少
public bool updataSockdetail(SockCost scost, SqlConnection conn, SqlTransaction trans)
{
bool flag = false;
string cmdtext = "update Sockdetail set Sockremainnumber=Sockremainnumber - @Sockcount where SockID=@SockID";
SqlParameter[] paras = new SqlParameter[]{
new SqlParameter("@SockID",scost.SockID),
//new SqlParameter("@cardId",scost.cardId),
new SqlParameter("@Sockcount",scost.Sockcount ),
//new SqlParameter("@Sockmoney",scost.Sockmoney ),
//new SqlParameter ("@Socktime",scost.Socktime ),
//new SqlParameter ("@Sockwrite",scost.Sockwrite ),
//new SqlParameter ("@Sockhistory",scost.Sockhistory)
};
int res = SQLHelper.ExecuteSqlRow(cmdtext.ToString(), conn, trans, paras);
if (res>0)
{
flag = true;
}
return flag;
}
#endregion
事务是有特殊的sqlhelper类。
public static int ExecuteSqlRow(string SQLString, SqlConnection conn, SqlTransaction trans, params SqlParameter[] cmdParms)
{
using (SqlCommand cmd = new SqlCommand()) //实例化SqlCommand,并在此范围内结束时处理对象
{
try
{
PrepareCommand(cmd, conn, trans, SQLString, cmdParms); //设置SQL语句
int rows = cmd.ExecuteNonQuery(); //执行非查询操作,并返回受影响行数
cmd.Parameters.Clear(); //清理参数
return rows; //受影响的行数
}
catch (System.Data.SqlClient.SqlException e)
{
throw e; //抛出异常
}
}
}
我在执行完事务一的时候,在事务二设置断点。这时刷新数据库,数据库什么数据也不显示。
谢谢大家的建议。