Published / updated29 Aug 2026

ADO.NET and Dapper Data Access

Before you start

You need: stages 2 and 3 — the C# objects and the SQL Server schema this stage connects.

Time: 22–28 classes.

Learning objective

Connect the C# from stage 2 to the SQL Server database from stage 3, safely, behind a repository.

Topics

• Connections, commands and readers
• Parameterised queries and SQL injection
• Mapping rows to objects
• Dapper Query, QuerySingle and Execute
• Stored procedure calls and DynamicParameters
• Async data access
• Transactions
• The repository concept

What this stage covers

This is where the two previous stages meet. C# objects on one side, SQL Server tables on the other, and a layer that translates between them.

ADO.NET comes first because it shows what is actually happening — open a connection, send a command, read rows. Dapper then removes the repetitive parts while leaving you in control of the SQL.

Concept	Used later in
Connection lifetime and using	Every repository method
Parameters	Every query — never string concatenation
Object mapping	Entities and DTOs, stage 5
Stored procedure calls	The receipt, from stage 3
Transactions	Recording a payment atomically
async repositories	The whole API, stage 5
The repository interface	Testability, stage 10

Three rules established here recur in every remaining stage:

• Every query is parameterised. User input concatenated into SQL is an injection vulnerability, and it is how O'Brien breaks a search.
• Every connection is inside a using. A leaked connection exhausts the pool, which appears as a timeout that looks like a slow query and is not.
• schoolId is a method parameter that the caller supplies from the auth claim — the repository never guesses it, and it appears in every WHERE.

Worked flow: the fee receipt

public interface IFeeRepository
{
    Task<FeeReceipt> GetReceiptAsync(int schoolId, int paymentId);
    Task<int> RecordPaymentAsync(int schoolId, FeePayment payment);
}

public class FeeRepository : IFeeRepository
{
    private readonly IDbConnectionFactory _connectionFactory;

    public FeeRepository(IDbConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }

    public async Task<FeeReceipt> GetReceiptAsync(int schoolId, int paymentId)
    {
        DynamicParameters parameters = new DynamicParameters();
        parameters.Add("@SchoolId", schoolId);
        parameters.Add("@PaymentId", paymentId);

        using (IDbConnection connection = _connectionFactory.Create())
        {
            return await connection.QuerySingleOrDefaultAsync<FeeReceipt>(
                "dbo.usp_GetFeeReceipt",
                parameters,
                commandType: CommandType.StoredProcedure);
        }
    }
}

Four things in that method are deliberate:

• It calls the stored procedure from stage 3, so the join and the SchoolId filter live in one place.
• schoolId is a parameter, supplied by the caller. The repository cannot invent it.
• QuerySingleOrDefaultAsync returns null when nothing matches, rather than throwing. A payment id from another school legitimately returns nothing.
• The connection is in a using, so it returns to the pool whatever happens.

Recording a payment touches two tables and must be atomic:

public async Task<int> RecordPaymentAsync(int schoolId, FeePayment payment)
{
    using (IDbConnection connection = _connectionFactory.Create())
    {
        connection.Open();

        using (IDbTransaction transaction = connection.BeginTransaction())
        {
            const string insertSql = @"INSERT INTO FeePayment
                                         (SchoolId, FeeAccountId, Amount, PaidOn, PaymentMode, ReceiptNumber)
                                       VALUES
                                         (@SchoolId, @FeeAccountId, @Amount, @PaidOn, @PaymentMode, @ReceiptNumber);
                                       SELECT CAST(SCOPE_IDENTITY() AS INT);";

            int paymentId = await connection.QuerySingleAsync<int>(insertSql, payment, transaction);

            const string updateSql = @"UPDATE FeeAccount
                                       SET PaidAmount = PaidAmount + @Amount
                                       WHERE Id = @FeeAccountId AND SchoolId = @SchoolId;";

            await connection.ExecuteAsync(updateSql,
                new { payment.Amount, payment.FeeAccountId, SchoolId = schoolId },
                transaction);

            transaction.Commit();

            return paymentId;
        }
    }
}

Every command inside the transaction is passed transaction. Miss one and it commits independently — so a rollback leaves the payment recorded and the balance unchanged, or the reverse. This is the single most common transaction bug and it produces no error.

PaidAmount = PaidAmount + @Amount lets the database do the arithmetic. Reading the value into C#, adding, and writing it back loses one of two payments made at the same moment — with no error and a balance that does not reconcile.

Where to learn it

Topic	Read
Connections and connection strings	Track 07 — ADO.NET foundations
Commands and execution	Track 07 — Commands and execution
Parameters and injection	Track 07 — Parameters and injection
Readers and mapping	Track 07 — Readers and mapping
CRUD and procedures	Track 07 — ADO.NET CRUD and procedures
Transactions	Track 07 — ADO.NET transactions
Dapper basics	Track 07 — Dapper fundamentals
Dapper parameters and procedures	Track 07 — Dapper parameters and procedures
Mapping, async, transactions	Track 07 — Mapping, async, transactions
The repository capstone	Track 07 — Repository project

EF Core is an optional alternative — Track 08. Learn Dapper first. Understanding what SQL your code sends is the skill; an ORM that hides it is easier to use and harder to debug.

Stage exercises

From the guided path syllabus:

Execute parameterised CRUD. Implement IStudentRepository against SQL Server with add, get by roll number, list by class and deactivate — every method taking schoolId first.

Convert ADO.NET code to Dapper. Write one method with SqlConnection, SqlCommand and SqlDataReader, then rewrite it with Dapper and compare the line counts.

Call procedures with output parameters. Call a procedure that inserts a payment and returns the new receipt number through an OUTPUT parameter.

Document the complete data flow for the receipt: which method, which procedure, which tables, which parameters, and where schoolId came from.

Debugging drills

Fix connection and parameter errors. Get error 18456 by using a wrong password, 4060 by using a wrong database name, and a parameter-count mismatch by omitting one from DynamicParameters.

Investigate a null mapping. Rename a column in the SQL so it no longer matches the property name, and watch that property arrive as null with no error.

Trace an uncommitted transaction. Remove transaction.Commit() and confirm the payment vanishes when the block ends. Then remove transaction from the second command and confirm half the operation survives a rollback.

Exhaust the pool. Create connections without using in a loop of 200 and read the timeout message. Note that it says "timeout", not "leak".

Practice

1. Work through Track 07's ten articles.
2. Implement IStudentRepository and IFeeRepository against SQL Server with Dapper.
3. Write one method twice — raw ADO.NET and Dapper — and compare.
4. Call usp_GetFeeReceipt with DynamicParameters and map it to FeeReceipt.
5. Build a search query by concatenating user input, then search for O'Brien. Fix it with parameters.
6. Rename a result column and find the resulting null property with a breakpoint.
7. Implement RecordPaymentAsync with a transaction, then omit transaction from the second command and force a rollback.
8. Replace the database-side increment with a read-modify-write, fire two concurrent payments, and lose one.
9. Loop 200 connections without using and exhaust the pool.
10. Make every repository method async and await them properly.
11. Write a repository method that omits the SchoolId filter, then retrieve another school's data through it.

Exercises 7, 8 and 11 all produce wrong results with no exception.

You can now

• Connect C# to SQL Server with ADO.NET and with Dapper
• Parameterise every query
• Map result rows to objects
• Run a multi-statement transaction correctly
• Put data access behind a repository interface

Review questions

1. Why must every command inside a transaction receive the transaction object?
2. Why does the database do the PaidAmount arithmetic rather than C#?
3. What does a leaked connection look like when it finally fails?
4. Where does schoolId come from, and why is it a parameter rather than something the repository reads?

Next: ASP.NET Core Web API