Transactions - (ADO.NET and SQL Server)

Transactions are a fundamental concept in database management systems, and are used to group a sequence of database operations into a single, atomic unit of work. These operations can either all succeed, or all fail, ensuring that the database remains in a consistent state.

In ADO.NET, transactions can be used to group multiple database operations together into a single transaction, so that they either all succeed, or all fail. This is useful when multiple database operations must be performed together or when you need to ensure that data modifications occur as a group.

Syntax

Here's the basic syntax for using transactions in ADO.NET:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlTransaction transaction = connection.BeginTransaction())
    {
        try
        {
            // perform multiple database operations
            // ...

            // commit the transaction
            transaction.Commit();
        }
        catch
        {
            // if an error occurs, roll back the transaction
            transaction.Rollback();
        }
    }
}

In this code, the connection object is used to create and open a connection to the SQL Server database, and the transaction object is used to create a new transaction. The transaction can then be used to group one or more database operations.

Example

Here's an example of using a transaction to update a bank account balance:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlTransaction transaction = connection.BeginTransaction())
    {
        try
        {
            // retrieve the current balance
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "SELECT Balance FROM Accounts WHERE AccountNumber = '12345'";
            cmd.Transaction = transaction;

            decimal currentBalance = (decimal)cmd.ExecuteScalar();

            // update the balance
            decimal newBalance = currentBalance - 100.00m;

            cmd.CommandText = "UPDATE Accounts SET Balance = @Balance WHERE AccountNumber = '12345'";
            cmd.Parameters.AddWithValue("@Balance", newBalance);

            cmd.ExecuteNonQuery();

            // commit the transaction
            transaction.Commit();

            Console.WriteLine("Transaction complete. New balance: {0}", newBalance);
        }
        catch (Exception ex)
        {
            Console.WriteLine("Transaction failed: " + ex.Message);

            // if an error occurs, roll back the transaction
            transaction.Rollback();
        }
    }
}

In this example, a transaction is created to update the balance of a bank account. The current balance is retrieved from the database, and then a new balance is calculated and used to update the database. If an error occurs, the transaction is rolled back, which means that the update is not applied to the database.

Output

When you run this example, it updates the balance of the bank account and displays the new balance in the console. If an error occurs, it displays a message indicating that the transaction failed.

Explanation

Transactions in ADO.NET provide a way to group multiple database operations together into a single unit of work. Transactions ensure that all operations either succeed or fail together, so that the database remains in a consistent state.

In this example, the SqlCommand class is to execute SQL commands against the SQL Server database. The Transaction property is used to associate the command with the transaction, so that it becomes part of the transaction.

Use

Transactions are useful in a variety of scenarios, including:

• Bank transactions: When multiple database operations must be performed together to update bank accounts or perform financial transactions.
• E-commerce websites: When multiple database operations must be performed together to process orders and update inventory.
• Batch processing: When a group of database operations must be performed together in a scheduled batch.

Important Points

• Transactions in ADO.NET can be used to group multiple database operations together into a single unit of work.
• Transactions ensure that all operations either succeed or fail together, so that the database remains in a consistent state.
• Use the BeginTransaction() method of the SqlConnection class to create a new transaction, and use the Transaction property of the SqlCommand class to associate the command with the transaction.
• Use the Commit() method of the SqlTransaction class to commit the transaction, and the Rollback() method to roll back the transaction if an error occurs.

Summary

In this page, we discussed the basics of transactions in ADO.NET and SQL Server. We covered the syntax, example, output, explanation, use, and important points of transactions. Transactions are a fundamental concept in database management systems, and provide a way to group multiple database operations together into a single atomic unit of work, ensuring that all operations either succeed or fail together.