# Exercise: MVC with Dapper

Grow the BooksPlus website - Dapper and dependency injection, attribute routes, a paged grid of partial-view cards, a validated Add a Book form for administrators only, and recently viewed books in the session.

Journeyman · ASP.NET Core · C# · Updated October 30, 2026

The BooksPlus website from *Exercise: BooksPlus* works, but it's a first draft: every controller builds its own DAO, the addresses come from class names, anyone can add a book, and nothing checks what they type. In this exercise you'll use everything from this chapter to make it a real site.

## Before you start

The tests start your website and read **your own BooksPlus database**, so SQL Server must be running and BooksPlus created.

## Get the exercise code

Open this lesson's GitHub repository, [github.com/ask-codey/exercise-mvc-with-dapper](https://github.com/ask-codey/exercise-mvc-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 `BooksPlusWeb.slnx` in Visual Studio.

It's the BooksPlus website as *Exercise: BooksPlus* left it, plus the Dapper package and a ready-made sign-in kit:

| Ready to use | What it is |
|---|---|
| `Services/UserService.cs` | Two users: `admin` / `Bookworm-Admin-2026` (role Admin) and `clerk` / `Bookworm-Clerk-2026` (role Staff) |
| `Controllers/AccountController.cs`, `Views/Account` | Sign in, sign out, and the access-denied page |
| `Views/Shared/_LoginStatus.cshtml` | *Hi, admin* and **Sign out**, already in the layout |
| `Views/Shared/_ValidationScriptsPartial.cshtml` | The client-side validation scripts |

## What to do

1. **Dapper.** Rewrite `BookDao` with Dapper, and add four methods: `Count`, `GetPage`, `GetByIds` and `TitleExists`.
2. **Dependency injection.** Register `BookDao` (scoped, built with the `BooksPlus` connection string) and `UserService` (singleton) in `Program.cs`, and let `HomeController` and `BooksController` ask for a `BookDao`.
3. **Sign-in and sessions.** Turn on cookie authentication, with `/Account/Login` and `/Account/Denied`, and session state, in `Program.cs`.
4. **Addresses.** Attribute routes for `BooksController`: `/books`, `/books/{id}` (ids only), `/books/search?q=...`, and `/books/new` for both the form and its POST.
5. **The list.** Twelve books a page, by title, each a Bootstrap card from a `_BookCard` partial view, in a grid of 1, 2 or 3 columns, with a pager. A page past the last is **Not Found**.
6. **Validation.** Rules on `Book`, with the exact messages in the README: title required and at most 255 characters, category required, a price from $0.01 to $500, and no title that already exists. Show the messages, and turn on client-side validation.
7. **Admins only.** Only the Admin role may open or post **Add a Book**.
8. **Recently viewed.** The last three books a visitor opened, most recent first, at the top of `/books`, kept in the session.

Run the website with **Ctrl+F5**, and the tests with **Test > Run All Tests**.

## Hints

- **Steps 1 to 3 first.** Until `Program.cs` registers the services, no page that needs a `BookDao` opens, and most tests fail with *Unable to resolve service*.
- **Old addresses stop working** once `BooksController` has attribute routes. That's expected; the tag helpers in the views write the new ones.
- **Paging:** `OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY`, after an `ORDER BY`. 111 books make 10 pages.
- **An unchosen category:** make `CategoryId` an `int?`, add a first option with `value=""`, and `[Required]` catches it.
- **The session holds text:** store the ids as JSON with `JsonSerializer`, as in *Session State*.
- **Try it as each user:** sign in as `clerk` and open **Add a Book** to see the access-denied page, then as `admin`.

## When you're done

Every test passes. Open the site on a phone-sized window (**F12**, then the device button) and check the cards stack and the menu folds.

## 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-mvc-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-mvc-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-mvc-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-mvc-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 a complete MVC application: data through Dapper, services through dependency injection, readable addresses, validated input, sign-in with roles, session state and reusable partial views. It's the same structure as professional .NET web apps, and the base for the Web APIs in the next module.

## Key terms

- Dapper
- Dependency Injection
- Attribute Routing
- \[Authorize\]

## Related

- [Exercise: BooksPlus](https://www.ask-codey.com/lessons/exercise-booksplus)
- [Registering Services](https://www.ask-codey.com/lessons/dependency-injection-in-aspnet-core-registering-services)
- [Cookie Authentication and \[Authorize\]](https://www.ask-codey.com/lessons/security-and-sessions-cookie-authentication-and-authorize)
- [Data Annotations and ModelState](https://www.ask-codey.com/lessons/model-binding-and-validation-data-annotations-and-modelstate)

Source: https://www.ask-codey.com/lessons/exercise-mvc-with-dapper