C# 事物控制

1.本地事物

即一个Connection并连接到单一的数据库中。用法如下:

同步事物：

static void LocalTranslate()
        {
            using (SqlConnection conn = new SqlConnection(args[0]))
            {
                conn.Open();
                string sql1 = "update Table1 set column1='NewValue1' where ID=0";
                string sql2 = "Insert into Table2 values(18,'rt','ghfg')";

                SqlTransaction sqlTransaction = conn.BeginTransaction();  //开启本地事物
                SqlCommand cmd = conn.CreateCommand();

                cmd.Transaction = sqlTransaction; //指定Command事物 
                try
                {
                    cmd.CommandText = sql1;
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = sql2;
                    cmd.ExecuteNonQuery();

                    sqlTransaction.Commit();  //提交事物
                }
                catch (Exception ex)
                {
                    sqlTransaction.Rollback();//回滚事物                   
                }
            }
        }

异步事物：

static async Task LocalTransactionAsync(string connectionString)
        {
            using(SqlConnection conn=new SqlConnection(connectionString))
            {
                await conn.OpenAsync();
                SqlCommand cmd=conn.CreateCommand();
                SqlTransaction trans = await Task.Run<SqlTransaction>(()=>conn.BeginTransaction());//指定事物
                cmd.Connection= conn;
                cmd.Transaction= trans;

                try
                {
                    cmd.CommandText = "Insert...";
                    await cmd.ExecuteNonQueryAsync();

                    cmd.CommandText = "Update...";
                    await cmd.ExecuteNonQueryAsync();

                    await Task.Run(()=>trans.Commit());//提交事物
                }catch(Exception ex)
                {  
                      trans.Rollback();//回滚事物
                }
            }

        }

static void InvokeLocalTrans()
        {
            SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder();
            connStr.DataSource = "VMH765";
            connStr.InitialCatalog = "Northwind";
            connStr.IntegratedSecurity = true;

            Task result=LocalTransactionAsync(connStr.ConnectionString);
            result.Wait();
        }

 

2.分布式事物

即多个Connection并连接到不同的DB中，比如一个Connection连接MSSQL,另一个Connection连接到Oracle。需用到TranscationScope,它会自动提升为分布式事物，并且不用特别指定Connection及Command事物名称，

只要这些操作在TranscationScop块中。在执行完后调用Complete方法即可，如果之前遇到Exception就会自动回滚事物并不会再调用Complete方法。

同步事物：

static void DistributeTranslate()
        {
            //需引用System.Transaction.dll并using,开启分布式事物
            using (TransactionScope scope = new TransactionScope()) 
            {
                using(SqlConnection conn1=new SqlConnection("Server1Connection")) //指定第一个DB库的连接
                {
                    
                    try
                    {
                        conn1.Open();//打开连接会自动将其登记到TransactionScope作为轻量级事务。
                        SqlCommand cmd1 = conn1.CreateCommand(); 
                        cmd1.CommandText = "Insert into server1.dbo.table values(...) ";
                        cmd1.ExecuteNonQuery();

                        using (SqlConnection conn2 = new SqlConnection("Server2Connection"))//指定第二个DB库的连接,要嵌套到第一个语句块中
                        {
                            conn2.Open(); //打开第二个连接时事务被提升为完全分布式。
                            SqlCommand cmd2 = conn2.CreateCommand();
                            cmd2.CommandText = "Update server2.dbo.table2...";
                            try
                            {
                                cmd2.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                //log the ex.message
                            }
                        }
                    }
                    catch(Exception ex)
                    {
                        // log the ex.message，不用调用return退出就会自动回滚
                    }                   

                }

       
                //如果引发了异常，则Complete不会被调用，并且事务被回滚。
                scope.Complete() ;
            }

 异步事物：

   需加载System.Transactions.dll并using System.Transactions

static async Task DistributedTransactionAsync(string connStr1,string connStr2)
        {
            using(SqlConnection conn1=new SqlConnection(connStr1))
            using(SqlConnection conn2=new SqlConnection(connStr2))
            {
               using(CommittableTransaction trans=new CommittableTransaction()) //创建事物
                {
                    await conn1.OpenAsync();
                    conn1.EnlistTransaction(trans); //指定分布式事物

                    await conn2.OpenAsync();
                    conn2.EnlistTransaction(trans);//指定分布式事物

                    try
                    {
                        SqlCommand cmd1= conn1.CreateCommand();
                        cmd1.CommandText = "Insert ...";
                        await cmd1.ExecuteNonQueryAsync();

                        SqlCommand cmd2= conn2.CreateCommand();
                        cmd2.CommandText = "Update...";
                        await cmd2.ExecuteNonQueryAsync();

                        trans.Commit(); //提交事物 

                    }catch(Exception ex)
                    {
                        trans.Rollback();//回滚事物
                    }
                }
            }

        }

static void InvokeDistributedTransaction()
        {
            SqlConnectionStringBuilder connStr1 = new SqlConnectionStringBuilder();
            connStr1.DataSource = "VMH765";
            connStr1.InitialCatalog = "Northwind";
            connStr1.IntegratedSecurity = true;

            SqlConnectionStringBuilder connStr2 = new SqlConnectionStringBuilder();
            connStr2.DataSource = "VMH765";
            connStr2.InitialCatalog = "testVM";            
            connStr2.IntegratedSecurity = true;

            Task result = DistributedTransactionAsync(connStr1.ConnectionString, connStr2.ConnectionString);
            result.Wait();
        }