Full Example

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:

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:

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

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

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

Program.cs

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(quot;{id}. {name}: {description}");
}

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

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

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
• Null-Forgiving Operator

Cheat sheets

• Full Example