Published / updated29 Aug 2026

ADO.NET Foundations

Before you start

You need: C# classes and using blocks (Track 03), and SQL SELECT (Track 06 Articles 01–04).

In Visual Studio: a Console App project, plus the Microsoft.Data.SqlClient package from NuGet.

Time: about 50 minutes, plus the practice.

Learning objective

Explain every step between a C# method call and a row returned from SQL Server, and open and release connections correctly.

Topics

• The data-access flow
• Providers and the Microsoft.Data.SqlClient package
• Connection strings and where they belong
• SqlConnection and its states
• Connection pooling
• using and deterministic disposal
• Diagnosing a connection failure

Terminology

Term	Meaning
Connection string	The configuration naming server, database, and credentials
Provider	The library that speaks a specific database's protocol
Connection pool	A reusable set of open connections held by the provider
Command	One SQL statement or procedure call, with its parameters
Reader	A forward-only stream of result rows
Mapping	Turning a result row into a C# object

The flow

C# application
  → ADO.NET (Microsoft.Data.SqlClient)
    → TDS protocol over TCP
      → SQL Server
        → Query or stored procedure
          → Result set
      ← rows
  ← C# objects

Every data-access technology sits on this. Dapper is a thin layer over ADO.NET; Entity Framework Core is a thick one. Both open a DbConnection, build a DbCommand, and read a DbDataReader.

Learning ADO.NET first means that when Dapper behaves unexpectedly you know what it is doing underneath, rather than treating it as magic.

The provider

Right-click the project → Manage NuGet Packages → Browse, search for the package, and click Install.

The Package Manager Console (Tools → NuGet Package Manager → Package Manager Console) does the same thing typed:

Install-Package Microsoft.Data.SqlClient

Check the Dependencies node in Solution Explorer afterwards. If the package is not listed there, it went into a different project — the console installs into whatever the Default project dropdown says, not the one you have open.

using Microsoft.Data.SqlClient;

Two namespaces exist and this matters:

Namespace	Status
System.Data.SqlClient	Legacy — shipped in .NET Framework, no longer developed
Microsoft.Data.SqlClient	Current — new features, TLS updates, Always Encrypted

Use Microsoft.Data.SqlClient in anything new. You will meet System.Data.SqlClient constantly in existing code; the APIs are nearly identical, so migrating is mostly a namespace change — but test it, because connection-string defaults differ.

The most common surprise: Microsoft.Data.SqlClient version 4 and later default Encrypt=True. A local SQL Server with a self-signed certificate then refuses the connection until you add TrustServerCertificate=True. Code that worked on the old package fails immediately on the new one.

Connection strings

Server=.;Database=NexCodingSchool;Integrated Security=True;TrustServerCertificate=True;
Server=nca-sql,1433;Database=NexCodingSchool;User Id=app_user;Password=...;Encrypt=True;

Keyword	Purpose
Server / Data Source	Instance — ., localhost, .\SQLEXPRESS, host,port
Database / Initial Catalog	Default database
Integrated Security=True	Use the process's Windows account
User Id / Password	SQL authentication
Encrypt	Encrypt the connection — default True on current providers
TrustServerCertificate	Accept a self-signed certificate
Connect Timeout	Seconds to wait for a connection (default 15)
Min Pool Size / Max Pool Size	Pool bounds (default 0 and 100)
Application Name	Appears in SQL Server monitoring — set it

Application Name=NexCoding.SchoolPortal costs nothing and makes sys.dm_exec_sessions immediately useful when several applications share a server.

Where the connection string belongs

// appsettings.json — committed, so no real credentials here
{
  "ConnectionStrings": {
    "SchoolDb": "Server=.;Database=NexCodingSchool;Integrated Security=True;TrustServerCertificate=True;"
  }
}

public class StudentRepository
{
    private readonly string _connectionString;

    public StudentRepository(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("SchoolDb")
            ?? throw new InvalidOperationException("Connection string 'SchoolDb' is not configured.");
    }
}

A production connection string with a password must never be in appsettings.json, because that file is in source control and stays in Git history permanently. Use user secrets in development and environment variables or a key vault in production:

Right-click the project → Manage User Secrets. Visual Studio creates secrets.json outside the solution folder and opens it:

{
  "ConnectionStrings": {
    "SchoolDb": "Server=.;Database=NexCodingSchool;Integrated Security=True;TrustServerCertificate=True;"
  }
}

The file lives in your user profile, not the project, so it cannot be committed by accident. That is the whole point of it.

Building one safely in code:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "nca-sql";
builder.InitialCatalog = "NexCodingSchool";
builder.IntegratedSecurity = true;
builder.ApplicationName = "NexCoding.SchoolPortal";
builder.ConnectTimeout = 15;

string connectionString = builder.ConnectionString;

SqlConnectionStringBuilder escapes values correctly. Concatenating a password containing ; or ' into a string produces a connection string that fails with a confusing parse error.

SqlConnection

using (SqlConnection connection = new SqlConnection(_connectionString))
{
    connection.Open();

    // use the connection

}   // Dispose() runs here — connection returns to the pool

The using statement calls Dispose() on exit, including when an exception is thrown. That is the entire point.

C# 8 and later allow the declaration form, which disposes at the end of the enclosing scope:

using SqlConnection connection = new SqlConnection(_connectionString);
connection.Open();

Both are correct. The block form makes the lifetime visually explicit, which matters when several disposables are nested.

Connection states

if (connection.State != ConnectionState.Open)
{
    connection.Open();
}

Closed, Open, Connecting, Executing, Fetching, Broken. In practice you check for Open and otherwise open it.

Open as late as possible and close as early as possible. A connection open while you loop over results, format output, and call a web service is a connection nobody else can use.

Connection pooling

connection.Open() does not usually open a network connection. The provider keeps a pool of open connections per unique connection string, and Open() takes one from it. Dispose() returns it rather than closing it.

This is why the correct pattern is create, open, use, dispose — every time:

// Correct: relies on pooling
public Student? GetStudent(int schoolId, Guid publicId)
{
    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        connection.Open();
        // ...
    }
}

// Wrong: a long-lived shared connection
public class StudentRepository
{
    private readonly SqlConnection _connection;   // opened once, kept forever
}

A held connection is not thread-safe, blocks pool reuse, and goes stale after a network interruption with no obvious symptom.

Pool exhaustion

The default Max Pool Size is 100. When all are in use, Open() waits, then throws:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

This error almost never means the pool is too small. It means connections are not being returned — a missing using somewhere, a reader left open, or a long-running transaction. Raising Max Pool Size delays the failure and makes it harder to find.

Find the leak by looking for new SqlConnection not inside a using:

grep -rn "new SqlConnection" --include=*.cs . | grep -v "using"

Pools are keyed by the exact connection-string text. Two strings differing only in whitespace or key order create two separate pools, quietly doubling connection use.

Deterministic disposal

Every ADO.NET disposable belongs in a using:

using (SqlConnection connection = new SqlConnection(_connectionString))
using (SqlCommand command = new SqlCommand(sql, connection))
{
    command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;

    connection.Open();

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // map rows
        }
    }
}

SqlConnection, SqlCommand, SqlDataReader, SqlTransaction, and SqlDataAdapter are all IDisposable.

A reader is the one people forget. An open SqlDataReader occupies its connection completely — no other command can run on it until the reader is closed, which produces:

There is already an open DataReader associated with this Command which must be executed first.

Two common causes: iterating a reader and issuing another query inside the loop, or returning IEnumerable<T> from a method that yields while the reader is open. Materialise with ToList() before the connection closes, or restructure so the second query runs after.

Diagnosing a connection failure

Work in this order.

1. Read the exception number, not just the message.

Number	Meaning	Usual cause
2 / 53	Server not found	Wrong instance name, SQL Browser off, firewall
18456	Login failed	Bad credentials, or the service account has no login
4060	Cannot open database	Wrong database name, or no permission to it
40615	IP not allowed	Azure SQL firewall rule missing
-2	Timeout	Slow query, blocking, or an unreachable server

2. Log the connection string the process actually used, with the password masked:

SqlConnectionStringBuilder safe = new SqlConnectionStringBuilder(_connectionString);
safe.Password = "***";

_logger.LogInformation("Connecting with {ConnectionString}", safe.ConnectionString);

Never assume the config file you opened is the one that won. Environment variables, user secrets, and transforms all override it.

3. Confirm the identity. With Integrated Security=True, the account is the process's, not yours. A site running under IIS APPPOOL\SchoolPortal needs a SQL login for that account — which is why an application works in Visual Studio and fails on the server.

SELECT SUSER_SNAME() AS LoginName, DB_NAME() AS CurrentDatabase;

Run that through your own code, not in SSMS, to see what the application is actually connecting as.

4. Test outside the application. SSMS with the same credentials rules out the network and permissions. If SSMS works and the code does not, the difference is the connection string or the identity.

Errors you will hit

Message	Cause	Fix
A network-related or instance-specific error	Wrong server or instance in the connection string	Try . or .\SQLEXPRESS
Login failed for user	Wrong credentials	Use Integrated Security=True locally
Cannot open database "X"	Wrong Initial Catalog	Check the name in SSMS
A connection was successfully established ... but then an error occurred during the login process	TLS mismatch	Add TrustServerCertificate=True for local development
Timeout expired ... obtaining a connection from the pool	Connections never disposed	Wrap every one in using
The type or namespace 'SqlConnection' could not be found	Package not installed, or wrong using	Install Microsoft.Data.SqlClient; check the namespace

System.Data.SqlClient and Microsoft.Data.SqlClient are different packages. The old one still exists; use the Microsoft. one, and make sure your using matches what you installed.

Common mistakes

• A shared, long-lived SqlConnection field
• Missing using, leaking connections until the pool is exhausted
• Raising Max Pool Size to hide a leak
• Leaving a SqlDataReader open while running another command
• Returning a lazy IEnumerable<T> that outlives the connection
• Real credentials in appsettings.json
• Concatenating a password into the connection string instead of using the builder
• Connection-string variants creating multiple pools
• Assuming System.Data.SqlClient and Microsoft.Data.SqlClient behave identically
• Not setting Application Name
• Opening the connection long before it is needed

Practice

1. Create a console application, add Microsoft.Data.SqlClient, and connect to NexCodingSchool. Set Application Name.
2. Run SELECT SUSER_SNAME(), DB_NAME() through your code and confirm the login and database.
3. Move the connection string to user secrets. Confirm it still works with nothing sensitive in appsettings.json.
4. Build a connection string with SqlConnectionStringBuilder, using a password containing ;. Then try the same by concatenation and record the error.
5. Remove TrustServerCertificate=True against a local server and record the exact exception.
6. Break the connection string four ways — wrong server, wrong database, wrong credentials, unreachable port — and record the exception number for each.
7. Open a connection in a loop 200 times without using, and force garbage collection off. Record how many iterations pass before the pool timeout, and the exact message.
8. Fix it with using and confirm 10,000 iterations run cleanly.
9. Open a reader and, inside the while loop, try to execute a second command on the same connection. Record the exception.
10. Add Application Name, run your app, and find your session in sys.dm_exec_sessions.

Exercises 7 and 8 are the ones worth doing carefully — pool exhaustion in production looks like a slow site, not a code bug, and recognising the message saves hours.

You can now

• Explain every step from a C# call to a returned row
• Build a connection string and say what each part does
• Keep credentials out of appsettings.json using User Secrets
• Wrap every connection in using
• Diagnose a connection failure from the error

Review questions

1. What does connection.Open() actually do when pooling is enabled?
2. Why does raising Max Pool Size usually hide rather than fix a problem?
3. Why can an application work in Visual Studio and fail on the server with Integrated Security=True?
4. What causes "There is already an open DataReader associated with this Command"?

Next: Commands and execution