Connecting Apps to SQL Server

Now your C# programs talk to the database. You will configure a project to connect to SQL Server, run queries with ADO.NET, keep user input safe with parameters, and map rows into C# objects.

Updated

Configure the Project for SQL Server

  1. Configure the Project for SQL Server (Journeyman): Add the Microsoft.Data.SqlClient package to a C# project, write a connection string for your local SQL Server, and keep it in appsettings.json.

Querying a Database with ADO.NET

  1. Querying a Database with ADO.NET: an overview (Journeyman): Every database query from C# takes three steps, connect, run the SQL, read the results, and each step has its own ADO.NET object.
  2. ADO.NET Objects (Journeyman): Use SqlConnection to connect, SqlCommand to run SQL, and SqlDataReader to read rows, choosing ExecuteReader, ExecuteScalar or ExecuteNonQuery for each kind of statement.
  3. Full Example (Journeyman): A complete console program that reads the connection string from appsettings.json, queries Northwind's categories, and prints a report.

Working with User Input in C#

  1. Working with User Input (Journeyman): Use what a user types in a query safely with SqlCommand parameters, and see why building SQL from their text opens the door to SQL injection.

Mapping Data to Models in C#

  1. Mapping Data to Models: an overview (Journeyman): Instead of using each row the moment it's read, copy it into a model object, so the program can keep and reuse the data after the reader is gone.
  2. Model Review (Journeyman): Write a model class with one property for each column, choosing C# types that match the SQL types, and nullable types for columns that allow NULL.
  3. Mapping Rows to Models (Journeyman): Read each row of a query into a new model object, handle NULL columns, and collect the objects in a List<T>.
  4. Data Access Objects (Journeyman): Gather all the SQL for one table into a Data Access Object (DAO), so the rest of the program asks for models and never sees the database.
  5. Example: a Product DAO (Journeyman): A complete ProductDao for Northwind, with methods to get, search, add, update and delete products, all with parameters and one mapping method.

Exercise: Connecting an App

  1. Exercise: Connecting an App (Journeyman): 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.