[玻璃鱼V]关于事务处理

Question

我有几条对数据库的处理需要用到事务处理，当一组事务出错的时候能够报错自动回滚。但是我对事务处理的东西不太了解，或者有比使用事务更灵活的方法，大家来教导下.代码片段：protected void datalistOfSubjects_DeleteCommand(object source, DataListCommandEventArgs e)
{
    voteSubject.RemoveVoteSubject(e.CommandArgument.ToString());                //根据投票分类ID删除分类
    voteItems.RemoveVoteItemsBySubjectId(e.CommandArgument.ToString());         //根据投票分类ID删除投票项目
    voteRecords.RemoveVoteRecordBySubjectId(e.CommandArgument.ToString());      //根据投票分类ID删除投票记录    bindData();
}其中有注释的3条语句需要作为一组事务进行处理，该怎么写呢？

Answer 1

           SqlConnection connection = new SqlConnection("");
            connection.Open();
            SqlTransaction transaction = connection.BeginTransaction();
            SqlCommand command = connection.CreateCommand();
            try
            {
command.CommandText = "";
            command.ExecuteNonQuery();
            command.CommandText = "";
            command.ExecuteNonQuery();
                //...
                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
            }
                command.Dispose();
            transaction.Dispose();
                connection.Close();
                connection.Dispose();

Answer 2

事务处理一定要实例化SqlConnection么？那在多层架构开发中如何利用事务呢？

Answer 3

不实例话SqlConnection 如何出来事务呢？多层架构的话。那就单独放一个连接数据库DBConn类型.比如是DAL层那就using DAL;然后DBConn conn = new DBConn();然后再conn.BeginTransaction();

Answer 4

去看看System.Transactions命名空间...不要狭隘地把目光只盯在数据库级别...
using(System.Transactions.TransactionScope)
{
//事务性代码...
}

Answer 5

更正...
using(System.Transactions.TransactionScope scope=new System.Transactions.TransactionScope())
{
//事务性代码...
}

Answer 6

String SQL_INSERT_BUSINFO = @"insert into Z_BusInfo(LuxCode,LuxName,QiD,ZhongD,CityCode,PassBy,PassBack,Description,AddUser)values(
                          @LuxCode,@LuxName,@QiD,@ZhongD,@CityCode,@PassBy,@PassBack,@Description,@AddUser) ";
        using (SqlConnection con = new SqlConnection(Dragons.DBUtility.SQLHelper.ConnectionStringLocalTransaction))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            SqlTransaction tx = con.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                SqlParameter[] parm = new SqlParameter[] 
                                {
                                    new SqlParameter("@LuxCode",SqlDbType.VarChar,50),
                                    new SqlParameter("@LuxName",SqlDbType.VarChar,50),
                                    new SqlParameter("@QiD",SqlDbType.VarChar,50),
                                    new SqlParameter("@ZhongD",SqlDbType.VarChar,50),
                                    new SqlParameter("@CityCode",SqlDbType.VarChar,50),
                                    new SqlParameter("@PassBy",SqlDbType.VarChar,1000),
                                    new SqlParameter("@PassBack",SqlDbType.VarChar,1000),
                                    new SqlParameter("@Description",SqlDbType.VarChar,1000),
                                    new SqlParameter("@AddUser",SqlDbType.Int,4)
                                };
                parm[0].Value = BusInfo.LuxCode;
                parm[1].Value = BusInfo.LuxName;
                parm[2].Value = BusInfo.QiD;
                parm[3].Value = BusInfo.ZhongD;
                parm[4].Value = BusInfo.CityCode;
                parm[5].Value = BusInfo.PassBy;
                parm[6].Value = BusInfo.PassBack;
                parm[7].Value = BusInfo.Description;
                parm[8].Value = BusInfo.AddUser;
                SqlParameter[] parm1 = new SqlParameter[] 
                {
                    new SqlParameter("@zname",SqlDbType.VarChar,1000),
                    new SqlParameter("@jp",SqlDbType.VarChar,1000),
                    new SqlParameter("@qp",SqlDbType.VarChar,1000),
                    new SqlParameter("@citycode",SqlDbType.VarChar,50),
                    new SqlParameter("@lxid",SqlDbType.Int,4)
                };
                parm1[0].Value = zname.ZDname;
                parm1[1].Value = zname.JP;
                parm1[2].Value = zname.PY;
                parm1[3].Value = zname.CityCode;
                parm1[4].Value = 0;
                if (Dragons.DBUtility.SQLHelper.ExecuteNonQuery(Dragons.DBUtility.SQLHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_INSERT_BUSINFO,parm) < 0)
                {
                    tx.Rollback(); return "false";
                }
                if (Dragons.DBUtility.SQLHelper.ExecuteNonQuery(Dragons.DBUtility.SQLHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "getpart",parm1) < 0)
                {
                    tx.Rollback(); return "false";
                }
                return "true";
            }
            catch
            {
                tx.Rollback();
                return "false";
            }
        }

Answer 7

2 直接sqlString SQL_INSERT_BUSINFO = "insert into Z_BusInfo(LuxCode,LuxName,QiD,ZhongD,CityCode,PassBy,PassBack,Description,AddUser)values(@LuxCode,@LuxName,@QiD,@ZhongD,@CityCode,@PassBy,@PassBack,@Description,@AddUser) ";
        String SQL_INSERT_ZNAME = "insert into Z_Zname(LxId,CityCode,Zname,PY,JP)select @IDS, @cityid,str_name,PY,JP from StrSplit(@znamelist,'-',@jp,@qp,@cityids) where str_name not in (select Zname from Z_Zname)";
        System.Text.StringBuilder sp = new StringBuilder();
        SqlCommand cmd = new SqlCommand();
        sp.Append("declare @err int;set @err=0;declare @IDS int;");
        sp.Append(SQL_INSERT_BUSINFO).Append(" SELECT @IDS=SCOPE_IDENTITY();").Append(" select @err=@err+@@error;");
        try
        {
            SqlParameter[] parm = new SqlParameter[] 
                                {
                                    new SqlParameter("@LuxCode",SqlDbType.VarChar,50),
                                    new SqlParameter("@LuxName",SqlDbType.VarChar,50),
                                    new SqlParameter("@QiD",SqlDbType.VarChar,50),
                                    new SqlParameter("@ZhongD",SqlDbType.VarChar,50),
                                    new SqlParameter("@CityCode",SqlDbType.VarChar,50),
                                    new SqlParameter("@PassBy",SqlDbType.VarChar,1000),
                                    new SqlParameter("@PassBack",SqlDbType.VarChar,1000),
                                    new SqlParameter("@Description",SqlDbType.VarChar,1000),
                                    new SqlParameter("@AddUser",SqlDbType.Int,4)
                                };
            parm[0].Value = BusInfo.LuxCode;
            parm[1].Value = BusInfo.LuxName;
            parm[2].Value = BusInfo.QiD;
            parm[3].Value = BusInfo.ZhongD;
            parm[4].Value = BusInfo.CityCode;
            parm[5].Value = BusInfo.PassBy;
            parm[6].Value = BusInfo.PassBack;
            parm[7].Value = BusInfo.Description;
            parm[8].Value = BusInfo.AddUser;
            foreach (SqlParameter p in parm)
            {
                cmd.Parameters.Add(p);
            }
            SqlParameter[] ps = new SqlParameter[] 
                                {
                                    new SqlParameter("@cityid",SqlDbType.VarChar,50),
                                    new SqlParameter("@znamelist",SqlDbType.VarChar,1000),
                                    new SqlParameter("@jp",SqlDbType.VarChar,1000),
                                    new SqlParameter("@qp",SqlDbType.VarChar,1000),
                                    new SqlParameter("@cityids",SqlDbType.VarChar,50)
                                };
            ps[0].Value = zname.CityCode;
            ps[1].Value = zname.ZDname;
            ps[2].Value = zname.JP;
            ps[3].Value = zname.PY;
            ps[4].Value = ps[0].Value;
            foreach (SqlParameter item in ps)
            {
                cmd.Parameters.Add(item);
            }
            using (SqlConnection con = new SqlConnection(Dragons.DBUtility.SQLHelper.ConnectionStringLocalTransaction))
            {
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = sp.Append(SQL_INSERT_ZNAME).Append(" select @err").ToString();
                using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    sdr.Read();
                    if (sdr.GetInt32(0) != 0)
                    {
                        return "DATA INTEGRITY ERROR ON ORDER INSERT - ROLLBACK ISSUED";
                    }
                }
                cmd.Parameters.Clear();
                return "true";
            }
        }
        catch
        {
            return "";
        }

Answer 8

或者直接使用sql：
SET XACT_ABORT ON 
begin transaction 
处理语句。
commit transaction 
go

Answer 9

using (TransactionScope tsCope = new TransactionScope())
{
　　using (SqlConnection conn1= new SqlConnection(""))
　　{
　　　　SqlCommand cmd = new SqlCommand("", conn1);
　　　　conn1.Open();
　　　　cmd.ExecuteNonQuery();
　　}
　　using (SqlConnection conn2= new SqlConnection(""))
　　{
　　　　SqlCommand cmd = new SqlCommand("", conn2);
　　　　conn2.Open();
　　　　cmd.ExecuteNonQuery();
　　}
　　tsCope.Complete();
}

Answer 10

呵呵 上面的方法应该不是楼主需要的事物的问题，楼主可以参考下微软封装的sqlhelp类把事物对象作为一个对象进行传递，在不同的方法里公用一个事物就可以了

Answer 11

额， LZ能说得具体点么，有实例最好。using(System.Transactions.TransactionScope scope=new System.Transactions.TransactionScope())
{
//事务性代码...
}这样是不是可以将任何语句（非数据库操作语句）都作为一组事务进行处理？呵呵，今天在忙其他事，没时间进行调试，还望大家多指导