求助高手:关于用sqlhelper实现稍复杂点的事务判断
小弟最近想用sqlhelper来替换一下原来的socut控件,其实主要是想用sqlhelper中的事务功能。现在有一个aspx.cs页面的判断逻辑比较多,我按如下处理了一下总是审核失败(原来没有tran事务的try...catch等代码时是可以正常审核成功的),不知道应该如何用sqlhelper来实现这种事务控制,还请各位指点指点,谢谢了。//审批子程序
public void Sp_Click(object Sender, EventArgs E)
{
using (SqlTransaction tran = BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, "update as_rkd01 set rkzt='已审批' where rkdid='" + Request.QueryString["id"] + "'", null);
}
catch
{
tran.Rollback();
throw;
}
Socut.Reader dr = new Socut.Reader("select a.wpid,a.rksl,a.rkdj,a.rksl*a.rkdj as rkje,b.rkck,b.rklx from as_rkd02 a left outer join as_rkd01 b on (a.ssrkdid=b.rkdid) where ssrkdid='" + Request.QueryString["id"].Trim() + "'");
while (dr.Read())
{
Socut.Reader dr3 = new Socut.Reader("select sswpid,sum(kcsl)as kczsl,(case when (sum(kcsl)+ " + dr["rksl"] + ")=0 then 'f' else 't' end)as kcbz from as_kc where sswpid='" + dr["wpid"] + "' and kcbs='" + dr["rklx"] + "' group by sswpid");
if (dr3.Read())
{
if (dr3["kcbz"].ToString() == "f")
{
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, "update as_wp set wpdj=" + dr["rkdj"] + " where wpid='" + dr["wpid"] + "'", null);
}
else
{
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, "update as_wp set wpdj=round(((" + dr3["kczsl"] + " * wpdj) + (" + dr["rksl"] + "*" + dr["rkdj"] + "))/(" + dr3["kczsl"] + " + " + dr["rksl"] + "),2) where wpid='" + dr["wpid"] + "'", null);
}
}
else
{
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, "update as_wp set wpdj=round(((0 * wpdj) + (" + dr["rksl"] + "*" + dr["rkdj"] + "))/(0 + " + dr["rksl"] + "),2) where wpid='" + dr["wpid"] + "'", null);
}
dr3.Close();
Socut.Reader dr2 = new Socut.Reader("select * from as_kc where ssckid='" + dr["rkck"] + "' and sswpid='" + dr["wpid"] + "' and kcbs='" + dr["rklx"] + "'");
if (dr2.Read())
{
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, "update as_kc set kcsl=kcsl + " + dr["rksl"] + ",kcje=kcje + " + dr["rkje"] + " where ssckid='" + dr["rkck"] + "' and sswpid='" + dr["wpid"] + "' and kcbs='" + dr["rklx"] + "'", null);
}
else
{
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, "insert into as_kc(ssckid,sswpid,kcsl,kcje,kcbs) values('" + dr["rkck"] + "','" + dr["wpid"] + "'," + dr["rksl"] + "," + dr["rkje"] + ",'" + dr["rklx"] + "')", null);
}
dr2.Close();
int ikc = (int)(SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction,CommandType.Text,"insert into as_kcmx(jzrq,ssckid,sswpid,ywlx,ywdh,jzsl,jzje) values(getdate(),'" + dr["rkck"] + "','" + dr["wpid"] + "','入库','" + Request.QueryString["id"] + "'," + dr["rksl"] + "," + dr["rkje"] + ")" + "select cast(scope_identity() as int)",null));
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, "update as_kcmx set jykcsl=(select sum(kcsl)as kcsl from as_kc where ssckid='" + dr["rkck"] + "' and sswpid='" + dr["wpid"] + "'),kcmxbs='" + dr["rklx"] + "' where kcmxid=" + ikc + "", null);
}
dr.Close();
tran.Commit();
ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('审批通过'); <" + "/script>");
}
}
public static SqlTransaction BeginTransaction(string con)
{
SqlConnection connection = new SqlConnection(con);
connection.Open();
SqlTransaction tran = connection.BeginTransaction();
return tran;
} --------------------编程问答-------------------- 我们做事务,都是事务类有一个SQL字符串列表,是静态的,在各个地方添加SQL语句,最后执行时做事务处理,失败后悔滚 --------------------编程问答-------------------- using(TransactionScope scope = new TransactionScope())
{
//方法一
scope.Commit();
}
--------------------编程问答-------------------- 程序这么写累不累啊。 --------------------编程问答-------------------- 看着这些拼接的SQL文就头疼啊 --------------------编程问答-------------------- wuyq11:请问TransactionScope比SqlTransaction的主要优势在哪呢 --------------------编程问答-------------------- 呵呵,不好意思。新手多多包含 --------------------编程问答-------------------- TransactionScope SqlTransaction --------------------编程问答--------------------
补充:.NET技术 , ASP.NET