How to Commit and Rollback Transaction in .NET

If you are sending several commands to SQL Server database, you should make it a habit to build your application to be rollback capable in order to recover all commands that were already executed. In ADO.NET, we can achieve this recovery process by using the SqlTransaction object. A SqlTransaction's ROLLBACK method can undo any work performed since the BeginTransaction method was executed while the COMMIT method ends a transaction and makes all changes visible to other users.


To give you an example of commit and rollback transaction, take a look at the following code.
1. The program will insert a record to tblLogs table (Logs).
2. Then insert a new category to tblCategories table (Category).
3. If insertion to category failed, rollback the 1st command (which is insert to tblLogs) else apply the changes by committing the transaction.

How to Commit and Rollback SQL Transaction in C#

            using (SqlConnection con = new SqlConnection("Data Source=mnlho8ap33;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    SqlTransaction trans = null;

                    try
                    {
                        con.Open();
                        // BeginTransaction() Requires Open Connection
                        trans = con.BeginTransaction();

                        // Assign Transaction to Command
                        cmd.Transaction = trans;

                        //1st command to execute. This will be rollback if 2nd command fails
                        cmd.CommandText = "INSERT INTO tblLogs (LogDescription) VALUES ('Inserted New Category')";
                        cmd.ExecuteNonQuery();

                        //2nd command to execute
                        cmd.CommandText = "INSERT INTO tblCategories (CategoryName) VALUES ('TEST CATEGORY')";
                        cmd.ExecuteNonQuery();



                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        Console.WriteLine("Error while processing command. Previous execution was rollback");
                    }
                    finally
                    {
                        con.Close();
                    }
                }

            }

How to Commit and Rollback SQL Transaction in VB.NET

        Dim con As New SqlConnection("Data Source=mnlho8ap33;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678")
        Dim cmd As SqlCommand = con.CreateCommand()
        Dim trans As SqlTransaction = Nothing
        Try
            con.Open()
            'BeginTransaction() Requires Open Connection
            trans = con.BeginTransaction()

            'Assign Transaction to Command
            cmd.Transaction = trans

            '1st command to execute. This will be rollback if 2nd command fails
            cmd.CommandText = "INSERT INTO tblLogs (LogDescription) VALUES ('Inserted New Category')"
            cmd.ExecuteNonQuery()

            '2nd command to execute
            cmd.CommandText = "INSERT INTO tblCategories (CategoryName) VALUES ('TEST CATEGORY')"
            cmd.ExecuteNonQuery()

            trans.Commit()
        Catch
            trans.Rollback()
            Console.WriteLine("Error while processing command. Previous execution was rollback")
        Finally
            con.Close()
        End Try

Don't forget to include System.Data.SqlClient namespace.

For more coding tips & tricks, subscribe now.