C# SqlConnection.EnlistTransaction方法代码示例

        public ActionResult Index()
        {
            string strConnStr = ConfigurationManager.ConnectionStrings["HirenTestConnectionStr"].ConnectionString;

            //Dapper test - Starts
            using (var connection = new SqlConnection(strConnStr))
            {
                connection.Open();

                //                //insert
                //                connection.Execute("insert into Person (Name,Address,PhoneNo) values(@Name,@Address,@PhoneNo)", param: new { Name = "TestPerson", Address = "TestAddress", PhoneNo = "TestPhoneNo" });
                //
                //
                //                //bulk insert test
                //                connection.Execute("insert into Person (Name,Address,PhoneNo) values(@Name,@Address,@PhoneNo)", param: new List<Person> {
                //                    new Person{ Name="BulkPerson1", PhoneNo = "BulkPhoneNo", Address="BulkAddress" },
                //                    new Person{ Name="BulkPerson2", PhoneNo = "BulkPhoneNo", Address="BulkAddress" },
                //                    new Person{ Name="BulkPerson3", PhoneNo = "BulkPhoneNo", Address="BulkAddress" },
                //                    new Person{ Name="BulkPerson4", PhoneNo = "BulkPhoneNo", Address="BulkAddress" }
                //                });

                //Get Data from person
                var persons = connection.Query<Person>("select * from Person");

                //Get Data From Person Note
                var personNote = connection.Query<PersonNote>("select * from PersonNote");

                //Get Data From storedProcedure
                var PersonNoteFrmStoredProc = connection.Query<PersonNote>("[GetPersonNote]", param: new { strSearch = "note 1" }, commandType: CommandType.StoredProcedure);

                //Join Example

                /*
                 var sql =  @"select * from #Posts p
                        left join #Users u on u.Id = p.OwnerId
                        Order by p.Id";

                        var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
                        var post = data.First();
                 */

                // var sql = @"select * from PersonNote as pn inner join Person as p on pn.PersonID = pn.ID";
                // var joinPersonNote = connection.Query<Person, PersonNote, PersonNote>(sql, (per, perNot) => { perNot.person = per; return perNot; });
                // var joinPersonNote1 = connection.Query<Person, PersonNote, PersonNote>(sql,splitOn: "PersonID" );

                //Transaction with dapper starts

                try
                {

                    using (var transactionScope = new TransactionScope(TransactionScopeOption.Required))
                    {
                        connection.EnlistTransaction(Transaction.Current);

                        for (int i = 0; i < 10; i++)
                        {
                            try
                            {
                                connection.Execute("insert into Person (Name,Address,PhoneNo) values(@Name,@Address,@PhoneNo)", param: new { Name = "TestTransaction" + i.ToString(), Address = "TestAddress", PhoneNo = "TestPhoneNo" });

                                //if i == 7 raise error
                                if (i == 7)
                                {
                                    throw new Exception("Erorr.... Can't go ahead...");
                                }
                            }
                            catch (Exception)
                            {
                                Transaction.Current.Rollback();
                                break;
                            }
                        }
                        transactionScope.Complete();
                        transactionScope.Dispose();

                    }
                }
                catch (TransactionAbortedException ex)
                { }
                catch (Exception)
                { }

                //Transaction with dapper ends

            }

            //Dapper test - Ends

            ViewBag.Message = "Modify this template to jump-start your ASP.NET MVC application.";

            return View();
        }

 public SqlConnection GetConnection(CommittableTransaction transaction = null)
 {
     if (Connection.IsNull())
     {
         Connection = new SqlConnection(Runtime.Context.GetDefaultConnectionString());
         if (transaction.IsInstance())
         {
             Connection.Open();
             Connection.EnlistTransaction(transaction);
         }
     }
     return Connection;
 }

        public override ManagedConnection Connect()
        {
            Action complete = () => { };
            Action dispose = () => { numberOfManagedConnections--; };

            try
            {
                if (Transaction.Current == null)
                {
                    var tx = new TransactionScope(
                        TransactionScopeOption.RequiresNew,
                        new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted });

                    complete += tx.Complete;
                    dispose += tx.Dispose;
                }

                var connection = new SqlConnection(connectionString);
                connection.InfoMessage += (obj, args) => OnMessage(args);
                connection.Open();
                connection.ChangeDatabase("tempdb");

                complete = connection.Dispose + complete;
                dispose = connection.Dispose + dispose;

                connection.EnlistTransaction(Transaction.Current);

                numberOfManagedConnections++;

                return new ManagedConnection(connection, complete, dispose);
            }
            catch (Exception)
            {
                dispose();
                throw;
            }
        }

        private void button84_Click(object sender, EventArgs e)
        {
            CommittableTransaction tx = null;
            try
            {

                using (tx = new CommittableTransaction())
                {
                    using (SqlConnection conn1 = new SqlConnection(Properties.Settings.Default.NorthwindConnectionString))
                    {

                        using (SqlCommand myCommand = new SqlCommand())
                        {

                            myCommand.Connection = conn1;

                            conn1.Open();
                            conn1.EnlistTransaction(tx);


                            //Restore database to near it's original condition so sample will work correctly.
                            myCommand.CommandText = "DELETE FROM Region WHERE (RegionID = 100) OR (RegionID = 101)";
                            myCommand.ExecuteNonQuery();

                            //Insert the first record.
                            myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'MidWestern')";
                            myCommand.ExecuteNonQuery();


                            //測試交易失敗用
                            //Insert the first record.
                            myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'MidWestern')";
                            myCommand.ExecuteNonQuery();


                            //Insert the second record.
                            myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'MidEastern')";
                            myCommand.ExecuteNonQuery();


                            tx.Commit();

                            MessageBox.Show("trans. Successfully");
                        }
                    }
                }

            }


            catch (TransactionException ex)
            {
                MessageBox.Show(ex.Message);
                tx.Rollback();
            }
            catch (Exception ex)
            {

                MessageBox.Show("trans. roll back " + ex.Message);
            }
        }

        /// <summary>
        /// Executes sql
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sql"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="dtx"></param>
        /// <returns></returns>
        private static Exception ExecuteSql(string connectionString, string sql,IList<SqlParameter> parameters,out string output, int commandTimeout, DependentTransaction dtx)
        {
            Exception exception = null;
            output = string.Empty;
            try
            {
                if (dtx != null)
                {
                    //if transaction - execute in transaction scope
                    using (TransactionScope scope = new TransactionScope(dtx))
                    {
                        using (SqlConnection conn = new SqlConnection(connectionString))
                        {
                            //Make sure transaction is placed in DTC
                            IDtcTransaction t = TransactionInterop.GetDtcTransaction(Transaction.Current);
                            conn.Open();
                            int retryCount = 120;
                            while (retryCount > 0)
                            {
                                try
                                {
                                    //Enlist to transaction. Will retry, because sometimes SQL server
                                    //reports that transaction is used by another session.
                                    //This happens not very often, but this is a protection
                                    conn.EnlistTransaction(TransactionInterop.GetTransactionFromDtcTransaction(t));
                                    break;
                                }
                                catch
                                {
                                    retryCount--;
                                    if (retryCount == 0)
                                        throw;
                                    Thread.Sleep(500);
                                }
                            }
                            using (SqlCommand cmd = conn.CreateCommand())
                            {
                                ExecuteSql(cmd, sql, parameters, out output, commandTimeout);
                            }
                        }
                        //commit if success
                        scope.Complete();
                    }
                }
                else
                {
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        conn.Open();
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            //if not transactional, then just execute sql
                            ExecuteSql(cmd, sql, parameters, out output, commandTimeout);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex;
            }

            return exception;
        }

        public void DbContext_can_be_initialized_without_promotion_to_distributed_transaction_inside_transaction_scope_if_the_context_type_has_been_previously_initialized_outside()
        {
            var connectionString = default(string);
            using (var context = new TransactionTestsContext())
            {
                // force context initialization
                ExtendedSqlAzureExecutionStrategy.ExecuteNew(() => context.Entities.Count());
                connectionString = context.Database.Connection.ConnectionString;
            }

            // this only works for integrated security, or when password is persisted after connecting
            // otherwise we can't connect to database during context initialization (password is gone from connection string)
            if (DatabaseTestHelpers.IsIntegratedSecutity(connectionString) ||
                DatabaseTestHelpers.PersistsSecurityInfo(connectionString))
            {

                ExtendedSqlAzureExecutionStrategy.ExecuteNew(
                    () =>
                    {
                        using (new TransactionScope())
                        {
                            using (var connection = new SqlConnection(connectionString))
                            {
                                connection.Open();
                                connection.EnlistTransaction(Transaction.Current);
                                using (var context = new TransactionTestsContext(connection, false))
                                {
                                    context.Entities.Count();
                                }
                            }
                        }
                    });
            }
        }

        public void DbContext_can_be_initialized_without_promotion_to_distributed_transaction_inside_transaction_scope_if_the_context_type_has_been_previously_initialized_outside()
        {
            var connectionString = default(string);
            using (var context = new TransactionTestsContext())
            {
                // force context initialization
                context.Entities.Count();
                connectionString = context.Database.Connection.ConnectionString;
            }

            using (new TransactionScope())
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    connection.EnlistTransaction(Transaction.Current);
                    using (var context = new TransactionTestsContext(connection, false))
                    {
                        context.Entities.Count();
                    }
                }
            }
        }

        public static void ListenerThreadProc(object queueListenerConfig)
        {
            QueueListenerConfig config = (QueueListenerConfig) queueListenerConfig;
              while (!stopping)
              {
            TransactionOptions to = new TransactionOptions();
            to.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
            to.Timeout = TimeSpan.MaxValue;

            CommittableTransaction tran = new CommittableTransaction(to);

            try
            {

              using (var con = new SqlConnection(config.ConnectionString))
              {
            con.Open();
            con.EnlistTransaction(tran);
            byte[] message = ServiceBrokerUtils.GetMessage(config.QueueName, con, TimeSpan.FromSeconds(10));
            if (message == null) //no message available
            {
              tran.Commit();
              con.Close();
              continue;
            }

            try
            {
              if (config.EnlistMessageProcessor)
              {
                using (var ts = new TransactionScope(tran))
                {
                  config.MessageProcessor(message);
                  ts.Complete();
                }
              }
              else
              {
                config.MessageProcessor(message);
              }

            }
            catch (SqlException ex) //catch selected exceptions thrown by the MessageProcessor
            {
              config.FailedMessageProcessor(message, con, ex);
            }

            tran.Commit(); // the message processing succeeded or the FailedMessageProcessor ran so commit the RECEIVE
            con.Close();

              }
            }
            catch (SqlException ex)
            {
              System.Diagnostics.Trace.Write("Error processing message from " + config.QueueName + ": " + ex.Message);
              tran.Rollback();
              tran.Dispose();
              Thread.Sleep(1000);
            }
              ///catch any other non-fatal exceptions that should not stop the listener loop.
            catch (Exception ex)
            {
              Trace.WriteLine("Unexpected Exception in Thread Proc for " + config.QueueName + ".  Thread Proc is exiting: " + ex.Message);
              tran.Rollback();
              tran.Dispose();
              return;
            }

              }
        }