SqlTransaction Class
Represents a Transact-SQL transaction to be made in a SQL Server database. This class cannot be inherited.
For a list of all members of this type, see SqlTransaction Members.
System.Object
System.MarshalByRefObject
System.Data.SqlClient.SqlTransaction
[Visual Basic] NotInheritable Public Class SqlTransaction Inherits MarshalByRefObject Implements IDbTransaction, IDisposable [C#] public sealed class SqlTransaction : MarshalByRefObject, IDbTransaction, IDisposable [C++] public __gc __sealed class SqlTransaction : public MarshalByRefObject, IDbTransaction, IDisposable [JScript] public class SqlTransaction extends MarshalByRefObject implements IDbTransaction, IDisposable
Thread Safety
Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.
Remarks
The application creates a SqlTransaction object by calling BeginTransaction on the SqlConnection object. All subsequent operations associated with the transaction (for example, committing or aborting the transaction), are performed on the SqlTransaction object.
Example
[Visual Basic, C#, C++] The following example creates a SqlConnection and a SqlTransaction. It also demonstrates how to use the BeginTransaction, Commit, 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
in the upper-left corner of the page.
Requirements
Namespace: System.Data.SqlClient
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
Assembly: System.Data (in System.Data.dll)
See Also
SqlTransaction Members | System.Data.SqlClient Namespace | SqlDataAdapter | SqlConnection