Configure the Project for SQL Server

After this lesson you can

• Add a NuGet package to a C# project from the terminal
• Write a connection string for SQL Server and read it from appsettings.json

Key takeaways

• dotnet add package Microsoft.Data.SqlClient adds the SQL Server driver from NuGet and writes a PackageReference into the .csproj file.
• A connection string, such as Server=localhost;Database=Northwind;, tells the driver which server and database to use and how to sign in.
• Integrated Security=true signs in as your Windows account, so there's no password in the connection string.
• A connection string with a password is a secret, and secrets never belong in your code or in Git.
• Keep the connection string in appsettings.json under ConnectionStrings, and read it with GetConnectionString("Northwind").

So far, your C# programs and your SQL queries have lived apart: one in Visual Studio, the other in SSMS. In this chapter you'll connect them, so a C# program can send SQL to the server and use the rows that come back.

The part of .NET that talks to databases is called ADO.NET. Before you can use it with SQL Server, the project needs two things: the SQL Server driver, and the address of the database, its connection string.

Packages: code you add to a project

.NET's built-in libraries cover a lot, but not everything. Extra libraries come as packages from NuGet, .NET's package library at nuget.org. A project lists the packages it uses in its .csproj file, and dotnet downloads them for you.

Make a console project, and add the SQL Server driver, Microsoft.Data.SqlClient:

mkdir -p ~/bootcamp/data
cd ~/bootcamp/data
dotnet new console -n NorthwindReport
cd NorthwindReport
dotnet add package Microsoft.Data.SqlClient
dotnet add package System.Security.Cryptography.Pkcs

The second package is there for a safety reason: SqlClient 7.1 uses an older version of it, with known security problems, and adding the newer version yourself replaces it. Without it, dotnet warns you about a vulnerable package every time you build. (Every database driver is a little different; this is the one SQL Server needs.)

dotnet add package writes the packages into the project file:

<ItemGroup>
  <PackageReference Include="Microsoft.Data.SqlClient" Version="7.1.1" />
  <PackageReference Include="System.Security.Cryptography.Pkcs" Version="10.0.14" />
</ItemGroup>

In Visual Studio, you can do the same with Project > Manage NuGet Packages...: search for the package on the Browse tab, and click Install.

The connection string

A connection string tells the driver where the database is and how to sign in, as a list of Name=value; settings:

Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;

Because the connection uses your Windows account, there's no password in it. That matters: a connection string with a password is a secret, and secrets never belong in your code or in Git.

Keep it in appsettings.json

You could type the connection string straight into your code, but then every class that needs it has its own copy, and moving to another database means hunting them all down. Put it in a settings file instead. Add a file named appsettings.json to the project:

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

Tell the project to copy it next to the program when it builds, by adding this to the .csproj file:

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

Then add the package that reads settings files:

dotnet add package Microsoft.Extensions.Configuration.Json

And read the connection string by its name:

using Microsoft.Extensions.Configuration;

IConfiguration configuration = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json")
    .Build();

string? connectionString = configuration.GetConnectionString("Northwind");
Console.WriteLine(connectionString);

GetConnectionString("Northwind") looks in the ConnectionStrings section for the setting named Northwind. Later in the course, ASP.NET Core apps read appsettings.json for you, in exactly this shape.

Common problems

Check yourself

2 questions to try on the lesson page.

So what

A driver package and a connection string are all a program needs to reach a database. Keep the connection string in one settings file, with no password in it, and every part of your app can find the data.

Key terms

• NuGet
• Package Reference
• Connection String
• appsettings.json

Cheat sheets

• Configure the Project for SQL Server