Exercise: Rewrite a DAO with Dapper

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, 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:

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 quot;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:

   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:

   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<T>()
• QuerySingleOrDefault<T>()
• Execute()

Related

• Exercise: Connecting an App
• Query and QuerySingle
• Parameters and Mapping to Models