C# SqlConnection.BeginTransaction方法代码示例

        private void BasicParallelTest(string connectionString, string tempTableName)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlTransaction trans1 = connection.BeginTransaction();
                SqlTransaction trans2 = connection.BeginTransaction();
                SqlTransaction trans3 = connection.BeginTransaction();

                SqlCommand com1 = new SqlCommand("select top 1 au_id from " + tempTableName, connection);
                com1.Transaction = trans1;
                com1.ExecuteNonQuery();

                SqlCommand com2 = new SqlCommand("select top 1 au_id from " + tempTableName, connection);
                com2.Transaction = trans2;
                com2.ExecuteNonQuery();

                SqlCommand com3 = new SqlCommand("select top 1 au_id from " + tempTableName, connection);
                com3.Transaction = trans3;
                com3.ExecuteNonQuery();

                trans1.Rollback();
                trans2.Rollback();
                trans3.Rollback();

                com1.Dispose();
                com2.Dispose();
                com3.Dispose();
            }
        }

        public static void TestMain()
        {
            string connstr = (new SqlConnectionStringBuilder(DataTestUtility.TcpConnStr) { MultipleActiveResultSets = true }).ConnectionString;
            string cmdText1 = "select * from Orders; select count(*) from Customers";
            string cmdText2 = "select * from Customers; select count(*) from Orders";

            using (var conn = new SqlConnection(connstr))
            {
                conn.Open();

                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    SqlCommand cmd1 = conn.CreateCommand();
                    cmd1.Transaction = tran;
                    cmd1.CommandText = cmdText1;

                    using (SqlDataReader reader1 = cmd1.ExecuteReader())
                    {

                        SqlCommand cmd2 = conn.CreateCommand();
                        cmd2.Transaction = tran;
                        cmd2.CommandText = cmdText2;

                        using (SqlDataReader reader2 = cmd2.ExecuteReader())
                        {
                            int actualOrderCount = 0;
                            while (reader1.Read())
                            {
                                Assert.True(actualOrderCount <= expectedOrders.Length, "FAILED: Received more results than expected");
                                DataTestUtility.AssertEqualsWithDescription(expectedOrders[actualOrderCount], reader1.GetValue(0), "FAILED: Received wrong value in order query at row " + actualOrderCount);
                                actualOrderCount++;
                            }
                            reader1.NextResult();
                            reader1.Read();
                            int customerCount = (int)reader1.GetValue(0);

                            int actualCustomerCount = 0;
                            while (reader2.Read())
                            {
                                Assert.True(actualCustomerCount <= expectedCustomers.Length, "FAILED: Received more results than expected");
                                DataTestUtility.AssertEqualsWithDescription(expectedCustomers[actualCustomerCount], reader2.GetValue(0), "FAILED: Received wrong value in customer query at row " + actualCustomerCount);
                                actualCustomerCount++;
                            }
                            reader2.NextResult();
                            reader2.Read();
                            int orderCount = (int)reader2.GetValue(0);

                            DataTestUtility.AssertEqualsWithDescription(expectedCustomers.Length, customerCount, "FAILED: Count query returned incorrect Customer count");
                            DataTestUtility.AssertEqualsWithDescription(expectedOrders.Length, orderCount, "FAILED: Count query returned incorrect Order count");
                        }
                    }

                    cmd1.CommandText = "select @@trancount";
                    int tranCount = (int)cmd1.ExecuteScalar();
                    Assert.True(tranCount == 1, "FAILED: Expected a transaction count of 1, but actually received " + tranCount);

                    tran.Commit();
                }
            }
        }

        private static void TestReaderNonMarsCase(string caseName, string connectionString, ReaderTestType testType, ReaderVerificationType verificationType)
        {
            WeakReference weak = null;

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = COMMAND_TEXT_1;

                    SqlDataReader gch = null;
                    if ((testType != ReaderTestType.ReaderGC) && (testType != ReaderTestType.ReaderGCConnectionClose))
                        gch = cmd.ExecuteReader();

                    switch (testType)
                    {
                        case ReaderTestType.ReaderClose:
                            gch.Dispose();
                            break;
                        case ReaderTestType.ReaderDispose:
                            gch.Dispose();
                            break;
                        case ReaderTestType.ReaderGC:
                            weak = OpenNullifyReader(cmd);
                            GC.Collect();
                            GC.WaitForPendingFinalizers();
                            Assert.False(weak.IsAlive, "Reader is still alive!");
                            break;

                        case ReaderTestType.ConnectionClose:
                            GC.SuppressFinalize(gch);
                            con.Close();
                            con.Open();
                            break;

                        case ReaderTestType.ReaderGCConnectionClose:
                            weak = OpenNullifyReader(cmd);
                            GC.Collect();
                            GC.WaitForPendingFinalizers();

                            Assert.False(weak.IsAlive, "Reader is still alive!");
                            con.Close();
                            con.Open();
                            break;
                    }

                    switch (verificationType)
                    {
                        case ReaderVerificationType.ExecuteReader:
                            cmd.CommandText = COMMAND_TEXT_2;
                            using (SqlDataReader rdr = cmd.ExecuteReader())
                            {
                                rdr.Read();
                                Assert.Equal(rdr.FieldCount, 1);
                                Assert.Equal(rdr.GetName(0), COLUMN_NAME_2);
                            }
                            break;

                        case ReaderVerificationType.ChangeDatabase:
                            con.ChangeDatabase(DATABASE_NAME);
                            Assert.Equal(con.Database, DATABASE_NAME);
                            break;

                        case ReaderVerificationType.BeginTransaction:
                            cmd.Transaction = con.BeginTransaction();
                            cmd.CommandText = "select @@trancount";
                            int tranCount = (int)cmd.ExecuteScalar();
                            Assert.Equal(tranCount, 1);
                            break;
                    }
                }
            }
        }

 private static WeakReference OpenNullifyTransaction(SqlConnection connection)
 {
     SqlTransaction transaction = connection.BeginTransaction();
     WeakReference weak = new WeakReference(transaction);
     transaction = null;
     return weak;
 }

        private static void TestTransactionSingleCase(string caseName, string connectionString, TransactionTestType testType)
        {
            WeakReference weak = null;

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                SqlTransaction gch = null;
                if ((testType != TransactionTestType.TransactionGC) && (testType != TransactionTestType.TransactionGCConnectionClose))
                    gch = con.BeginTransaction();

                switch (testType)
                {
                    case TransactionTestType.TransactionRollback:
                        gch.Rollback();
                        break;

                    case TransactionTestType.TransactionDispose:
                        gch.Dispose();
                        break;

                    case TransactionTestType.TransactionGC:
                        weak = OpenNullifyTransaction(con);
                        GC.Collect();
                        GC.WaitForPendingFinalizers();

                        Assert.False(weak.IsAlive, "Transaction is still alive!");
                        break;
                    case TransactionTestType.ConnectionClose:
                        GC.SuppressFinalize(gch);
                        con.Close();
                        con.Open();
                        break;

                    case TransactionTestType.TransactionGCConnectionClose:
                        weak = OpenNullifyTransaction(con);
                        GC.Collect();
                        GC.WaitForPendingFinalizers();

                        Assert.False(weak.IsAlive, "Transaction is still alive!");
                        con.Close();
                        con.Open();
                        break;
                }

                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "select @@trancount";
                    int tranCount = (int)cmd.ExecuteScalar();
                    Assert.Equal(tranCount, 0);
                }
            }
        }

        private void MultipleExecutesInSameTransactionTest(string connectionString, string tempTableName)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlTransaction trans1 = connection.BeginTransaction();
                SqlTransaction trans2 = connection.BeginTransaction();
                SqlTransaction trans3 = connection.BeginTransaction();

                SqlCommand com1 = new SqlCommand("select top 1 au_id from " + tempTableName, connection);
                com1.Transaction = trans1;
                com1.ExecuteNonQuery();

                SqlCommand com2 = new SqlCommand("select top 1 au_id from " + tempTableName, connection);
                com2.Transaction = trans2;
                com2.ExecuteNonQuery();

                SqlCommand com3 = new SqlCommand("select top 1 au_id from " + tempTableName, connection);
                com3.Transaction = trans3;
                com3.ExecuteNonQuery();

                trans1.Rollback();
                trans2.Rollback();
                trans3.Rollback();

                com1.Dispose();
                com2.Dispose();
                com3.Dispose();

                SqlCommand com4 = new SqlCommand("select top 1 au_id from " + tempTableName, connection);
                com4.Transaction = trans1;
                SqlDataReader reader4 = com4.ExecuteReader();
                reader4.Dispose();
                com4.Dispose();

                trans1.Rollback();
            }
        }

        private static void TestReaderMarsCase(string caseName, string connectionString, ReaderTestType testType, ReaderVerificationType verificationType)
        {
            WeakReference weak = null;
            SqlCommand[] cmd = new SqlCommand[CONCURRENT_COMMANDS];
            SqlDataReader[] gch = new SqlDataReader[CONCURRENT_COMMANDS];

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                for (int i = 0; i < CONCURRENT_COMMANDS; i++)
                {
                    cmd[i] = con.CreateCommand();
                    cmd[i].CommandText = COMMAND_TEXT_1;
                    if ((testType != ReaderTestType.ReaderGC) && (testType != ReaderTestType.ReaderGCConnectionClose))
                        gch[i] = cmd[i].ExecuteReader();
                    else
                        gch[i] = null;
                }

                for (int i = 0; i < CONCURRENT_COMMANDS; i++)
                {
                    switch (testType)
                    {
                        case ReaderTestType.ReaderClose:
                            gch[i].Dispose();
                            break;

                        case ReaderTestType.ReaderDispose:
                            gch[i].Dispose();
                            break;

                        case ReaderTestType.ReaderGC:
                            weak = OpenNullifyReader(cmd[i]);
                            GC.Collect();
                            GC.WaitForPendingFinalizers();
                            Assert.False(weak.IsAlive, "Transaction is still alive on TestReaderMars: ReaderGC");
                            break;

                        case ReaderTestType.ConnectionClose:
                            GC.SuppressFinalize(gch[i]);
                            con.Close();
                            con.Open();
                            break;

                        case ReaderTestType.ReaderGCConnectionClose:
                            weak = OpenNullifyReader(cmd[i]);
                            GC.Collect();
                            GC.WaitForPendingFinalizers();
                            Assert.False(weak.IsAlive, "Transaction is still alive on TestReaderMars: ReaderGCConnectionClose");
                            con.Close();
                            con.Open();
                            break;
                    }

                    cmd[i].Dispose();
                }

                SqlCommand verificationCmd = con.CreateCommand();

                switch (verificationType)
                {
                    case ReaderVerificationType.ExecuteReader:
                        verificationCmd.CommandText = COMMAND_TEXT_2;
                        using (SqlDataReader rdr = verificationCmd.ExecuteReader())
                        {
                            rdr.Read();
                            DataTestClass.AssertEqualsWithDescription(1, rdr.FieldCount, "Execute Reader should return expected Field count");
                            DataTestClass.AssertEqualsWithDescription(COLUMN_NAME_2, rdr.GetName(0), "Execute Reader should return expected Field name");
                        }
                        break;

                    case ReaderVerificationType.ChangeDatabase:
                        con.ChangeDatabase(DATABASE_NAME);
                        DataTestClass.AssertEqualsWithDescription(DATABASE_NAME, con.Database, "Change Database should return expected Database Name");
                        break;

                    case ReaderVerificationType.BeginTransaction:
                        verificationCmd.Transaction = con.BeginTransaction();
                        verificationCmd.CommandText = "select @@trancount";
                        int tranCount = (int)verificationCmd.ExecuteScalar();
                        DataTestClass.AssertEqualsWithDescription(1, tranCount, "Begin Transaction should return expected Transaction count");
                        break;
                }

                verificationCmd.Dispose();
            }
        }

        public override bool Execute()
        {
            if(scripts.Length==0)
            {
                Log.LogWarning("No scripts were defined!");
                return true;
            }
            List<CommandInfo> commands = new List<CommandInfo>();
            if(!BuildCommands(commands))
                return false;

            using(SqlConnection connection = new SqlConnection(ConnectionString))
            {
                try
                {
                    connection.Open();                    
                }
                catch(Exception e)
                {
                    Log.LogError(string.Format("Could not connect to [{0}] because {1}", connectionString, e));
                    return false;
                }
                int count=0;
                string currentFile = null;
                SqlTransaction transaction=null;
                if (UseTransaction)
                    transaction = connection.BeginTransaction();
                using(transaction)
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandTimeout = CommandTimeout;
                    command.Connection = connection;
                    command.Transaction = transaction;
                    foreach (CommandInfo commandInfo in commands)
                    {
                        if(currentFile!=commandInfo.FileName)
                        {
                            Log.LogMessage("Now executing statements from "+commandInfo.FileName);
                            currentFile = commandInfo.FileName;
                        }
                        command.CommandText = commandInfo.Command;
                        try
                        {
                            count += 1;
                            command.ExecuteNonQuery();
                            if(count%100==0)
                            {
                                Log.LogMessage("Successfully executed {0} of {1} commands", count,commands.Count);
                            }
                        }
                        catch(Exception e)
                        {
                            string s = string.Format(
@"Could not execute command.
{0} {1}: {2}
Error: {3}", commandInfo.FileName, commandInfo.Line, commandInfo.Command, e);
                            Log.LogError(s);
                            return false;
                        }
                    }
                    if(transaction!=null)
                        transaction.Commit();
                }
            }
            return true;
        }

 /// <summary>
 /// 删除指定用户的所有信息
 /// </summary>
 /// <param name="uid">指定的用户uid</param>
 /// <param name="delposts">是否删除帖子</param>
 /// <param name="delpms">是否删除短消息</param>
 /// <returns></returns>
 public bool DelUserAllInf(int uid, bool delPosts, bool delPms)
 {
     SqlConnection conn = new SqlConnection(DbHelper.ConnectionString);
     conn.Open();
     using (SqlTransaction trans = conn.BeginTransaction())
     {
         try
         {
             DbParameter[] parms = {
                                     DbHelper.MakeInParam("@uid",(DbType)SqlDbType.Int,4,uid),
                                     DbHelper.MakeInParam("@delPosts",(DbType)SqlDbType.Bit,1, delPosts ? 1 : 0),
                                     DbHelper.MakeInParam("@delPms",(DbType)SqlDbType.Bit,1,delPms ? 1 : 0)
                                   };
             DbHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, string.Format("{0}deluserallinf", BaseConfigs.GetTablePrefix), parms);
             trans.Commit();
         }
         catch (Exception ex)
         {
             trans.Rollback();
             throw ex;
         }
     }
     conn.Close();
     return true;
 }