在使用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;
}
}