Starts a database transaction with the specified isolation level.
(in System.Data.dll)
Visual Basic (Declaration)
Public Function BeginTransaction ( _
iso As IsolationLevel _
) As SqlTransaction
Dim instance As SqlConnection
Dim iso As IsolationLevel
Dim returnValue As SqlTransaction
returnValue = instance.BeginTransaction(iso)
public SqlTransaction BeginTransaction(
IsolationLevel iso
)
public:
SqlTransaction^ BeginTransaction(
IsolationLevel iso
)
public function BeginTransaction(
iso : IsolationLevel
) : SqlTransaction
This command maps to the SQL Server implementation of BEGIN TRANSACTION.
You must explicitly commit or roll back the transaction using the Commit or Rollback method. To make sure that the .NET Framework Data Provider for SQL Server transaction management model performs correctly, avoid using other transaction management models, such as the one provided by SQL Server.
.gif) Note: |
|---|
After a transaction is committed or rolled back, the isolation level of the transaction persists for all subsequent commands that are in autocommit mode (the SQL Server default). This can produce unexpected results, such as an isolation level of REPEATABLE READ persisting and locking other users out of a row. To reset the isolation level to the default (READ COMMITTED), execute the Transact-SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED statement, or call SqlConnection..::.BeginTransaction followed immediately by SqlTransaction..::.Commit. For more information on SQL Server isolation levels, see "Isolation Levels in the Database Engine" in SQL Server 2005 Books Online. |
For more information on SQL Server transactions, see "Explicit Transactions" and "Coding Efficient Transactions" in SQL Server 2005 Books Online.
.gif) Caution: |
|---|
When your query returns a large amount of data and calls BeginTransaction, a SqlException is thrown because SQL Server 2005 does not allow parallel transactions when using MARS. To avoid this problem, always associate a transaction with the command, the connection, or both before any readers are open. |
The following example creates a SqlConnection and a SqlTransaction. It also demonstrates how to use the BeginTransaction, a Commit, and Rollback methods.
Private Sub ExecuteSqlTransaction(ByVal connectionString As String)
Using connection As New SqlConnection(connectionString)
connection.Open()
Dim command As SqlCommand = connection.CreateCommand()
Dim transaction As SqlTransaction
' Start a local transaction
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
' 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()
transaction.Commit()
Console.WriteLine("Both records are written to database.")
Catch e As Exception
Try
transaction.Rollback()
Catch ex As SqlException
If Not transaction.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.")
End Try
End Using
End Sub
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(IsolationLevel.ReadCommitted);
// 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();
transaction.Commit();
Console.WriteLine("Both records are written to database.");
}
catch (Exception e)
{
try
{
transaction.Rollback();
}
catch (SqlException ex)
{
if (transaction.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.");
}
}
}
Windows 7, Windows Vista, Windows XP SP2, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP Starter Edition, Windows Server 2008 R2, Windows Server 2008, Windows Server 2003, Windows Server 2000 SP4, Windows Millennium Edition, Windows 98, Windows CE, Windows Mobile for Smartphone, Windows Mobile for Pocket PC
The .NET Framework and .NET Compact Framework do not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.
.NET Framework
Supported in: 3.5, 3.0, 2.0, 1.1, 1.0
.NET Compact Framework
Supported in: 3.5, 2.0, 1.0
Reference
Other Resources