Transactions in ADO.NET

ICSM Computer 735 12 Feb 2025

Ask by ICSM Computer

Updated 12 Feb 2025

1 Answers

ICSM Computer 12 Feb 2025

In ADO.NET, transactions are used to ensure that a series of database operations are executed as a single unit. If any operation in the transaction fails, all changes made during the transaction can be rolled back, ensuring data consistency.

Steps to Use Transactions in ADO.NET

Open a database connection using SqlConnection.
Begin a transaction using BeginTransaction().
Assign the transaction to the SqlCommand.
Execute SQL commands within the transaction.
Commit the transaction if all operations succeed.
Rollback the transaction in case of an error.

Example: Using Transactions in ADO.NET with C#

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

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string_here";

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

            // Start a transaction
            SqlTransaction transaction = conn.BeginTransaction();

            try
            {
                using (SqlCommand cmd1 = new SqlCommand("INSERT INTO Employees (Name, Age) VALUES ('John Doe', 30)", conn, transaction))
                {
                    cmd1.ExecuteNonQuery();
                }

                using (SqlCommand cmd2 = new SqlCommand("INSERT INTO Salaries (EmployeeId, Salary) VALUES (1, 5000)", conn, transaction))
                {
                    cmd2.ExecuteNonQuery();
                }

                // Commit transaction if all queries succeed
                transaction.Commit();
                Console.WriteLine("Transaction committed successfully.");
            }
            catch (Exception ex)
            {
                // Rollback transaction in case of an error
                transaction.Rollback();
                Console.WriteLine("Transaction rolled back: " + ex.Message);
            }
        }
    }
}

Key Points

Use BeginTransaction() to start a transaction.
Assign the transaction to each SqlCommand using the constructor: new SqlCommand(query, connection, transaction).
Use transaction.Commit() to save changes if all operations succeed.
Use transaction.Rollback() to undo changes if an error occurs.
Always use using statements to ensure proper disposal of resources.

Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md

interview

Transactions in ADO.NET

Can you answer this question?

1 Answers

Steps to Use Transactions in ADO.NET

Key Points