interview

home / developersection / interviews / transactions in ado.net

Ask Question
ICSM Computer

IT-Hardware & Networking

Ravi Vishwakarma is a dedicated Software Developer with a passion for crafting efficient and innovative solutions. With a keen eye for detail and years of experience, he excels in developing robust software systems that meet client needs. His expertise spans across multiple programming languages and technologies, making him a valuable asset in any software development project.

Message
Can you answer this question?
Answer

1 Answers

ICSM Computer

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

  1. Open a database connection using SqlConnection.
  2. Begin a transaction using BeginTransaction().
  3. Assign the transaction to the SqlCommand.
  4. Execute SQL commands within the transaction.
  5. Commit the transaction if all operations succeed.
  6. 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.

 

advertising
advertising

Liked By

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy