C#中事务的操作

在使用C#开发中，我们经常对数据库操作，所以会用到事务。前面的文章中我写了使用FreeSql框架如何使用事务，今天我们说一下在C#中如果是使用的原生SQL怎么使用事务。

如何使用事务

1.创建
SqlTransanction wmsin = conn.BeginTransaction(); 
创建事务对象，需要注意的是我们创建的时候必须保证数据库是打开的，也就是在Open下才可以创建。

2.挂载
cmd.Transaction = wmsin;
把事务对象挂到命令对象上，让命令执行带有事务的功能。

执行完挂载后，我们对数据进行增删改的操作。然后是执行完操作后，当成功的话我们提交事务，当失败的话，我们回滚事务。
成功： wmsin.Commit();//提交
失败：wmsin.Rollback();//回滚

举个实际上的例子：

public void chengpinin()
        {
            String connstrWMS = ConfigurationManager.ConnectionStrings["ConnectionStringWms"].ConnectionString;//wms数据库
            String connstrInter = ConfigurationManager.ConnectionStrings["ConnectionStringInter"].ConnectionString;//中间库
            SqlConnection connInter = new SqlConnection(connstrInter);//中间库
            SqlConnection connWms = new SqlConnection(connstrWMS);//wms数据库
            string batch_no = "";string quantity = "";string remark = "";string flag = "";
            string matno ="";
            string lotno = "";
            string demo1 = "";
            string demo2 = "";
            int AFlag = 0;
            try
            {
                DataSet ds_aimer_to_wms_inter = new DataSet();
                DataSet ds_aimer_to_wms_inter2 = new DataSet();
                DataSet wms = new DataSet();
                connInter.Open();
                DataSet ds = new DataSet();
                string sql = string.Empty;
                sql = sql + " select * from A_FG_PORT_ENTYR where flag = '0'   ";
                ds_aimer_to_wms_inter = Getdata_bymemo(sql);
                connInter.Close();
                if (ds_aimer_to_wms_inter.Tables[0].Rows.Count <= 0)
                {
                    return;
                }
                connInter.Open(); 
                connWms.Open();
                //循环中间库数据库数据
                for (int j = 0; j < ds_aimer_to_wms_inter.Tables[0].Rows.Count; j++)
                {
                    batch_no = ds_aimer_to_wms_inter.Tables[0].Rows[j]["batch_no"].ToString();
                    quantity = ds_aimer_to_wms_inter.Tables[0].Rows[j]["quantity"].ToString();
                    remark = ds_aimer_to_wms_inter.Tables[0].Rows[j]["remark"].ToString();
                    flag = ds_aimer_to_wms_inter.Tables[0].Rows[j]["flag"].ToString();
                   
                    string sql2 = string.Empty;
                    sql2 = sql2 + " select * from A_FG_PORT_ENTYR_BOX where batch_no = '"+batch_no+"'";
                    ds_aimer_to_wms_inter2 = Getdata_bymemo(sql2); 
                    //这就是第一步 先创建
                    SqlTransaction sqlwmsTran = connWms.BeginTransaction();//创建添加事务
                    SqlTransaction sqlmesTran = connInter.BeginTransaction();//创建修改事务
                    //
                    if (ds_aimer_to_wms_inter2.Tables[0].Rows.Count != 0)
                    {
                        for (int y = 0; y < ds_aimer_to_wms_inter2.Tables[0].Rows.Count; y++)
                        {
                            string BARCODE = ds_aimer_to_wms_inter2.Tables[0].Rows[y]["BARCODE"].ToString();
                            matno = BARCODE.Substring(6, 2);
                            lotno = BARCODE.Substring(8, 2);
                        }
                        string add = "insert into log_erp_in(trfno,ORDNO,ITMNO,RECTYPE,MATNO,MUNIT,QUANT,QUANT0," +
                            "JIAN,CFLAG,BATCH,LOTNO,SUPPLY,WORKS,STORE,SECTN,PRDAT,QUDAT,PONO,POITEM,STYPE,ATTACHMENT," +
                            "VCDSCR,PRICE,KEEPER,STATU,TKDAT,COMDAT,OPUSER,USERID,DEMO1,DEMO2,DEMO3,DEMO8) " +
                            "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}'," +
                            "'{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}')";
                        add = string.Format(add, batch_no, batch_no, batch_no.Remove(0, batch_no.Length - 4), "1", matno, "1", quantity, "0.000000", "1", "1",
                            batch_no, lotno, "604", "22", "604", "G", DateTime.Now.ToString(), DateTime.Now.ToString(), "-", "-", "-", "-", "-", "1",
                            "", "", DateTime.Now, Convert.ToDateTime("2050-01-01 00:00:00.000"), "a", "a", "ALL", "ALL", "-", "erp");
                        SqlCommand cmd = new SqlCommand(add, connWms);
                        //这里是第二步  挂载
                        cmd.Transaction = sqlwmsTran;//把事务对象挂到命令对象上，让命令执行带有事务的功能
                        //
                        if (cmd.ExecuteNonQuery() <= 0)
                        {
                            AFlag = AFlag + 1;//如果添加失败 
                        }    
                    }
                    string updatezt = "update A_FG_PORT_ENTYR set FLAG = 1 where BATCH_NO = '" + batch_no + "'";
                    SqlCommand cmd2 = new SqlCommand(updatezt, connInter);
                    cmd2.Transaction = sqlmesTran;
                    if (cmd2.ExecuteNonQuery() <= 0)
                    {
                        AFlag = AFlag + 1; //修改失败 
                        LogHelper log = new LogHelper("LogTest");
                        log.WriteLine("入库状态修改失败，sql语句：" + updatezt);
                    }
                    if (AFlag == 0)
                    {
                    //执行成功后 我们提交事务
                        sqlwmsTran.Commit();
                        sqlmesTran.Commit();
                   //
                    }
                    else
                    { 
                    //执行失败后 我们回滚事务
                        sqlwmsTran.Rollback();
                        sqlmesTran.Rollback();
                    //
                    }
                }
                connWms.Close(); 
                connInter.Close();
            }
            catch (Exception ex)
            {
                LogHelper log = new LogHelper("LogTest");
                log.WriteLine("入库异常：" + ex.Message);
                string a = ex.Message;
                connInter.Close();
                connWms.Close();
                connInter = null;
                connstrWMS = null;
            }
        }