# Exercise: Connecting an App

Write the Data Access Objects for the BooksPlus bookstore (categories, books and customers) with ADO.NET, and run tests that check them against your own database.

Journeyman · C# · T-SQL · Updated November 3, 2026

Now you'll connect an app to a database yourself. The BooksPlus bookstore chain needs the data layer of its new app: a DAO for each of three tables, `Category`, `Book` and `Customer`. The models are ready; the DAOs are yours to write.

## Before you start

The tests run your DAOs against **your own BooksPlus database**, so SQL Server must be running, and BooksPlus created. If you haven't created it yet, run `BooksPlus.sql` from the course's sample databases in SSMS (see *SQL Server Management Studio*).

## Get the exercise code

Open this lesson's GitHub repository, [github.com/ask-codey/exercise-connecting-an-app](https://github.com/ask-codey/exercise-connecting-an-app), 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.

| Folder | What's in it |
|---|---|
| `BooksPlusData/Models` | `Category`, `Book` and `Customer`, ready to use |
| `BooksPlusData/Data` | `CategoryDao`, `BookDao` and `CustomerDao`, for you to finish |
| `BooksPlusData.Tests` | tests for every DAO method |

`appsettings.json` already holds the connection string, using Windows Authentication:

```json
{
  "ConnectionStrings": {
    "BooksPlus": "Server=localhost;Database=BooksPlus;Integrated Security=true;TrustServerCertificate=true;"
  }
}
```

## What to do

`CategoryDao.GetAll()` is finished: read it first, as your example. Then write each of the other methods:

| DAO | Methods |
|---|---|
| `CategoryDao` | `GetById`, `Add` |
| `BookDao` | `GetById`, `GetByCategory`, `SearchByTitle` |
| `CustomerDao` | `GetById`, `GetByCity`, `GetByLastNameStart` |

The comment above each method says what it returns. Run the program with **Ctrl+F5** to see the categories, and the tests with **Test > Run All Tests**.

## Hints

- **Query in SSMS first.** Get each query right against BooksPlus, then put it in a raw string (`"""`) in your method.
- **One parameter per value.** `WHERE BookId = @BookId`, with `command.Parameters.AddWithValue("@BookId", bookId)`. One test tries an SQL injection attack on `SearchByTitle`, and only parameters pass it.
- **`NULL` columns.** Every book's `Summary` is `NULL`. Read nullable columns with `reader["Summary"] as string`, `reader["Price"] as decimal?` and so on.
- **One mapping method per DAO**, like `MapRowToCategory`, used by every method that reads rows.
- **Searches:** `LIKE '%' + @Text + '%'` finds text anywhere; `LIKE @Letters + '%'` finds text at the start.
- **`Add` returns the new id:** follow the insert with `SELECT CAST(SCOPE_IDENTITY() AS int);` and use `ExecuteScalar`. The test that adds a category removes it again afterward.

## When you're done

Every test is green. Look over your three DAOs: is any code repeated that a private method could hold, such as running a query and mapping every row?

## 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-connecting-an-app`), 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-connecting-an-app.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-connecting-an-app.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-connecting-an-app`, 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

Models, DAOs, parameters and mapping: you've built a real data layer, the same shape as the one behind the web apps you'll build next.

## Key terms

- [Data Access Object (DAO)](https://www.ask-codey.com/glossary/data-access-object-dao)
- [Parameterized Query](https://www.ask-codey.com/glossary/parameterized-query)
- [SqlDataReader](https://www.ask-codey.com/glossary/sqldatareader)
- [Model](https://www.ask-codey.com/glossary/model-c-sharp)

## Related

- [ADO.NET Objects](https://www.ask-codey.com/lessons/querying-a-database-with-ado-net-ado-net-objects)
- [Working with User Input](https://www.ask-codey.com/lessons/working-with-user-input-in-csharp)
- [Example: a Product DAO](https://www.ask-codey.com/lessons/mapping-data-to-models-in-csharp-example-a-product-dao)

Source: https://www.ask-codey.com/lessons/exercise-connecting-an-app