Local transactions
Applies to:
.NET Framework
.NET
.NET Standard
Transactions in ADO.NET are used when you want to bind multiple tasks together so that they execute as a single unit of work. For example, imagine that an application performs two tasks. First, it updates a table with order information. Second, it updates a table that contains inventory information, debiting the items ordered. If either task fails, then both updates are rolled back.
Determining the transaction type
A transaction is considered to be a local transaction when it is a single-phase transaction and is handled by the database directly. A transaction is considered to be a distributed transaction when it is coordinated by a transaction monitor and uses fail-safe mechanisms (such as two-phase commit) for transaction resolution.
The Microsoft SqlClient Data Provider for SQL Server has its own SqlTransaction object for performing local transactions in SQL Server databases. Other .NET data providers also provide their own Transaction
objects. In addition, there is a DbTransaction class that is available for writing provider-independent code that requires transactions.
Note
Transactions are most efficient when they are performed on the server. If you are working with a SQL Server database that makes extensive use of explicit transactions, consider writing them as stored procedures using the Transact-SQL BEGIN TRANSACTION statement.
Performing a transaction using a single connection
In ADO.NET, you control transactions with the Connection
object. You can initiate a local transaction with the BeginTransaction
method. Once you have begun a transaction, you can enlist a command in that transaction with the Transaction
property of a Command
object. You can then commit or roll back modifications made at the data source based on the success or failure of the components of the transaction.
Note
The EnlistDistributedTransaction
method should not be used for a local transaction.
The scope of the transaction is limited to the connection. The following example performs an explicit transaction that consists of two separate commands in the try
block. The commands execute INSERT statements against the Production.ScrapReason
table in the AdventureWorks SQL Server sample database, which are committed if no exceptions are thrown. The code in the catch
block rolls back the transaction if an exception is thrown. If the transaction is aborted or the connection is closed before the transaction has completed, it is automatically rolled back.
Example
Follow these steps to perform a transaction.
Call the BeginTransaction method of the SqlConnection object to mark the start of the transaction. The BeginTransaction method returns a reference to the transaction. This reference is assigned to the SqlCommand objects that are enlisted in the transaction.
Assign the
Transaction
object to the Transaction property of the SqlCommand to be executed. If a command is executed on a connection with an active transaction, and theTransaction
object has not been assigned to theTransaction
property of theCommand
object, an exception is thrown.Execute the required commands.
Call the Commit method of the SqlTransaction object to complete the transaction, or call the Rollback method to end the transaction. If the connection is closed or disposed before either the Commit or Rollback methods have been executed, the transaction is rolled back.
The following code example demonstrates transactional logic using the Microsoft SqlClient Data Provider for SQL Server.
using System;
using Microsoft.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
string connectionString = "Data Source = localhost; Integrated Security = true; Initial Catalog = AdventureWorks";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Start a local transaction.
SqlTransaction sqlTran = connection.BeginTransaction();
// Enlist a command in the current transaction.
SqlCommand command = connection.CreateCommand();
command.Transaction = sqlTran;
try
{
// Execute two separate commands.
command.CommandText =
"INSERT INTO Production.ScrapReason(Name) VALUES('Wrong size')";
command.ExecuteNonQuery();
command.CommandText =
"INSERT INTO Production.ScrapReason(Name) VALUES('Wrong color')";
command.ExecuteNonQuery();
// Commit the transaction.
sqlTran.Commit();
Console.WriteLine("Both records were written to database.");
}
catch (Exception ex)
{
// Handle the exception if the transaction fails to commit.
Console.WriteLine(ex.Message);
try
{
// Attempt to roll back the transaction.
sqlTran.Rollback();
}
catch (Exception exRollback)
{
// Throws an InvalidOperationException if the connection
// is closed or the transaction has already been rolled
// back on the server.
Console.WriteLine(exRollback.Message);
}
}
}
}
}
See also
Зворотний зв’язок
https://aka.ms/ContentUserFeedback.
Очікується незабаром: протягом 2024 року ми будемо припиняти використання механізму реєстрації проблем у GitHub для зворотного зв’язку щодо вмісту й замінювати його новою системою зворотного зв’язку. Докладніше:Надіслати й переглянути відгук про