How do you use transactions with a DataAdapter?

When working with a DataAdapter in ADO.NET, you can use a database transaction to ensure that multiple insert, update, or delete operations are treated as a single unit of work. If any operation fails, the transaction can be rolled back, preventing partial updates.

Steps to Use Transactions with a DataAdapter

• Create and open a database connection.
• Begin a transaction.
• Assign the transaction to the DataAdapter's commands (InsertCommand, UpdateCommand, and DeleteCommand).
• Call the Update() method on the DataAdapter.
• Commit the transaction if successful.
• Roll back the transaction if an exception occurs.

Example in C#

using System;
using System.Data;
using System.Data.SqlClient;

class TransactionExample
{
    static void Main()
    {
        // Connection string to the SQL Server database
        string connectionString = "your_connection_string";

        // Create a SQL connection
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Open the database connection
            connection.Open();

            // Start a database transaction
            SqlTransaction transaction = connection.BeginTransaction();

            try
            {
                // Create a DataAdapter
                SqlDataAdapter adapter = new SqlDataAdapter();

                // Configure the UpdateCommand
                adapter.UpdateCommand = new SqlCommand(
                    "UPDATE Employees SET Salary = @Salary WHERE EmployeeID = @EmployeeID",
                    connection,
                    transaction);

                // Add parameters
                adapter.UpdateCommand.Parameters.Add("@Salary", SqlDbType.Decimal, 0, "Salary");
                adapter.UpdateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");

                // Create a DataTable
                DataTable table = new DataTable();

                // Assume the table contains modified employee records

                // Update the database
                adapter.Update(table);

                // Commit the transaction
                transaction.Commit();

                Console.WriteLine("Transaction committed successfully.");
            }
            catch (Exception ex)
            {
                // Roll back the transaction if an error occurs
                transaction.Rollback();

                Console.WriteLine("Transaction rolled back.");
                Console.WriteLine(ex.Message);
            }
        }
    }
}

Explanation

• BeginTransaction() starts a new transaction on the connection.
• The transaction is associated with the adapter's commands by passing it to the SqlCommand constructor (or by setting the Transaction property).
• DataAdapter.Update() executes the appropriate SQL commands within the transaction.
• Commit() permanently saves all changes if every command succeeds.
• Rollback() undoes all changes made during the transaction if an error occurs.

Important Notes

• Every command used by the DataAdapter (InsertCommand, UpdateCommand, DeleteCommand) should use the same SqlConnection and SqlTransaction.
• Transactions improve data integrity by ensuring all related operations either succeed together or fail together.
• Keep transactions as short as possible to minimize locking and improve database performance.

Using transactions with a DataAdapter is a best practice whenever multiple related database modifications must be completed atomically.