C# classics: connecting to SQL Server database using SqlConnection in ADO.NET

I was today demonstrating ADO.NET database access with C#, and wanted to use the classic Northwind sample database, as it's simple and easy to understand. I wanted to share the C# code that allows you to quickly get customer details from the database given a country, but also revise my old code to use modern C# features, like using statements and raw string literals.

In modern .NET (version 10 is the latest at this writing), you cannot anymore use the System.Date.SqlClient namespace, but instead you must install the Microsoft.Data.SqlClient Nuget package.

After this, the code is cleaner than it was in 2009. Here's my current version:

using Microsoft.Data.SqlClient;

string connStr = "Server=localhost\\SQLEXPRESS;Database=Northwind;" +
                 "Trusted_Connection=True;Encrypt=false;";
using SqlConnection conn = new(connStr);
conn.Open();

string sql = """
    SELECT CompanyName, ContactName
    FROM Customers
    WHERE Country = 'Finland'
    """;

using SqlCommand cmd = new(sql, conn);
using SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    string companyName = reader["CompanyName"].ToString() ?? "";
    string contactName = reader["ContactName"].ToString() ?? "";
    Console.WriteLine($"{companyName} ({contactName})");
}

As you can see, no need for nested try..finally blocks anymore.

Happy ADO'ing!