SqlTransaction.Commit Method

Definition

Namespace:

Microsoft.Data.SqlClient

Assembly:

Microsoft.Data.SqlClient.dll

Package:

Microsoft.Data.SqlClient v5.2.0

Commits the database transaction.

public:
 override void Commit();

public override void Commit ();

override this.Commit : unit -> unit

Public Overrides Sub Commit ()

Exceptions

Exception

An error occurred while trying to commit the transaction.

InvalidOperationException

The transaction has already been committed or rolled back.

-or-

The connection is broken.

Examples

The following example creates a SqlConnection and a SqlTransaction. It also demonstrates how to use the Commit, BeginTransaction, and Rollback methods. The transaction is rolled back on any error. Try/Catch error handling is used to handle any errors when attempting to commit or roll back the transaction.

using Microsoft.Data.SqlClient;

namespace Transaction1CS
{
    class Program
    {
        static void Main()
        {
            string connectionString =
                "Persist Security Info=False;Integrated Security=SSPI;database=Northwind;server=(local)";
            ExecuteSqlTransaction(connectionString);
            Console.ReadLine();
        }
        private static void ExecuteSqlTransaction(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction;

                // Start a local transaction.
                transaction = connection.BeginTransaction();

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction
                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    command.CommandText =
                        "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
                    command.ExecuteNonQuery();
                    command.CommandText =
                        "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
                    command.ExecuteNonQuery();

                    // Attempt to commit the transaction.
                    transaction.Commit();
                    Console.WriteLine("Both records are written to database.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                    Console.WriteLine("  Message: {0}", ex.Message);

                    // Attempt to roll back the transaction.
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (Exception ex2)
                    {
                        // This catch block will handle any errors that may have occurred
                        // on the server that would cause the rollback to fail, such as
                        // a closed connection.
                        Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                        Console.WriteLine("  Message: {0}", ex2.Message);
                    }
                }
            }
        }
    }
}

Remarks

The Commit method is equivalent to the Transact-SQL COMMIT TRANSACTION statement. You cannot roll back a transaction once it has been committed, because all modifications have become a permanent part of the database. For more information, see COMMIT TRANSACTION (Transact-SQL).

Note

Try/Catch exception handling should always be used when committing or rolling back a SqlTransaction. Both Commit and Rollback generates an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server.

For more information on SQL Server transactions, see Transactions (Transact-SQL).