Your First Database Connection

Every database interaction starts with a connection – a communication channel between your C# code and the database server. In ADO.NET, the SqlConnection class (for SQL Server) manages this connection lifecycle.

Here's the foundational pattern you'll use in every test that touches the database:

using System.Data.SqlClient;

[Test]
public async Task MyFirstDatabaseTest()
{
    // Connection string specifies: server, database, authentication
    var connectionString = "Server=localhost;Database=TestDb;Trusted_Connection=true;";

    // Create connection (does NOT open it yet)
    using var connection = new SqlConnection(connectionString);

    // Explicitly open the connection
    await connection.OpenAsync();

    // Connection is now ready for commands
    Console.WriteLine($"Connected to: {connection.Database}");
    Console.WriteLine($"Server version: {connection.ServerVersion}");

    // using statement automatically closes and disposes connection when done
}

Let's break down what's happening:

Connection String: The connection string contains all information needed to locate and authenticate with the database. This example uses Windows authentication (Trusted_Connection=true), which uses your Windows login. For SQL Server authentication, you'd use User Id=testuser;Password=testpass; instead.

using Statement: The using keyword ensures the connection is properly disposed when the code block exits – even if an exception occurs. This releases the connection back to the connection pool (more on this shortly).

OpenAsync: Creating a SqlConnection doesn't actually connect to the database – you must call OpenAsync() explicitly. This lets you control exactly when the connection attempt happens and handle connection failures appropriately.

Connection Strings in Test Environments

Hardcoding connection strings in test code works for initial learning but breaks down quickly. Different developers use different database instances, CI/CD systems need different credentials, and local vs. integration environments require different servers.

Better approach – store connection strings in configuration files:

// appsettings.test.json
{
  "ConnectionStrings": {
    "TestDatabase": "Server=localhost;Database=TestAutomation;Trusted_Connection=true;"
  }
}

using Microsoft.Extensions.Configuration;

public class DatabaseTestBase
{
    protected string ConnectionString { get; private set; }

    [OneTimeSetUp]
    public void SetupConfiguration()
    {
        var config = new ConfigurationBuilder()
            .AddJsonFile("appsettings.test.json")
            .Build();

        ConnectionString = config.GetConnectionString("TestDatabase");
    }
}

Now all tests inherit from DatabaseTestBase and use ConnectionString – change the config file, not the code.

Connection Pooling – Why You Don't Need to Worry

Opening database connections is expensive – network handshakes, authentication, resource allocation. If every test opened a new physical connection, your test suite would be painfully slow.

Fortunately, ADO.NET implements connection pooling automatically. When you call connection.Close() or dispose a connection, ADO.NET doesn't actually close the physical connection to the database. Instead, it returns the connection to a pool of reusable connections. When you open a new connection with the same connection string, ADO.NET gives you a pooled connection – nearly instant.

Connection pooling happens transparently. You write code as if you're creating fresh connections, but ADO.NET reuses them behind the scenes. This means the pattern "create connection per test" is actually efficient – you're not paying the connection cost every time.

if (connection.State == ConnectionState.Open)
{
    // Safe to execute commands
}

Understanding connection management sets the foundation. Next, you'll execute actual queries and commands against the database.

Executing Commands – The Core Operations

Once you have an open connection, you execute SQL using SqlCommand objects. ADO.NET provides three primary execution methods, each optimized for different query types:

ExecuteNonQuery – For INSERT, UPDATE, DELETE

ExecuteNonQuery runs commands that don't return result sets – typically data modification statements. It returns the number of rows affected.

[Test]
public async Task CreateTestUser_InsertsUserSuccessfully()
{
    using var connection = new SqlConnection(ConnectionString);
    await connection.OpenAsync();

    // Create the command
    using var command = new SqlCommand(
        "INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive) " +
        "VALUES ('[email protected]', 'Test', 'User', @CreatedDate, 1)",
        connection);

    // Add parameter for current timestamp
    command.Parameters.AddWithValue("@CreatedDate", DateTime.UtcNow);

    // Execute and get rows affected
    var rowsAffected = await command.ExecuteNonQueryAsync();

    Assert.That(rowsAffected, Is.EqualTo(1), "Should insert exactly one row");
}

Notice we're mixing literal values (the strings, the number 1) with a parameter (@CreatedDate). While this works, it's not ideal. We'll cover proper parameterization patterns shortly.

ExecuteScalar – For Single-Value Queries

ExecuteScalar executes a query and returns the first column of the first row. Perfect for retrieving generated IDs, counts, or aggregate values.

[Test]
public async Task CreateUser_ReturnsGeneratedUserId()
{
    using var connection = new SqlConnection(ConnectionString);
    await connection.OpenAsync();

    using var command = new SqlCommand(
        "INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive) " +
        "VALUES (@Email, @FirstName, @LastName, @CreatedDate, @IsActive); " +
        "SELECT SCOPE_IDENTITY();",  // Returns the generated UserId
        connection);

    command.Parameters.AddWithValue("@Email", "[email protected]");
    command.Parameters.AddWithValue("@FirstName", "Test");
    command.Parameters.AddWithValue("@LastName", "User");
    command.Parameters.AddWithValue("@CreatedDate", DateTime.UtcNow);
    command.Parameters.AddWithValue("@IsActive", true);

    // ExecuteScalar returns object – convert to int
    var userId = Convert.ToInt32(await command.ExecuteScalarAsync());

    Assert.That(userId, Is.GreaterThan(0), "Should return valid UserId");
}

The pattern INSERT ... ; SELECT SCOPE_IDENTITY(); is common in SQL Server – it returns the auto-generated identity value from the previous INSERT. Other databases use different syntax (RETURNING id in PostgreSQL, LAST_INSERT_ROWID() in SQLite).

ExecuteScalar is also perfect for validation queries in tests:

// Verify user count
var userCount = Convert.ToInt32(await command.ExecuteScalarAsync());
Assert.That(userCount, Is.EqualTo(5));

// Check if email exists
var emailExists = (int)await command.ExecuteScalarAsync() > 0;
Assert.That(emailExists, Is.True);

ExecuteReader – For SELECT Queries

ExecuteReader returns a SqlDataReader – a forward-only, read-only cursor over result rows. It's the most efficient way to read query results.

[Test]
public async Task GetActiveUsers_ReturnsUserList()
{
    using var connection = new SqlConnection(ConnectionString);
    await connection.OpenAsync();

    using var command = new SqlCommand(
        "SELECT UserId, Email, FirstName, LastName " +
        "FROM Users WHERE IsActive = 1",
        connection);

    // Execute and get reader
    using var reader = await command.ExecuteReaderAsync();

    var users = new List();

    // Read rows one at a time
    while (await reader.ReadAsync())
    {
        users.Add(new User
        {
            UserId = reader.GetInt32(0),           // Column index 0
            Email = reader.GetString(1),           // Column index 1
            FirstName = reader.GetString(2),       // Column index 2
            LastName = reader.GetString(3)         // Column index 3
        });
    }

    Assert.That(users.Count, Is.GreaterThan(0), "Should find active users");
    Assert.That(users.All(u => !string.IsNullOrEmpty(u.Email)), Is.True);
}

The SqlDataReader is forward-only – you can't go backward or skip rows. This constraint enables excellent performance. Call ReadAsync() to advance to the next row; it returns false when no more rows exist.

Reading columns by index (0, 1, 2) works but is fragile – if the SELECT list changes order, your code breaks. Better approach using column names:

while (await reader.ReadAsync())
{
    users.Add(new User
    {
        UserId = reader.GetInt32(reader.GetOrdinal("UserId")),
        Email = reader.GetString(reader.GetOrdinal("Email")),
        FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
        LastName = reader.GetString(reader.GetOrdinal("LastName"))
    });
}

GetOrdinal("ColumnName") returns the column's index. This is more verbose but makes code self-documenting and resistant to SELECT list reordering.

Handling NULL Values

Database columns can be NULL, but GetString, GetInt32, etc. throw exceptions if you try to read NULL values. Check for NULL first:

while (await reader.ReadAsync())
{
    var userId = reader.GetInt32(reader.GetOrdinal("UserId"));

    var emailOrdinal = reader.GetOrdinal("Email");
    var email = reader.IsDBNull(emailOrdinal)
        ? null
        : reader.GetString(emailOrdinal);

    // Or use GetFieldValue with nullable types
    var firstName = reader.GetFieldValue(reader.GetOrdinal("FirstName"));
}

These three execution methods – ExecuteNonQuery, ExecuteScalar, and ExecuteReader – handle every database operation you'll need in test automation. Master these patterns, and ADO.NET becomes straightforward.

Parameterized Queries – Security and Safety

You've seen parameters in previous examples, but let's understand why they're absolutely critical and how to use them correctly. Never build SQL queries with string concatenation or interpolation when dealing with variable data.

The SQL Injection Problem

Consider this naive approach:

// ❌ DANGEROUS – Don't do this!
var email = userInput;  // Imagine this came from a test parameter
var sql = $"SELECT * FROM Users WHERE Email = '{email}'";

using var command = new SqlCommand(sql, connection);
var reader = await command.ExecuteReaderAsync();

What if email contains [email protected]'; DROP TABLE Users; --? The resulting SQL becomes:

SELECT * FROM Users WHERE Email = '[email protected]'; DROP TABLE Users; --'

The single quote closes the string, the semicolon ends the SELECT statement, and DROP TABLE Users executes – deleting your entire Users table. This is a SQL injection attack.

"But this is just test code!" you might think. Security vulnerabilities in test code matter because:

• Test code often gets copied to production code
• Tests run against production-like databases in staging environments
• Bad habits in tests become bad habits everywhere
• Test data might come from external sources (CSV files, API responses, configuration)

The Parameterized Query Solution

Parameters separate SQL structure from data values. The database knows exactly what's SQL and what's data – no parsing ambiguity.

// ✅ Safe and correct
var email = userInput;
var sql = "SELECT * FROM Users WHERE Email = @Email";  // @Email is a placeholder

using var command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@Email", email);  // Bind the value

var reader = await command.ExecuteReaderAsync();

Now, if email contains [email protected]'; DROP TABLE Users; --, the database treats the entire string as a literal email value. It searches for a user with that exact (weird) email address – it doesn't interpret the SQL commands embedded in it.

Parameter Syntax Variations

ADO.NET offers multiple ways to add parameters. Choose based on clarity and type safety:

// Simple approach – type inferred from value
command.Parameters.AddWithValue("@Email", "[email protected]");
command.Parameters.AddWithValue("@IsActive", true);

// Explicit type – better control
command.Parameters.Add("@UserId", SqlDbType.Int).Value = 123;
command.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.UtcNow;

// Constructor approach
command.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 100)
{
    Value = "Test"
});

AddWithValue is convenient but can cause performance issues if the database guesses the wrong type. For test automation, the performance difference is negligible, so AddWithValue is usually fine. For production code or performance-critical operations, explicit types are better.

Parameter Reuse and Batching

When inserting multiple rows with the same structure, reuse the command and just change parameter values:

[Test]
public async Task CreateMultipleUsers_UsesParameterReuse()
{
    using var connection = new SqlConnection(ConnectionString);
    await connection.OpenAsync();

    // Prepare command once
    using var command = new SqlCommand(
        "INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive) " +
        "VALUES (@Email, @FirstName, @LastName, @CreatedDate, @IsActive)",
        connection);

    // Add parameters (without values yet)
    command.Parameters.Add("@Email", SqlDbType.NVarChar);
    command.Parameters.Add("@FirstName", SqlDbType.NVarChar);
    command.Parameters.Add("@LastName", SqlDbType.NVarChar);
    command.Parameters.Add("@CreatedDate", SqlDbType.DateTime);
    command.Parameters.Add("@IsActive", SqlDbType.Bit);

    // Insert multiple users
    var users = new[]
    {
        ("[email protected]", "Alice", "Anderson"),
        ("[email protected]", "Bob", "Brown"),
        ("[email protected]", "Carol", "Carter")
    };

    foreach (var (email, firstName, lastName) in users)
    {
        command.Parameters["@Email"].Value = email;
        command.Parameters["@FirstName"].Value = firstName;
        command.Parameters["@LastName"].Value = lastName;
        command.Parameters["@CreatedDate"].Value = DateTime.UtcNow;
        command.Parameters["@IsActive"].Value = true;

        await command.ExecuteNonQueryAsync();
    }
}

This pattern is efficient because the database can reuse the query execution plan for each iteration.

Parameterized queries prevent SQL injection, improve performance through query plan caching, and make your code more readable by separating SQL structure from data values. There's no good reason to skip them.

Transactions for Test Isolation

The most powerful pattern in database testing: wrap your test in a transaction and always rollback. Every INSERT, UPDATE, and DELETE executes normally during the test, but rolling back the transaction makes all changes vanish – instantly and completely.

The Transaction Pattern

Here's the pattern you'll use constantly:

[Test]
public async Task TestOrderProcessing_WithTransactionRollback()
{
    using var connection = new SqlConnection(ConnectionString);
    await connection.OpenAsync();

    // Begin transaction
    using var transaction = connection.BeginTransaction();

    try
    {
        // Create test user
        using var userCommand = new SqlCommand(
            "INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive) " +
            "VALUES (@Email, @FirstName, @LastName, @CreatedDate, @IsActive); " +
            "SELECT SCOPE_IDENTITY();",
            connection,
            transaction);  // Associate command with transaction

        userCommand.Parameters.AddWithValue("@Email", "[email protected]");
        userCommand.Parameters.AddWithValue("@FirstName", "Test");
        userCommand.Parameters.AddWithValue("@LastName", "User");
        userCommand.Parameters.AddWithValue("@CreatedDate", DateTime.UtcNow);
        userCommand.Parameters.AddWithValue("@IsActive", true);

        var userId = Convert.ToInt32(await userCommand.ExecuteScalarAsync());

        // Create order for this user
        using var orderCommand = new SqlCommand(
            "INSERT INTO Orders (UserId, OrderDate, TotalAmount, Status) " +
            "VALUES (@UserId, @OrderDate, @TotalAmount, @Status); " +
            "SELECT SCOPE_IDENTITY();",
            connection,
            transaction);  // Same transaction

        orderCommand.Parameters.AddWithValue("@UserId", userId);
        orderCommand.Parameters.AddWithValue("@OrderDate", DateTime.UtcNow);
        orderCommand.Parameters.AddWithValue("@TotalAmount", 99.99m);
        orderCommand.Parameters.AddWithValue("@Status", "Pending");

        var orderId = Convert.ToInt32(await orderCommand.ExecuteScalarAsync());

        // Verify order exists
        using var verifyCommand = new SqlCommand(
            "SELECT COUNT(*) FROM Orders WHERE OrderId = @OrderId",
            connection,
            transaction);

        verifyCommand.Parameters.AddWithValue("@OrderId", orderId);
        var orderCount = Convert.ToInt32(await verifyCommand.ExecuteScalarAsync());

        Assert.That(orderCount, Is.EqualTo(1), "Order should exist");
    }
    finally
    {
        // Always rollback – even if test fails
        transaction.Rollback();
    }

    // After this point, the user and order don't exist in the database
}

Notice three critical details:

1. Transaction Parameter: Every SqlCommand must receive the transaction object as its third constructor parameter (or set via command.Transaction = transaction). Commands without the transaction won't be part of it.

2. Finally Block: The finally ensures rollback happens even if assertions fail or exceptions occur. This guarantees cleanup.

3. No Commit: We never call transaction.Commit(). The test sees all changes (because they're visible within the transaction), but they never become permanent.

Why This Pattern is Powerful

Compare transaction rollback to manual cleanup:

// Manual cleanup approach
var userId = 0;
var orderId = 0;

try
{
    userId = CreateTestUser();
    orderId = CreateTestOrder(userId);

    // Run test assertions
}
finally
{
    // Delete in reverse dependency order
    if (orderId > 0)
        DeleteOrder(orderId);
    if (userId > 0)
        DeleteUser(userId);
}

This works, but it has problems:

• Order Matters: Must delete child records before parents (orders before users)
• Incomplete Cleanup: If you create related data you forget to track, it leaks
• Slow: Each DELETE is a separate database operation
• Complex: More tables mean more delete statements

Transaction rollback eliminates all these issues. One operation undoes everything, instantly, regardless of complexity.

Transaction Isolation Levels

By default, transactions use READ COMMITTED isolation – you see committed data from other transactions. For test isolation, you might want SERIALIZABLE to prevent other transactions from affecting your test data:

using var transaction = connection.BeginTransaction(IsolationLevel.Serializable);

However, for most test scenarios, the default isolation level works fine. Tests typically run against dedicated test databases where concurrent access is controlled.

When NOT to Use Transaction Rollback

This pattern has limitations:

Testing Transaction Logic: If your application code explicitly commits transactions, wrapping the test in a rollback transaction interferes with what you're testing.

DDL Operations: Some databases (like SQL Server) auto-commit DDL statements (CREATE TABLE, ALTER TABLE). You can't rollback schema changes within a transaction in these databases.

Cross-Database Operations: Distributed transactions across multiple databases require more complex handling.

For these cases, fall back to manual cleanup or database reset strategies.

Transaction rollback is the gold standard for database test isolation. It's fast, reliable, and eliminates entire categories of test pollution bugs. Use it whenever possible.

Building Reusable Test Utilities

Raw ADO.NET code in every test gets verbose quickly. Abstract common patterns into reusable utility methods that make tests cleaner and more maintainable.

Database Test Base Class

Create a base class that handles connection and transaction management:

public abstract class DatabaseTestBase
{
    protected string ConnectionString { get; private set; }
    protected SqlConnection Connection { get; private set; }
    protected SqlTransaction Transaction { get; private set; }

    [OneTimeSetUp]
    public void LoadConfiguration()
    {
        var config = new ConfigurationBuilder()
            .AddJsonFile("appsettings.test.json")
            .Build();

        ConnectionString = config.GetConnectionString("TestDatabase");
    }

    [SetUp]
    public async Task SetupDatabaseConnection()
    {
        Connection = new SqlConnection(ConnectionString);
        await Connection.OpenAsync();
        Transaction = Connection.BeginTransaction();
    }

    [TearDown]
    public void CleanupDatabaseConnection()
    {
        // Always rollback
        Transaction?.Rollback();
        Transaction?.Dispose();

        Connection?.Close();
        Connection?.Dispose();
    }
}

Now your tests inherit from DatabaseTestBase and automatically get connection management:

public class OrderTests : DatabaseTestBase
{
    [Test]
    public async Task CreateOrder_StoresCorrectData()
    {
        // Connection and Transaction are already set up
        var userId = await CreateTestUser("[email protected]");
        var orderId = await CreateTestOrder(userId, 150.00m);

        // Verify
        using var command = new SqlCommand(
            "SELECT TotalAmount FROM Orders WHERE OrderId = @OrderId",
            Connection,
            Transaction);

        command.Parameters.AddWithValue("@OrderId", orderId);
        var amount = (decimal)await command.ExecuteScalarAsync();

        Assert.That(amount, Is.EqualTo(150.00m));

        // Automatic rollback happens in TearDown
    }
}

Data Creation Helpers

Encapsulate common data creation patterns:

public abstract class DatabaseTestBase
{
    // ... previous code ...

    protected async Task CreateTestUser(
        string email,
        string firstName = "Test",
        string lastName = "User",
        bool isActive = true)
    {
        using var command = new SqlCommand(
            "INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive) " +
            "VALUES (@Email, @FirstName, @LastName, @CreatedDate, @IsActive); " +
            "SELECT SCOPE_IDENTITY();",
            Connection,
            Transaction);

        command.Parameters.AddWithValue("@Email", email);
        command.Parameters.AddWithValue("@FirstName", firstName);
        command.Parameters.AddWithValue("@LastName", lastName);
        command.Parameters.AddWithValue("@CreatedDate", DateTime.UtcNow);
        command.Parameters.AddWithValue("@IsActive", isActive);

        return Convert.ToInt32(await command.ExecuteScalarAsync());
    }

    protected async Task CreateTestOrder(
        int userId,
        decimal totalAmount,
        string status = "Pending")
    {
        using var command = new SqlCommand(
            "INSERT INTO Orders (UserId, OrderDate, TotalAmount, Status) " +
            "VALUES (@UserId, @OrderDate, @TotalAmount, @Status); " +
            "SELECT SCOPE_IDENTITY();",
            Connection,
            Transaction);

        command.Parameters.AddWithValue("@UserId", userId);
        command.Parameters.AddWithValue("@OrderDate", DateTime.UtcNow);
        command.Parameters.AddWithValue("@TotalAmount", totalAmount);
        command.Parameters.AddWithValue("@Status", status);

        return Convert.ToInt32(await command.ExecuteScalarAsync());
    }

    protected async Task CreateTestProduct(
        string name,
        decimal price,
        int categoryId = 1,
        int stockQuantity = 100)
    {
        using var command = new SqlCommand(
            "INSERT INTO Products (Name, Price, CategoryId, StockQuantity, IsActive) " +
            "VALUES (@Name, @Price, @CategoryId, @StockQuantity, 1); " +
            "SELECT SCOPE_IDENTITY();",
            Connection,
            Transaction);

        command.Parameters.AddWithValue("@Name", name);
        command.Parameters.AddWithValue("@Price", price);
        command.Parameters.AddWithValue("@CategoryId", categoryId);
        command.Parameters.AddWithValue("@StockQuantity", stockQuantity);

        return Convert.ToInt32(await command.ExecuteScalarAsync());
    }
}

Tests become dramatically simpler:

[Test]
public async Task ComplexOrderScenario_WorksCorrectly()
{
    // Setup test data concisely
    var userId = await CreateTestUser("[email protected]");
    var productId = await CreateTestProduct("Laptop", 999.99m);
    var orderId = await CreateTestOrder(userId, 999.99m);

    // Test your application logic here

    // No cleanup needed – automatic rollback
}

Query Helper Methods

Reduce boilerplate for common query patterns:

public abstract class DatabaseTestBase
{
    // ... previous code ...

    protected async Task ExecuteScalarInt(string sql, params SqlParameter[] parameters)
    {
        using var command = new SqlCommand(sql, Connection, Transaction);
        command.Parameters.AddRange(parameters);

        return Convert.ToInt32(await command.ExecuteScalarAsync());
    }

    protected async Task ExecuteScalarString(string sql, params SqlParameter[] parameters)
    {
        using var command = new SqlCommand(sql, Connection, Transaction);
        command.Parameters.AddRange(parameters);

        var result = await command.ExecuteScalarAsync();
        return result?.ToString();
    }

    protected async Task ExecuteScalar(string sql, params SqlParameter[] parameters)
    {
        using var command = new SqlCommand(sql, Connection, Transaction);
        command.Parameters.AddRange(parameters);

        var result = await command.ExecuteScalarAsync();
        return (T)Convert.ChangeType(result, typeof(T));
    }
}

Usage becomes cleaner:

[Test]
public async Task ValidateUserCount()
{
    await CreateTestUser("[email protected]");
    await CreateTestUser("[email protected]");
    await CreateTestUser("[email protected]");

    var count = await ExecuteScalarInt(
        "SELECT COUNT(*) FROM Users WHERE Email LIKE @Pattern",
        new SqlParameter("@Pattern", "user%"));

    Assert.That(count, Is.EqualTo(3));
}

Assertion Helpers

Create domain-specific assertions that read naturally:

public abstract class DatabaseTestBase
{
    // ... previous code ...

    protected async Task AssertUserExists(int userId)
    {
        var exists = await ExecuteScalarInt(
            "SELECT COUNT(*) FROM Users WHERE UserId = @UserId",
            new SqlParameter("@UserId", userId));

        Assert.That(exists, Is.EqualTo(1), $"User {userId} should exist");
    }

    protected async Task AssertOrderStatus(int orderId, string expectedStatus)
    {
        var status = await ExecuteScalarString(
            "SELECT Status FROM Orders WHERE OrderId = @OrderId",
            new SqlParameter("@OrderId", orderId));

        Assert.That(status, Is.EqualTo(expectedStatus),
            $"Order {orderId} should have status '{expectedStatus}'");
    }

    protected async Task AssertProductStockQuantity(int productId, int expectedQuantity)
    {
        var quantity = await ExecuteScalarInt(
            "SELECT StockQuantity FROM Products WHERE ProductId = @ProductId",
            new SqlParameter("@ProductId", productId));

        Assert.That(quantity, Is.EqualTo(expectedQuantity),
            $"Product {productId} should have {expectedQuantity} units in stock");
    }
}

Tests read like specifications:

[Test]
public async Task ProcessOrder_UpdatesInventoryCorrectly()
{
    var productId = await CreateTestProduct("Widget", 29.99m, stockQuantity: 100);
    var userId = await CreateTestUser("[email protected]");

    // Simulate order processing (your application code)
    await ProcessOrder(userId, productId, quantity: 5);

    // Verify inventory decreased
    await AssertProductStockQuantity(productId, 95);
}

Building utility classes takes initial time but pays off exponentially. Tests become shorter, clearer, and easier to maintain. Common patterns are centralized, making updates trivial.

Error Handling and Resilience

Database operations fail for many reasons – network issues, constraint violations, deadlocks, timeouts. Proper error handling makes tests reliable and debuggable.

Common Database Exceptions

ADO.NET throws SqlException for database-specific errors. The exception includes detailed information about what went wrong:

[Test]
public async Task CreateUser_DuplicateEmail_ThrowsException()
{
    await CreateTestUser("[email protected]");

    // Attempt to create another user with same email
    var ex = Assert.ThrowsAsync(async () =>
    {
        await CreateTestUser("[email protected]");
    });

    // Verify it's a unique constraint violation (error number 2627)
    Assert.That(ex.Number, Is.EqualTo(2627));
    Assert.That(ex.Message, Does.Contain("unique").IgnoreCase);
}

Common SQL Server error numbers you'll encounter:

• 2627 / 2601: Unique constraint violation
• 547: Foreign key constraint violation
• 1205: Deadlock detected
• -1 / -2: Timeout errors

Connection Resilience

Network hiccups cause transient failures. Retry logic makes tests more robust:

protected async Task OpenConnectionWithRetry(int maxAttempts = 3)
{
    var connection = new SqlConnection(ConnectionString);

    for (int attempt = 1; attempt <= maxAttempts; attempt++)
    {
        try
        {
            await connection.OpenAsync();
            return connection;
        }
        catch (SqlException ex) when (attempt < maxAttempts)
        {
            Console.WriteLine($"Connection attempt {attempt} failed: {ex.Message}");
            await Task.Delay(TimeSpan.FromSeconds(Math.Pow(2, attempt))); // Exponential backoff
        }
    }

    // Final attempt without catching exception
    await connection.OpenAsync();
    return connection;
}

Timeout Configuration

Default command timeout is 30 seconds. For tests that insert large volumes of data or run complex queries, increase the timeout:

using var command = new SqlCommand(sql, Connection, Transaction);
command.CommandTimeout = 120; // 2 minutes

// For connection timeouts, set in connection string:
// "Server=...;Database=...;Connection Timeout=60;"

Validating Constraints

Test that your database constraints work correctly:

[Test]
public async Task CreateOrder_WithInvalidUserId_FailsForeignKeyConstraint()
{
    var ex = Assert.ThrowsAsync(async () =>
    {
        await CreateTestOrder(userId: 99999, totalAmount: 100.00m);
    });

    Assert.That(ex.Number, Is.EqualTo(547), "Should be foreign key violation");
}

[Test]
public async Task CreateProduct_WithNegativePrice_FailsCheckConstraint()
{
    // Assuming you have a CHECK constraint: Price >= 0
    var ex = Assert.ThrowsAsync(async () =>
    {
        await CreateTestProduct("Invalid Product", price: -10.00m);
    });

    Assert.That(ex.Message, Does.Contain("check constraint").IgnoreCase);
}

try
{
    await command.ExecuteNonQueryAsync();
}
catch (SqlException ex)
{
    Console.WriteLine($"Query failed: {command.CommandText}");
    foreach (SqlParameter param in command.Parameters)
    {
        Console.WriteLine($"  {param.ParameterName} = {param.Value}");
    }
    throw;
}

Robust error handling distinguishes fragile tests from reliable ones. Expect failures, handle them gracefully, and make error messages informative.

Best Practices and Anti-Patterns

You've learned the mechanics of ADO.NET. Now let's examine common mistakes and how to avoid them.

Anti-Pattern: Forgetting to Dispose Resources

// ❌ Bad – connections and readers leak
var connection = new SqlConnection(connectionString);
connection.Open();
var command = new SqlCommand(sql, connection);
var reader = command.ExecuteReader();
// If exception occurs, resources never get disposed

// ✅ Good – using ensures disposal
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
using var command = new SqlCommand(sql, connection);
using var reader = await command.ExecuteReaderAsync();
// Automatic disposal even if exceptions occur

Anti-Pattern: Opening Connections Too Early

// ❌ Bad – connection open during test setup logic
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

// Do 30 seconds of test preparation
PrepareTestData();
CalculateExpectedResults();

// Finally execute query – connection was open but idle
var result = await command.ExecuteScalarAsync();

// ✅ Good – open connection right before use
PrepareTestData();
CalculateExpectedResults();

using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
var result = await command.ExecuteScalarAsync();

Anti-Pattern: Not Using Async/Await Consistently

// ❌ Bad – mixing sync and async
connection.Open();  // Blocks thread
var result = await command.ExecuteScalarAsync();  // Async

// ✅ Good – async throughout
await connection.OpenAsync();
var result = await command.ExecuteScalarAsync();

Anti-Pattern: Hardcoded SQL in Tests

// ❌ Bad – SQL duplicated across tests
[Test]
public async Task Test1()
{
    var sql = "INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive) VALUES ...";
    // execute
}

[Test]
public async Task Test2()
{
    var sql = "INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive) VALUES ...";
    // execute – if table schema changes, must update everywhere
}

// ✅ Good – centralized helper methods
[Test]
public async Task Test1()
{
    var userId = await CreateTestUser("[email protected]");
}

[Test]
public async Task Test2()
{
    var userId = await CreateTestUser("[email protected]");
    // Schema change? Update CreateTestUser once
}

Best Practice: Use Meaningful Test Data

// ❌ Bad – unclear what's being tested
await CreateTestUser("[email protected]", "X", "Y");

// ✅ Good – test data reveals intent
await CreateTestUser("[email protected]", "Premium", "Customer");

Best Practice: Verify What Matters

// ❌ Bad – testing database behavior, not application logic
var count = await ExecuteScalarInt("SELECT COUNT(*) FROM Users");
Assert.That(count, Is.GreaterThan(0)); // So what?

// ✅ Good – testing specific application behavior
var userId = await CreateTestUser("[email protected]");
await ProcessUserRegistration(userId);
await AssertUserStatus(userId, "Active");
await AssertWelcomeEmailSent(userId);

Best Practice: Test Data Independence

// ❌ Bad – tests depend on each other
[Test, Order(1)]
public async Task CreateUser() { /* creates user id=1 */ }

[Test, Order(2)]
public async Task UpdateUser() { /* assumes user id=1 exists */ }

// ✅ Good – each test is independent
[Test]
public async Task CreateUser()
{
    var userId = await CreateTestUser("[email protected]");
    // Test creation
}

[Test]
public async Task UpdateUser()
{
    var userId = await CreateTestUser("[email protected]");
    // Test update on this specific user
}

Avoiding these anti-patterns from the start saves countless debugging hours. Pattern recognition comes with practice – review your test code regularly and refactor toward clarity.