Exercise: Connecting an App

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

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

{
  "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:

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:

   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:

   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)
• Parameterized Query
• SqlDataReader
• Model

Related

• ADO.NET Objects
• Working with User Input
• Example: a Product DAO