# Exercise: Rewrite a DAO with Dapper

Use Dapper to rewrite the BooksPlus DAOs from the Connecting an App exercise, add a rename and a lookup by a list of ids, and pass the same tests with a fraction of the code.

Journeyman · C# · T-SQL · Updated October 30, 2026

In *Exercise: Connecting an App*, you wrote the BooksPlus DAOs with ADO.NET. Now you'll write them again with Dapper, and see how much code disappears while every test keeps passing.

## Before you start

The tests run against **your own BooksPlus database**, so SQL Server must be running and BooksPlus created (run `BooksPlus.sql` from the course's sample databases if you haven't).

## Get the exercise code

Open this lesson's GitHub repository, [github.com/ask-codey/exercise-rewrite-a-dao-with-dapper](https://github.com/ask-codey/exercise-rewrite-a-dao-with-dapper), also linked from the **Exercise** box beside the lesson. Click **Code**, then **Download ZIP**, and unzip it into your `bootcamp` folder. Or clone it with Git. Then open `BooksPlusData.slnx` in Visual Studio.

It's the same project as before, with the Dapper package added and the DAOs' method bodies taken out. `CategoryDao.GetAll()` is finished with Dapper, as an example to follow.

## What to do

Write every method with Dapper:

| DAO | Methods |
|---|---|
| `CategoryDao` | `GetById`, `Add`, and the new `Rename` |
| `BookDao` | `GetById`, `GetByCategory`, `SearchByTitle`, and the new `GetByIds` |
| `CustomerDao` | `GetById`, `GetByCity`, `GetByLastNameStart` |

The two new methods:

- **`Rename(categoryId, newName)`** gives a category a new name, and returns `true` if it found the category, `false` if not.
- **`GetByIds(bookIds)`** returns the books whose ids are in the list, in order of title, skipping ids with no book.

Run the tests with **Test > Run All Tests**. Each method you finish turns its tests green.

## Hints

- **Keep your SQL.** Dapper replaces the code around a query, not the query: start from your ADO.NET solution, or write each query in SSMS first. In `BookDao`, the `Columns` constant lists the columns to select; put it into the query with string interpolation, such as `$"SELECT {Columns} FROM Book WHERE BookId = @BookId;"`.
- **Pick the method by rows:** `Query<T>` for many, `QuerySingleOrDefault<T>` for one or none, `QuerySingle<int>` for a new id, `Execute` for the `UPDATE`.
- **Parameters:** an anonymous object whose property names match the `@` names, such as `new { BookId = bookId }`; or pass the `Category` itself to `Add`.
- **The ISBN:** it's a fixed-width column padded with spaces. `RTRIM(ISBN) AS Isbn` trims it and gives it the property's name.
- **The list:** `WHERE BookId IN @BookIds`, with no parentheses.

## When you're done

Every test passes. Put your ADO.NET `BookDao` beside your Dapper one: same queries, same results, and most of the code gone.

## Submit your exercise

The exercise is how you finish this lesson. When your work is done, put it on GitHub in a repo of your own, then submit that repo's address here.

1. On GitHub, click **+**, then **New repository**. Name it (for example `exercise-rewrite-a-dao-with-dapper`), choose **Public**, and click **Create repository**. Leave out the README: your folder already has one.
2. In a terminal in your exercise folder (Git Bash on Windows), send your work to your new repo. If you downloaded the ZIP:

   ```bash
   git init -b main
   git add .
   git commit -m "My solution"
   git remote add origin https://github.com/your-name/exercise-rewrite-a-dao-with-dapper.git
   git push -u origin main
   ```

   If you cloned the starter instead, point your copy at your own repo, then commit and push:

   ```bash
   git remote set-url origin https://github.com/your-name/exercise-rewrite-a-dao-with-dapper.git
   git add .
   git commit -m "My solution"
   git push -u origin main
   ```

3. Paste your repo's address, such as `https://github.com/your-name/exercise-rewrite-a-dao-with-dapper`, into **Submit your exercise** on this page. The site checks that the repo is public and holds your commits, and then the lesson is complete.

Changed something after you submitted? Push it, and submit again: your latest submission is the one that counts.

## So what

That's why most .NET teams reach for Dapper (or another ORM) instead of raw ADO.NET: the SQL stays yours, and the repetitive mapping code is never written again.

## Key terms

- Dapper
- Query&lt;T>()
- QuerySingleOrDefault&lt;T>()
- Execute()

## Related

- [Exercise: Connecting an App](https://www.ask-codey.com/lessons/exercise-connecting-an-app)
- [Query and QuerySingle](https://www.ask-codey.com/lessons/dapper-query-and-querysingle)
- [Parameters and Mapping to Models](https://www.ask-codey.com/lessons/dapper-parameters-and-mapping-to-models)

Source: https://www.ask-codey.com/lessons/exercise-rewrite-a-dao-with-dapper