# Full Example

A complete console program that reads the connection string from appsettings.json, queries Northwind's categories, and prints a report.

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

## After this lesson you can

- Write a console program that queries SQL Server and prints the results

## Key takeaways

- A program that shows data reads its settings, opens a connection, runs a command, and reads the rows with a reader.
- A raw string literal, between `"""` and `"""`, holds a multi-line SQL query laid out just as you would in SSMS.
- The `!` in `GetConnectionString("Northwind")!` tells C# you're sure the setting is there.
- If the program can't connect, check that SSMS can connect to `localhost` and that the database name is right.

Here's everything from this chapter so far in one program. It reads the connection string from `appsettings.json`, then prints every Northwind category with its description.

## The project

Make the project and add its packages, as in *Configure the Project for SQL Server*:

```bash
dotnet new console -n CategoryReport
cd CategoryReport
dotnet add package Microsoft.Data.SqlClient
dotnet add package System.Security.Cryptography.Pkcs
dotnet add package Microsoft.Extensions.Configuration.Json
```

Add `appsettings.json`:

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

And copy it next to the program on every build, in `CategoryReport.csproj`:

```xml
<ItemGroup>
  <None Update="appsettings.json" CopyToOutputDirectory="PreserveNewest" />
</ItemGroup>
```

## Program.cs

```csharp
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;

// Read the connection string from appsettings.json
IConfiguration configuration = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json")
    .Build();
string connectionString = configuration.GetConnectionString("Northwind")!;

// 1. Connect
using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

// 2. Run the SQL
string sql = """
    SELECT CategoryId
        , CategoryName
        , Description
    FROM Categories
    ORDER BY CategoryName;
    """;
using SqlCommand command = new SqlCommand(sql, connection);
using SqlDataReader reader = command.ExecuteReader();

// 3. Read the results
Console.WriteLine("Northwind categories");
Console.WriteLine("--------------------");
while (reader.Read())
{
    int id = reader.GetInt32(0);
    string name = reader.GetString(1);
    string? description = reader.IsDBNull(2) ? null : reader.GetString(2);

    Console.WriteLine($"{id}. {name}: {description}");
}
```

Run it with `dotnet run`, or **Ctrl+F5** in Visual Studio:

```text
Northwind categories
--------------------
1. Beverages: Soft drinks, coffees, teas, beers, and ales
2. Condiments: Sweet and savory sauces, relishes, spreads, and seasonings
3. Confections: Desserts, candies, and sweet breads
4. Dairy Products: Cheeses
5. Grains/Cereals: Breads, crackers, pasta, and cereal
6. Meat/Poultry: Prepared meats
7. Produce: Dried fruit and bean curd
8. Seafood: Seaweed and fish
```

## Two new pieces of C#

- **`"""` raw strings.** Three double quotes start a **raw string literal**, which can span several lines. It's the neatest way to put a multi-line SQL query in C#: lay it out just as you would in SSMS. The indentation of the closing `"""` is removed from every line.
- **The `!` after `GetConnectionString("Northwind")`.** `GetConnectionString` returns `string?`, because the setting might be missing. The `!` tells C# you're sure it's there; if it isn't, the program fails when it opens the connection, with a clear error.

## Common problems

| What you see | What to do |
|---|---|
| `A network-related or instance-specific error occurred` | SQL Server isn't running, or the `Server=` part is wrong. Check that SSMS can connect to `localhost`. |
| `Cannot open database "Northwind" requested by the login` | The database name is wrong, or you haven't created Northwind yet. |
| `The certificate chain was issued by an authority that is not trusted` | Add `TrustServerCertificate=true;` to the connection string. |
| `Invalid object name 'Category'` | The SQL has a typo: the table is `Categories`. Try the query in SSMS first. |

## Check yourself

1 question to try on the lesson page.

## So what

This is the skeleton of every data-driven program: settings, connection, command, reader. From here on, you'll mostly be changing the SQL and what you do with the rows.

## Key terms

- [Raw String Literal](https://www.ask-codey.com/glossary/raw-string-literal)
- [Null-Forgiving Operator](https://www.ask-codey.com/glossary/null-forgiving-operator)

## Cheat sheets

- [Full Example](https://www.ask-codey.com/cheat-sheets/querying-a-database-with-ado-net-full-example-cheat-sheet)

Source: https://www.ask-codey.com/lessons/querying-a-database-with-ado-net-full-example