Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

SqlTransaction.Commit Method

Commits the database transaction.

[Visual Basic]
Public Overridable Sub Commit() Implements IDbTransaction.Commit
[C#]
public virtual void Commit();
[C++]
public: virtual void Commit();
[JScript]
public function Commit();

Implements

IDbTransaction.Commit

Exceptions

Exception Type Condition
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.

Remarks

The Commit method is equivalent to the Transact-Sql COMMIT TRANSACTION statement. For more information, see SQL Server Books Online.

Example

[Visual Basic, C#, C++] The following example creates a SqlConnection and a SqlTransaction. It also demonstrates how to use the Commit, BeginTransaction, and Rollback methods.

[Visual Basic] 
Public Sub RunSqlTransaction(myConnString As String)
    Dim myConnection As New SqlConnection(myConnString)
    myConnection.Open()
    
    Dim myCommand As SqlCommand = myConnection.CreateCommand()
    Dim myTrans As SqlTransaction
    
    ' Start a local transaction
    myTrans = myConnection.BeginTransaction()
    ' Must assign both transaction object and connection
    ' to Command object for a pending local transaction
    myCommand.Connection = myConnection
    myCommand.Transaction = myTrans
    
    Try
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"
      myCommand.ExecuteNonQuery()
      myTrans.Commit()
      Console.WriteLine("Both records are written to database.")
    Catch e As Exception
      Try
        myTrans.Rollback()
      Catch ex As SqlException
        If Not myTrans.Connection Is Nothing Then
          Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                            " was encountered while attempting to roll back the transaction.")
        End If
      End Try
    
      Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                      "was encountered while inserting the data.")
      Console.WriteLine("Neither record was written to database.")
    Finally
      myConnection.Close()
    End Try
End Sub 'RunSqlTransaction

[C#] 
public void RunSqlTransaction(string myConnString) 
 {
    SqlConnection myConnection = new SqlConnection(myConnString);
    myConnection.Open();

    SqlCommand myCommand = myConnection.CreateCommand();
    SqlTransaction myTrans;

    // Start a local transaction
    myTrans = myConnection.BeginTransaction();
    // Must assign both transaction object and connection
    // to Command object for a pending local transaction
    myCommand.Connection = myConnection;
    myCommand.Transaction = myTrans;

    try
    {
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
      myCommand.ExecuteNonQuery();
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
      myCommand.ExecuteNonQuery();
      myTrans.Commit();
      Console.WriteLine("Both records are written to database.");
    }
    catch(Exception e)
    {
      try
      {
        myTrans.Rollback();
      }
      catch (SqlException ex)
      {
        if (myTrans.Connection != null)
        {
          Console.WriteLine("An exception of type " + ex.GetType() +
                            " was encountered while attempting to roll back the transaction.");
        }
      }
    
      Console.WriteLine("An exception of type " + e.GetType() +
                        " was encountered while inserting the data.");
      Console.WriteLine("Neither record was written to database.");
    }
    finally 
    {
      myConnection.Close();
    }
}

[C++] 
public:
void RunSqlTransaction(String* myConnString) 
 {
    SqlConnection* myConnection = new SqlConnection(myConnString);
    myConnection->Open();

    SqlCommand* myCommand = myConnection->CreateCommand();
    SqlTransaction* myTrans;

    // Start a local transaction
    myTrans = myConnection->BeginTransaction();
    // Must assign both transaction object and connection
    // to Command object for a pending local transaction
    myCommand->Connection = myConnection;
    myCommand->Transaction = myTrans;

    try
    {
      myCommand->CommandText = S"Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
      myCommand->ExecuteNonQuery();
      myCommand->CommandText = S"Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
      myCommand->ExecuteNonQuery();
      myTrans->Commit();
      Console::WriteLine(S"Both records are written to database.");
    }
    catch(Exception* e)
    {
      try
      {
        myTrans->Rollback();
      }
      catch (SqlException* ex)
      {
        if (myTrans->Connection != 0)
        {
          Console::WriteLine(S"An exception of type {0} was encountered while attempting to roll back the transaction.", ex->GetType());
        }
      }
    
      Console::WriteLine(S"An exception of type {0} was encountered while inserting the data.", e->GetType());
      Console::WriteLine(S"Neither record was written to database.");
    }
    __finally 
    {
      myConnection->Close();
    }
}

[JScript] No example is available for JScript. To view a Visual Basic, C#, or C++ example, click the Language Filter button Language Filter in the upper-left corner of the page.

Requirements

Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family, .NET Compact Framework

See Also

SqlTransaction Class | SqlTransaction Members | System.Data.SqlClient Namespace | BeginTransaction | Save | Rollback

Show:
© 2015 Microsoft