Getting Started with Dapper

Dapper ships as a NuGet package. Add it to your test project:

// Package Manager Console
Install-Package Dapper

// Or dotnet CLI
dotnet add package Dapper

That's the entire setup. No configuration files, no DbContext, no entity registration. Dapper adds extension methods directly to IDbConnection – which means any ADO.NET connection you already know how to open works immediately with Dapper.

using System.Data.SqlClient;
using Dapper;  // Just add this using directive

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

// Dapper extension methods are now available on 'connection'
var users = await connection.QueryAsync<User>("SELECT * FROM Users");

Your existing knowledge of connection strings, connection pooling, and transaction management from ADO.NET applies directly. Dapper doesn't replace ADO.NET's connection layer – it enhances it.

What Dapper Does (and Doesn't Do)

Understanding Dapper's scope prevents misconceptions:

Dapper DOES: Execute SQL queries and map results to C# objects automatically. Handle parameterized queries with anonymous objects. Support transactions. Work with stored procedures. Provide multiple result set mapping.

Dapper DOESN'T: Generate SQL for you. Track changes to objects. Manage database schema or migrations. Handle relationships between objects automatically. Add any abstraction over your SQL.

This focused scope is intentional. Dapper's creators at Stack Overflow wanted maximum performance with minimum magic. You write SQL, Dapper maps results. Everything else stays under your control.

The Mapping Convention

Dapper maps database column names to C# property names using a case-insensitive name match. A column named UserId maps to a property named UserId, userId, or userid. Extra columns in the result set are ignored. Extra properties on the class that have no matching column remain at their default values.

// Database: UserId INT, Email VARCHAR, FirstName VARCHAR, LastName VARCHAR, IsActive BIT
public class User
{
    public int UserId { get; set; }
    public string Email { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool IsActive { get; set; }
    public string FullName => $"{FirstName} {LastName}";  // Computed – no column, no problem
}

var user = await connection.QuerySingleAsync<User>(
    "SELECT UserId, Email, FirstName, LastName, IsActive FROM Users WHERE UserId = @Id",
    new { Id = 1 });

Dapper reads UserId from the result and sets user.UserId. It reads Email and sets user.Email. The computed property FullName is never touched – it has no backing column. This convention works for the vast majority of scenarios without any configuration.

// Database: user_id, user_email → C#: UserId, Email
var user = await connection.QuerySingleAsync<User>(
    "SELECT user_id AS UserId, user_email AS Email FROM users WHERE user_id = @Id",
    new { Id = 1 });

Installation is a single package reference, the mapping convention is intuitive, and your existing ADO.NET skills apply immediately. The learning curve is remarkably shallow.

Core Query Methods

Dapper's API surface is intentionally small. You'll use six methods for the vast majority of database operations in tests. Master these six and you're equipped for almost anything.

Query<T> – Multiple Results

QueryAsync<T> executes a SELECT query and returns all matching rows as a IEnumerable<T>. Use it whenever you expect zero or more results:

// Returns all active users – could be 0, 1, or many
var activeUsers = await connection.QueryAsync<User>(
    "SELECT UserId, Email, FirstName, LastName FROM Users WHERE IsActive = @IsActive",
    new { IsActive = true });

Assert.That(activeUsers.Count(), Is.GreaterThan(0));

// Returns orders for a specific user
var userOrders = await connection.QueryAsync<Order>(
    "SELECT OrderId, OrderDate, TotalAmount, Status " +
    "FROM Orders WHERE UserId = @UserId ORDER BY OrderDate DESC",
    new { UserId = testUserId });

Assert.That(userOrders.All(o => o.UserId == testUserId), Is.True);

If no rows match, QueryAsync returns an empty enumerable – no exception. This is usually correct behavior for test validation queries.

QuerySingle<T> – Exactly One Result

QuerySingleAsync<T> expects exactly one row and throws if zero or multiple rows are returned. Use this for lookups by primary key or unique constraints where any other result count indicates a problem:

// Validate a specific order exists with correct data
var order = await connection.QuerySingleAsync<Order>(
    "SELECT OrderId, TotalAmount, Status FROM Orders WHERE OrderId = @OrderId",
    new { OrderId = createdOrderId });

Assert.That(order.TotalAmount, Is.EqualTo(150.00m));
Assert.That(order.Status, Is.EqualTo("Pending"));

If the order doesn't exist (zero rows), Dapper throws InvalidOperationException: Sequence contains no elements. If somehow two orders have the same ID (shouldn't happen with a proper primary key), it throws Sequence contains more than one element. Either failure instantly highlights a data integrity problem.

QuerySingleOrDefault<T> – Zero or One Result

QuerySingleOrDefaultAsync<T> returns the single matching row or null (for reference types) if no rows match. Throws if multiple rows are returned. Use when absence is a valid outcome:

// Check if user exists – null is valid
var user = await connection.QuerySingleOrDefaultAsync<User>(
    "SELECT UserId, Email FROM Users WHERE Email = @Email",
    new { Email = "[email protected]" });

if (user != null)
{
    // User exists, continue test flow
}
else
{
    // User doesn't exist, test alternative path
}

QueryFirst<T> – First of Many

QueryFirstAsync<T> returns the first row from the result set, ignoring any additional rows. Use when you expect multiple rows but only need one – typically with ordering:

// Get the most recent order for a user
var latestOrder = await connection.QueryFirstAsync<Order>(
    "SELECT OrderId, OrderDate, TotalAmount FROM Orders " +
    "WHERE UserId = @UserId ORDER BY OrderDate DESC",
    new { UserId = testUserId });

Unlike QuerySingle, QueryFirst won't complain about extra rows. It throws only if the result set is empty.

Execute – Data Modification

ExecuteAsync runs commands that don't return result sets – INSERT, UPDATE, DELETE, stored procedure calls. Returns the number of rows affected:

// Update order status
var rowsAffected = await connection.ExecuteAsync(
    "UPDATE Orders SET Status = @Status WHERE OrderId = @OrderId",
    new { Status = "Shipped", OrderId = testOrderId });

Assert.That(rowsAffected, Is.EqualTo(1));

// Delete test data (when not using transaction rollback)
await connection.ExecuteAsync(
    "DELETE FROM OrderItems WHERE OrderId = @OrderId",
    new { OrderId = testOrderId });

// Execute for multiple records using a collection
var userIds = new[] { 1, 2, 3 };
await connection.ExecuteAsync(
    "UPDATE Users SET IsActive = 0 WHERE UserId = @UserId",
    userIds.Select(id => new { UserId = id }));  // Executes once per item

ExecuteScalar<T> – Single Value

ExecuteScalarAsync<T> returns the first column of the first row as type T. Exactly like ADO.NET's ExecuteScalar but with type inference:

// Get the generated ID after insert
var userId = await connection.ExecuteScalarAsync<int>(
    "INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive) " +
    "VALUES (@Email, @FirstName, @LastName, @CreatedDate, @IsActive); " +
    "SELECT SCOPE_IDENTITY();",
    new
    {
        Email = "[email protected]",
        FirstName = "Test",
        LastName = "User",
        CreatedDate = DateTime.UtcNow,
        IsActive = true
    });

Assert.That(userId, Is.GreaterThan(0));

// Get a count
var orderCount = await connection.ExecuteScalarAsync<int>(
    "SELECT COUNT(*) FROM Orders WHERE UserId = @UserId",
    new { UserId = testUserId });

Six methods, complete coverage. Query for collections, QuerySingle for unique lookups, QuerySingleOrDefault for optional lookups, QueryFirst for ordered first results, Execute for modifications, ExecuteScalar for single values. Every test automation database scenario maps to one of these.

Parameters – Simple and Dynamic

Dapper's parameter handling is one of its biggest quality-of-life improvements over ADO.NET. Anonymous objects replace verbose SqlParameter collections in most scenarios.

Anonymous Object Parameters

The simplest and most common parameter style – create an anonymous object with properties matching your parameter names:

// Single parameter
var user = await connection.QuerySingleAsync<User>(
    "SELECT * FROM Users WHERE UserId = @UserId",
    new { UserId = 123 });

// Multiple parameters
var orders = await connection.QueryAsync<Order>(
    "SELECT * FROM Orders WHERE UserId = @UserId AND Status = @Status",
    new { UserId = 123, Status = "Pending" });

// Mixed types – Dapper handles type inference
await connection.ExecuteAsync(
    "INSERT INTO Orders (UserId, OrderDate, TotalAmount, Status) " +
    "VALUES (@UserId, @OrderDate, @TotalAmount, @Status)",
    new
    {
        UserId = 123,
        OrderDate = DateTime.UtcNow,
        TotalAmount = 99.99m,
        Status = "Pending"
    });

Dapper infers SQL parameter types from the C# types. int becomes INT, string becomes NVARCHAR, decimal becomes DECIMAL, DateTime becomes DATETIME, bool becomes BIT. This just works for standard types.

DynamicParameters – When You Need Control

DynamicParameters is Dapper's explicit parameter class. Use it when you need to specify types explicitly, add output parameters, or build parameters dynamically:

var parameters = new DynamicParameters();
parameters.Add("@Email", "[email protected]", DbType.String, size: 255);
parameters.Add("@IsActive", true, DbType.Boolean);
parameters.Add("@CreatedDate", DateTime.UtcNow, DbType.DateTime);

var user = await connection.QuerySingleOrDefaultAsync<User>(
    "SELECT * FROM Users WHERE Email = @Email AND IsActive = @IsActive",
    parameters);

Output Parameters for Stored Procedures

When working with stored procedures that return output parameters, DynamicParameters is essential:

var parameters = new DynamicParameters();
parameters.Add("@Email", "[email protected]");
parameters.Add("@FirstName", "Test");
parameters.Add("@LastName", "User");

// Output parameter – Dapper fills this after execution
parameters.Add("@UserId", dbType: DbType.Int32, direction: ParameterDirection.Output);

await connection.ExecuteAsync("sp_CreateUser", parameters,
    commandType: CommandType.StoredProcedure);

// Read the output value
var newUserId = parameters.Get<int>("@UserId");
Assert.That(newUserId, Is.GreaterThan(0));

IN Clause Parameters – A Common Challenge

SQL IN clauses present a challenge for parameterization. You can't pass a list as a single parameter to standard SQL. Dapper has built-in support for this:

// ❌ This doesn't work in standard ADO.NET
var sql = "SELECT * FROM Products WHERE ProductId IN @Ids";

// ✅ Dapper expands IEnumerable automatically
var productIds = new[] { 1, 2, 3, 4, 5 };
var products = await connection.QueryAsync<Product>(
    "SELECT * FROM Products WHERE ProductId IN @Ids",
    new { Ids = productIds });  // Dapper expands to (1,2,3,4,5)

Dapper translates the collection into proper parameterized syntax: WHERE ProductId IN (@Ids0, @Ids1, @Ids2, @Ids3, @Ids4) with individual parameters for each value. SQL injection stays prevented, and collections work cleanly.

This is incredibly useful in tests when you need to verify a set of created records:

var createdUserIds = new List<int>();
createdUserIds.Add(await CreateTestUser("[email protected]"));
createdUserIds.Add(await CreateTestUser("[email protected]"));
createdUserIds.Add(await CreateTestUser("[email protected]"));

// Verify all three users exist
var users = await connection.QueryAsync<User>(
    "SELECT * FROM Users WHERE UserId IN @Ids",
    new { Ids = createdUserIds });

Assert.That(users.Count(), Is.EqualTo(3));

Parameters in Dapper are almost always simpler than in ADO.NET. Anonymous objects handle 90% of scenarios, DynamicParameters handles the rest. The collection expansion for IN clauses alone saves significant code compared to ADO.NET's manual string-building approaches.

Multi-Mapping – JOIN Queries

Real-world database tests often query data that spans multiple tables. An order belongs to a user. Order items reference products. Dapper's multi-mapping feature handles JOIN queries by splitting result columns across multiple objects automatically.

Single JOIN – Two Objects

Consider querying orders with their user details:

// Result class that holds both objects
public class OrderWithUser
{
    public Order Order { get; set; }
    public User User { get; set; }
}

var sql = @"
    SELECT
        o.OrderId, o.TotalAmount, o.Status, o.OrderDate,
        u.UserId, u.Email, u.FirstName, u.LastName
    FROM Orders o
    INNER JOIN Users u ON o.UserId = u.UserId
    WHERE o.Status = @Status";

var ordersWithUsers = await connection.QueryAsync<Order, User, OrderWithUser>(
    sql,
    (order, user) =>
    {
        // Map function – receives both mapped objects
        return new OrderWithUser { Order = order, User = user };
    },
    new { Status = "Pending" },
    splitOn: "UserId");  // Column name where User object starts

The splitOn parameter is the key. Dapper reads columns in query order. When it encounters the splitOn column, it starts mapping to the next type. In this example, OrderId, TotalAmount, Status, OrderDate map to Order. From UserId onward, columns map to User.

More Common Pattern – Navigation Properties

Instead of wrapper classes, populate navigation properties directly:

// Order class with User navigation property
public class Order
{
    public int OrderId { get; set; }
    public int UserId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
    public string Status { get; set; }
    public User Customer { get; set; }  // Navigation property
}

var sql = @"
    SELECT
        o.OrderId, o.UserId, o.OrderDate, o.TotalAmount, o.Status,
        u.UserId, u.Email, u.FirstName, u.LastName
    FROM Orders o
    INNER JOIN Users u ON o.UserId = u.UserId
    WHERE o.OrderId = @OrderId";

var order = await connection.QuerySingleAsync<Order, User, Order>(
    sql,
    (order, user) =>
    {
        order.Customer = user;  // Assign user to navigation property
        return order;
    },
    new { OrderId = testOrderId },
    splitOn: "UserId");

The assertion then reads naturally:

Assert.That(order.Status, Is.EqualTo("Shipped"));
Assert.That(order.Customer.Email, Is.EqualTo("[email protected]"));

Multiple JOINs – Three Objects

Dapper supports up to 7 types in a single multi-mapping call. For an order with its items and products:

public class OrderItem
{
    public int OrderItemId { get; set; }
    public int OrderId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public Product Product { get; set; }
}

var sql = @"
    SELECT
        oi.OrderItemId, oi.OrderId, oi.Quantity, oi.UnitPrice,
        p.ProductId, p.Name, p.Price
    FROM OrderItems oi
    INNER JOIN Products p ON oi.ProductId = p.ProductId
    WHERE oi.OrderId = @OrderId";

var orderItems = await connection.QueryAsync<OrderItem, Product, OrderItem>(
    sql,
    (item, product) =>
    {
        item.Product = product;
        return item;
    },
    new { OrderId = testOrderId },
    splitOn: "ProductId");

Assert.That(orderItems.Sum(oi => oi.Quantity * oi.UnitPrice),
    Is.EqualTo(expectedTotal));

When NOT to Use Multi-Mapping

Multi-mapping is powerful but adds complexity. For simple test validation, multiple individual queries are often clearer:

// Sometimes simpler to run two queries
var order = await connection.QuerySingleAsync<Order>(
    "SELECT * FROM Orders WHERE OrderId = @Id",
    new { Id = orderId });

var user = await connection.QuerySingleAsync<User>(
    "SELECT * FROM Users WHERE UserId = @Id",
    new { Id = order.UserId });

// Same assertions, but more readable code
Assert.That(order.Status, Is.EqualTo("Shipped"));
Assert.That(user.Email, Is.EqualTo("[email protected]"));

Two simple queries are often more readable than one complex multi-mapped query. Reserve multi-mapping for scenarios where a JOIN is semantically important – testing that a relationship exists correctly, validating join conditions, or when the combined data is the assertion target.

Multi-mapping unlocks clean validation of relational data, but use it thoughtfully. The goal is test clarity, not SQL virtuosity.

Transactions with Dapper

Dapper integrates seamlessly with ADO.NET transactions. All Dapper methods accept an optional transaction parameter – pass your existing SqlTransaction and every command participates automatically.

The Dapper Transaction Pattern

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

    try
    {
        // Create test user – participates in transaction
        var userId = await connection.ExecuteScalarAsync<int>(
            "INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive) " +
            "VALUES (@Email, @FirstName, @LastName, @CreatedDate, @IsActive); " +
            "SELECT SCOPE_IDENTITY();",
            new
            {
                Email = "[email protected]",
                FirstName = "Test",
                LastName = "Customer",
                CreatedDate = DateTime.UtcNow,
                IsActive = true
            },
            transaction);  // Pass transaction here

        // Create order – same transaction
        var orderId = await connection.ExecuteScalarAsync<int>(
            "INSERT INTO Orders (UserId, OrderDate, TotalAmount, Status) " +
            "VALUES (@UserId, @OrderDate, @TotalAmount, @Status); " +
            "SELECT SCOPE_IDENTITY();",
            new { UserId = userId, OrderDate = DateTime.UtcNow,
                  TotalAmount = 299.99m, Status = "Pending" },
            transaction);

        // Validate using QuerySingle
        var order = await connection.QuerySingleAsync<Order>(
            "SELECT OrderId, TotalAmount, Status FROM Orders WHERE OrderId = @Id",
            new { Id = orderId },
            transaction);

        Assert.That(order.TotalAmount, Is.EqualTo(299.99m));
        Assert.That(order.Status, Is.EqualTo("Pending"));
    }
    finally
    {
        transaction.Rollback();  // Always rollback
    }
}

The transaction parameter in Dapper methods works exactly like the transaction parameter in ADO.NET commands. Commands without the transaction run outside it (and won't be rolled back), so pass it consistently.

Simplifying with a Base Class

As with ADO.NET, abstract the connection and transaction lifecycle:

public abstract class DapperTestBase
{
    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 SetupDatabase()
    {
        Connection = new SqlConnection(ConnectionString);
        await Connection.OpenAsync();
        Transaction = Connection.BeginTransaction();
    }

    [TearDown]
    public void CleanupDatabase()
    {
        Transaction?.Rollback();
        Transaction?.Dispose();
        Connection?.Close();
        Connection?.Dispose();
    }

    // Dapper helper that automatically includes transaction
    protected Task<IEnumerable<T>> Query<T>(string sql, object param = null) =>
        Connection.QueryAsync<T>(sql, param, Transaction);

    protected Task<T> QuerySingle<T>(string sql, object param = null) =>
        Connection.QuerySingleAsync<T>(sql, param, Transaction);

    protected Task<T> QuerySingleOrDefault<T>(string sql, object param = null) =>
        Connection.QuerySingleOrDefaultAsync<T>(sql, param, Transaction);

    protected Task<int> Execute(string sql, object param = null) =>
        Connection.ExecuteAsync(sql, param, Transaction);

    protected Task<T> ExecuteScalar<T>(string sql, object param = null) =>
        Connection.ExecuteScalarAsync<T>(sql, param, Transaction);
}

The wrappers automatically pass the transaction, so tests never forget to include it:

public class OrderTests : DapperTestBase
{
    [Test]
    public async Task UpdateOrder_ChangesStatus()
    {
        // No transaction wrangling – helpers handle it
        var userId = await ExecuteScalar<int>(
            "INSERT INTO Users ... SELECT SCOPE_IDENTITY();",
            new { Email = "[email protected]", ... });

        var orderId = await ExecuteScalar<int>(
            "INSERT INTO Orders ... SELECT SCOPE_IDENTITY();",
            new { UserId = userId, ... });

        await Execute(
            "UPDATE Orders SET Status = @Status WHERE OrderId = @OrderId",
            new { Status = "Shipped", OrderId = orderId });

        var order = await QuerySingle<Order>(
            "SELECT Status FROM Orders WHERE OrderId = @Id",
            new { Id = orderId });

        Assert.That(order.Status, Is.EqualTo("Shipped"));
    }
}

Transaction support in Dapper is direct and unsurprising. The extra transaction parameter is the only ceremony compared to connection-only usage – pass it everywhere or abstract it away in a base class helper.

Test Data Factories with Dapper

Beyond the base class pattern, you can extract test data creation into dedicated factory classes. A test data factory centralizes all creation logic, making it reusable across test classes and maintainable as schemas evolve.

The Factory Pattern

public class TestDataFactory
{
    private readonly IDbConnection _connection;
    private readonly IDbTransaction _transaction;

    public TestDataFactory(IDbConnection connection, IDbTransaction transaction = null)
    {
        _connection = connection;
        _transaction = transaction;
    }

    public async Task<int> CreateUser(
        string email = null,
        string firstName = "Test",
        string lastName = "User",
        bool isActive = true)
    {
        // Generate unique email if not specified
        email ??= $"test-{Guid.NewGuid():N}@example.com";

        return await _connection.ExecuteScalarAsync<int>(
            "INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive) " +
            "VALUES (@Email, @FirstName, @LastName, @CreatedDate, @IsActive); " +
            "SELECT SCOPE_IDENTITY();",
            new { Email = email, FirstName = firstName, LastName = lastName,
                  CreatedDate = DateTime.UtcNow, IsActive = isActive },
            _transaction);
    }

    public async Task<int> CreateOrder(
        int userId,
        decimal totalAmount = 100.00m,
        string status = "Pending",
        DateTime? orderDate = null)
    {
        return await _connection.ExecuteScalarAsync<int>(
            "INSERT INTO Orders (UserId, OrderDate, TotalAmount, Status) " +
            "VALUES (@UserId, @OrderDate, @TotalAmount, @Status); " +
            "SELECT SCOPE_IDENTITY();",
            new { UserId = userId, OrderDate = orderDate ?? DateTime.UtcNow,
                  TotalAmount = totalAmount, Status = status },
            _transaction);
    }

    public async Task<int> CreateProduct(
        string name = null,
        decimal price = 29.99m,
        int categoryId = 1,
        int stockQuantity = 100)
    {
        name ??= $"Product-{Guid.NewGuid():N[..8]}";

        return await _connection.ExecuteScalarAsync<int>(
            "INSERT INTO Products (Name, Price, CategoryId, StockQuantity, IsActive) " +
            "VALUES (@Name, @Price, @CategoryId, @StockQuantity, 1); " +
            "SELECT SCOPE_IDENTITY();",
            new { Name = name, Price = price, CategoryId = categoryId,
                  StockQuantity = stockQuantity },
            _transaction);
    }

    public async Task CreateOrderItem(int orderId, int productId, int quantity, decimal unitPrice)
    {
        await _connection.ExecuteAsync(
            "INSERT INTO OrderItems (OrderId, ProductId, Quantity, UnitPrice) " +
            "VALUES (@OrderId, @ProductId, @Quantity, @UnitPrice)",
            new { OrderId = orderId, ProductId = productId,
                  Quantity = quantity, UnitPrice = unitPrice },
            _transaction);
    }
}

Builder Pattern for Complex Scenarios

For tests requiring complex object graphs, chain factory calls using a builder:

public class OrderScenarioBuilder
{
    private readonly TestDataFactory _factory;
    private int _userId;
    private int _orderId;
    private readonly List<int> _productIds = new();

    public OrderScenarioBuilder(TestDataFactory factory)
    {
        _factory = factory;
    }

    public async Task<OrderScenarioBuilder> WithUser(string email = null)
    {
        _userId = await _factory.CreateUser(email);
        return this;
    }

    public async Task<OrderScenarioBuilder> WithOrder(
        decimal total = 100m, string status = "Pending")
    {
        _orderId = await _factory.CreateOrder(_userId, total, status);
        return this;
    }

    public async Task<OrderScenarioBuilder> WithProduct(
        decimal price = 50m, int quantity = 2)
    {
        var productId = await _factory.CreateProduct(price: price);
        await _factory.CreateOrderItem(_orderId, productId, quantity, price);
        _productIds.Add(productId);
        return this;
    }

    public (int UserId, int OrderId, IReadOnlyList<int> ProductIds) Build() =>
        (_userId, _orderId, _productIds.AsReadOnly());
}

Tests using the builder read like test specifications:

[Test]
public async Task ProcessPayment_UpdatesOrderAndInventory()
{
    var factory = new TestDataFactory(Connection, Transaction);
    var builder = new OrderScenarioBuilder(factory);

    var (userId, orderId, productIds) = await (await (await builder
        .WithUser("[email protected]"))
        .WithOrder(total: 100.00m, status: "Pending"))
        .WithProduct(price: 50.00m, quantity: 2)
        .Build();

    // Act: Process payment through your application
    await paymentService.ProcessOrder(orderId);

    // Assert: Verify status changed
    var order = await QuerySingle<Order>(
        "SELECT Status FROM Orders WHERE OrderId = @Id",
        new { Id = orderId });

    Assert.That(order.Status, Is.EqualTo("Paid"));
}

// Always unique – parallel tests can't conflict on email
var email = $"test-{Guid.NewGuid():N}@example.com";

// Short GUID fragment for readable product names
var name = $"Product-{Guid.NewGuid().ToString()[..8]}";

Test data factories centralize creation logic, make tests concise and readable, and provide a single maintenance point when schemas change. The investment in building them pays back tenfold as your test suite grows.

Common Pitfalls and Performance Tips

Dapper is difficult to misuse, but a few patterns cause problems in test automation contexts specifically.

Pitfall: Forgetting the Transaction Parameter

Dapper's transaction parameter is optional. Forgetting it on even one command creates a subtle bug – that command commits immediately while others roll back:

// ❌ Bug: second command has no transaction
var userId = await connection.ExecuteScalarAsync<int>(
    "INSERT INTO Users ...; SELECT SCOPE_IDENTITY();",
    new { Email = "..." },
    transaction);  // ✅ Has transaction

var orderId = await connection.ExecuteScalarAsync<int>(
    "INSERT INTO Orders ...; SELECT SCOPE_IDENTITY();",
    new { UserId = userId });  // ❌ Missing transaction! This commits immediately.

transaction.Rollback();  // User is rolled back, but order persists!

The base class wrapper pattern from the previous section prevents this by always including the transaction in helper methods.

Pitfall: Using SELECT * in Tests

// ❌ Fragile – column additions or removals may silently affect mapping
var order = await connection.QuerySingleAsync<Order>(
    "SELECT * FROM Orders WHERE OrderId = @Id",
    new { Id = orderId });

// ✅ Explicit columns – resilient to schema changes
var order = await connection.QuerySingleAsync<Order>(
    "SELECT OrderId, UserId, TotalAmount, Status FROM Orders WHERE OrderId = @Id",
    new { Id = orderId });

Explicit column lists make queries self-documenting and prevent surprising mapping issues when schemas evolve. They also prevent unnecessary data transfer when tables have large text columns irrelevant to your test.

Pitfall: N+1 in Test Setup

When creating many related records, avoid the N+1 pattern:

// ❌ N+1 – separate insert per item (N database roundtrips for N items)
foreach (var item in orderItems)
{
    await connection.ExecuteAsync(
        "INSERT INTO OrderItems (...) VALUES (...)",
        item, transaction);
}

// ✅ Batch insert – single roundtrip
await connection.ExecuteAsync(
    "INSERT INTO OrderItems (OrderId, ProductId, Quantity, UnitPrice) " +
    "VALUES (@OrderId, @ProductId, @Quantity, @UnitPrice)",
    orderItems,  // Dapper accepts IEnumerable – executes once per item but more efficiently
    transaction);

When Dapper receives a collection as the parameter argument, it executes the command once per item. This is still multiple database operations, but it reuses the prepared statement. For very large batches (thousands of rows), consider ADO.NET's SqlBulkCopy instead.

Pitfall: Mapping Enum Values

Dapper maps database integers to C# enums automatically, but string-to-enum requires a custom type handler:

// Database stores "Pending", "Shipped", "Delivered" as strings
public enum OrderStatus { Pending, Shipped, Delivered }

// ❌ This fails – can't map "Pending" string to enum
var status = await connection.ExecuteScalarAsync<OrderStatus>(
    "SELECT Status FROM Orders WHERE OrderId = @Id",
    new { Id = orderId });

// ✅ Option 1: Map to string, parse manually
var statusStr = await connection.ExecuteScalarAsync<string>(
    "SELECT Status FROM Orders WHERE OrderId = @Id",
    new { Id = orderId });
var status = Enum.Parse<OrderStatus>(statusStr);

// ✅ Option 2: Map through an intermediary object
var order = await connection.QuerySingleAsync<Order>(
    "SELECT OrderId, Status FROM Orders WHERE OrderId = @Id",
    new { Id = orderId });
// Order.Status is a string – convert in your assertion
Assert.That(order.Status, Is.EqualTo("Shipped"));

Performance Tip: Buffered vs. Unbuffered

By default, QueryAsync buffers all results in memory before returning (buffered: true). For test data validation with large result sets, use unbuffered streaming:

// Buffered (default) – loads all 10,000 rows into memory
var allProducts = await connection.QueryAsync<Product>(
    "SELECT * FROM Products",
    buffered: true);

// Unbuffered – streams rows as you enumerate
var allProducts = await connection.QueryAsync<Product>(
    "SELECT * FROM Products",
    buffered: false);

// Process without loading entire set
await foreach (var product in allProducts)
{
    // Validate each row
}

For most test validation scenarios, buffered queries are fine. Use unbuffered mode when validating large data sets during performance tests or data migration verification.

These pitfalls are edge cases you'll encounter as your test suite matures. Recognizing them early prevents head-scratching debugging sessions down the road.