Transactions & Batch Operations

Level: Intermediate

ℹ️ Where This Fits

Transactions ensure multiple operations succeed or fail together. Batch operations improve performance by executing multiple queries efficiently.

ℹ️ What You'll Learn

• Transaction scope: Multiple operations atomic
• BeginTransaction: Start transaction
• Commit/Rollback: Commit success or rollback on error
• Batch Insert: Insert multiple rows efficiently
• School Management: Create student + fee account transaction, batch attendance update
• Common mistakes: Not rolling back on error, long-running transactions

Basic Transaction with Dapper

using (var conn = new SqlConnection(connString))
{
    conn.Open();
    using (var trans = conn.BeginTransaction())
    {
        try
        {
            // Step 1: Create student
            var studentId = conn.QuerySingle<int>(
                @"INSERT INTO Students (Name, ClassName, Status, CreatedAt) 
                  VALUES (@name, @className, @status, GETDATE());
                  SELECT SCOPE_IDENTITY();",
                new { name = "Ravi Kumar", className = "10-A", status = "Active" },
                transaction: trans
            );

            // Step 2: Create fee account
            conn.Execute(
                @"INSERT INTO FeeAccount (StudentId, TotalFees, Status, DueDate)
                  VALUES (@studentId, @fees, @status, DATEADD(month, 3, GETDATE()))",
                new { studentId = studentId, fees = 50000, status = "Pending" },
                transaction: trans
            );

            trans.Commit();
            return studentId;
        }
        catch (Exception ex)
        {
            trans.Rollback();
            throw new Exception("Student creation failed", ex);
        }
    }
}

Batch Insert Multiple Rows

public async Task BatchInsertStudentsAsync(List<Student> students)
{
    const string sql = @"
        INSERT INTO Students (Name, ClassName, RollNumber, Status, CreatedAt)
        VALUES (@Name, @ClassName, @RollNumber, @Status, GETDATE())";

    using (var conn = new SqlConnection(connString))
    {
        await conn.OpenAsync();
        using (var trans = conn.BeginTransaction())
        {
            try
            {
                foreach (var student in students)
                {
                    await conn.ExecuteAsync(sql, student, transaction: trans);
                }
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                throw;
            }
        }
    }
}

Batch Update with Transaction

public async Task UpdateFeeStatusBatchAsync(
    List<int> studentIds, 
    string newStatus)
{
    const string sql = @"
        UPDATE FeeAccount 
        SET Status = @status, UpdatedAt = GETDATE()
        WHERE StudentId = @studentId";

    using (var conn = new SqlConnection(connString))
    {
        await conn.OpenAsync();
        using (var trans = conn.BeginTransaction())
        {
            try
            {
                foreach (var id in studentIds)
                {
                    await conn.ExecuteAsync(
                        sql,
                        new { studentId = id, status = newStatus },
                        transaction: trans
                    );
                }
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                throw;
            }
        }
    }
}

Enrollment Transaction - Complete Example

public async Task EnrollStudentAsync(string name, string className)
{
    using (var conn = new SqlConnection(connString))
    {
        await conn.OpenAsync();
        using (var trans = conn.BeginTransaction())
        {
            try
            {
                // Create student
                var studentId = await conn.QuerySingleAsync<int>(
                    @"INSERT INTO Students (Name, ClassName, Status, CreatedAt)
                      VALUES (@name, @className, 'Active', GETDATE());
                      SELECT SCOPE_IDENTITY();",
                    new { name = name, className = className },
                    transaction: trans
                );

                // Create fee account
                await conn.ExecuteAsync(
                    @"INSERT INTO FeeAccount (StudentId, TotalFees, Status, DueDate)
                      VALUES (@studentId, 50000, 'Pending', DATEADD(month, 3, GETDATE()))",
                    new { studentId = studentId },
                    transaction: trans
                );

                // Create attendance record
                await conn.ExecuteAsync(
                    @"INSERT INTO Attendance (StudentId, Date, IsPresent, MarkedAt)
                      VALUES (@studentId, GETDATE(), 1, GETDATE())",
                    new { studentId = studentId },
                    transaction: trans
                );

                trans.Commit();
                return studentId;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw new Exception("Enrollment failed", ex);
            }
        }
    }
}

Best Practices

✓ Always rollback on error ✓ Keep transactions short ✓ Pass transaction parameter to Execute/Query methods ✓ Use using statement for auto-disposal

💡 Transaction Pattern

Pattern:

1. Begin transaction
2. Execute operations (pass transaction parameter)
3. Commit on success
4. Rollback on exception

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on Transactions & Batch Operations. Try these prompts:

• "How do you create a transaction in Dapper?"
• "What's the difference between Commit and Rollback?"
• "How do you pass a transaction to Execute method?"
• "When should you batch operations?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> 06. Optimization & Best Practices