Transactions ADO.Net & SQL

Link Artículo

SQL

BEGIN TRANSACTION [Tran1]

BEGIN TRY

INSERT INTO [Test].[dbo].[T1]

  ([Title], [AVG])

VALUES ('Tidd130', 130), ('Tidd230', 230)

UPDATE [Test].[dbo].[T1]

  SET [Title] = N'az2' ,[AVG] = 1

WHERE [dbo].[T1].[Title] = N'az'

--RAISERROR('your message here',16,1)

COMMIT TRANSACTION [Tran1]

END TRY

BEGIN CATCH

  ROLLBACK TRANSACTION [Tran1]

  SELECT

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() as ErrorState,

        ERROR_PROCEDURE() as ErrorProcedure,

        ERROR_LINE() as ErrorLine,

        ERROR_MESSAGE() as ErrorMessage; 

 

END CATCH 

GO

ADO .Net [C#]

Link artículo

   public class TransactionDemo

        {

            public TransactionDemo()

            {

            }

            [STAThread]

            public static void Main()

            {

                Demo1();

            }

            private static void Demo1()

            {

                SqlConnection db = new SqlConnection("connstringhere");

                SqlTransaction transaction;

                db.Open();

                transaction = db.BeginTransaction();

                try

                {

                    new SqlCommand("INSERT INTO TransactionDemo " +

                       "(Text) VALUES ('Row1');", db, transaction)

                       .ExecuteNonQuery();

                    new SqlCommand("INSERT INTO TransactionDemo " +

                       "(Text) VALUES ('Row2');", db, transaction)

                       .ExecuteNonQuery();

                    new SqlCommand("INSERT INTO CrashMeNow VALUES " +

                       "('Die', 'Die', 'Die');", db, transaction)

                       .ExecuteNonQuery();

                    transaction.Commit();

                }

                catch (SqlException sqlError)

                {

                    transaction.Rollback();

                }

                db.Close();

            }

        }



TransactionScope [C#]

Crear una transacción adentro de otra

    string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;

            var option = new TransactionOptions

            {

                IsolationLevel = IsolationLevel.ReadCommitted,

                Timeout = TimeSpan.FromSeconds(60)

            };

            using (var scopeOuter = new TransactionScope(TransactionScopeOption.Required, option))

            {

                using (var conn = new SqlConnection(connectionString))

                {

                    using (SqlCommand cmd = conn.CreateCommand())

                    {

                        cmd.CommandText = "INSERT INTO Data(Code, FirstName)VALUES('A-100','Mr.A')";

                        cmd.Connection.Open();

                        cmd.ExecuteNonQuery();

                    }

                }

                using (var scopeInner = new TransactionScope(TransactionScopeOption.Required, option))

                {

                    using (var conn = new SqlConnection(connectionString))

                    {

                        using (SqlCommand cmd = conn.CreateCommand())

                        {

                            cmd.CommandText = "INSERT INTO Data(Code, FirstName) VALUES('B-100','Mr.B')";

                            cmd.Connection.Open();

                            cmd.ExecuteNonQuery();

                        }

                    }

                    scopeInner.Complete();

                }

                scopeOuter.Complete();

            }

 

TransactionScope

  try

            {

                using (TransactionScope scope = new TransactionScope())

                {

                    // Do Operation 1

                    // Do Operation 2

                    //...

 

                    // In case of an exception, it wont be called and transaction is rolled back

                    scope.Complete();

                }

            }

            catch (ThreadAbortException ex)

            {

                // Handle exception

            }