136 - Database Connections · dotnetmadeeasy.com

Opening a new connection for every query feels wasteful — but the pool underneath makes it not just fine, but the recommended way.

Here's a worry almost every developer has the first time they see ADO.NET code: "Wait, this method creates a brand-new SqlConnection every single time it runs. Isn't opening a network connection to a database server slow? Shouldn't I keep one connection open and reuse it everywhere?"

It's a completely reasonable instinct — and it's the wrong instinct. In .NET, creating a new connection object per call, opening it, using it briefly, and disposing it is not just acceptable, it's the recommended pattern. The reason is a feature working quietly behind the scenes: connection pooling.

In this lesson, you'll learn what a database connection actually represents, how connection strings describe where and how to connect, and why connection pooling makes "open, use, dispose, repeat" both safe and fast.

What Is It?

The Simple Explanation

A database connection is an object in your code — SqlConnection for SQL Server — that represents an active line of communication with a specific database. You tell it where the database lives and how to authenticate using a connection string, then you Open() it before you can run any commands over it.

The Technical Definition

SqlConnection derives from the provider-agnostic DbConnection base class you met in the previous lesson. It manages the underlying network session (typically TCP) to the database server, tracks the connection's state (Closed, Open, Connecting, Broken), and is the object every DbCommand executes through.

Connection String Part	Example	Meaning
Server	Server=localhost	The database server's hostname or address
Database	Database=ShopDb	Which database on that server to use
Trusted_Connection	Trusted_Connection=True	Use the current Windows identity instead of a username/password (integrated security)
User Id / Password	User Id=app_user;Password=...	SQL Server login credentials, used instead of integrated security
TrustServerCertificate	TrustServerCertificate=True	Common for local dev to skip TLS certificate validation — never in production
Encrypt	Encrypt=True	Whether traffic to the server is encrypted (defaults to true in modern drivers)

Why Does It Exist?

The Problem — Opening a Real Network Connection Is Expensive

Establishing a fresh TCP connection to a database server, then authenticating and negotiating a session, is a genuinely slow operation — it can take tens of milliseconds. If your application handled every web request by opening a brand-new physical connection, authenticating, running one query, then tearing the connection down — and did that thousands of times a minute — the overhead would dominate your response times.

But the alternative — one single, long-lived, shared connection reused by the entire application — is also broken. Database connections aren't safe to use from multiple threads simultaneously, so a shared connection becomes a bottleneck and a source of bizarre concurrency bugs the moment two requests try to query at the same time.

The Solution — Connection Pooling

ADO.NET providers solve this with a connection pool that sits invisibly beneath the SqlConnection object. When you call Open(), the provider doesn't necessarily open a brand-new physical connection — it first checks whether an already-open physical connection, matching your exact connection string, is sitting idle in the pool. If so, it hands you that one instantly. When you call Close() or Dispose(), the physical connection isn't actually torn down — it's returned to the pool, ready for the next caller.

What You Might Assume Happens

new SqlConnection() → brand-new TCP handshake, every time
Open() is always slow
Dispose() always tears the connection down completely
So: reuse one long-lived connection to "save time"

What Actually Happens

new SqlConnection() creates a lightweight wrapper object only
Open() usually grabs an already-open physical connection from the pool
Dispose() returns the physical connection to the pool for reuse
So: create/open/dispose per unit of work — it's cheap and it's correct

Big Picture

How It Works

Always use using

SqlConnection implements IDisposable (technically, IAsyncDisposable as well). Wrapping it in a using statement guarantees it's returned to the pool even if an exception is thrown while it's open — exactly the guarantee finally gives you, which you saw in the exception-handling lesson.

Simple Example

using Microsoft.Data.SqlClient;

public async Task<int> GetProductCountAsync(string connectionString)
{
    // The recommended pattern: a fresh connection object per unit of work
    await using SqlConnection connection = new(connectionString);
    await connection.OpenAsync();

    await using SqlCommand command = connection.CreateCommand();
    command.CommandText = "SELECT COUNT(*) FROM Products";

    return (int)(await command.ExecuteScalarAsync())!;
} // connection.DisposeAsync() runs automatically here — returned to the pool, not torn down

Calling GetProductCountAsync a thousand times in a row does not open a thousand physical TCP connections. After the first few calls warm up the pool, nearly every subsequent call reuses an already-open physical connection — the Open()/Dispose() pair is cheap.

Tuning the pool (rarely needed, good to know it exists)

Real-World Example

Picture an ASP.NET Core Web API handling checkout requests for an e-commerce site — potentially hundreds per second at peak traffic. Each request independently needs to look up a product, check stock, and record an order. Every one of those requests calls a method just like GetProductCountAsync above — opening and disposing its own connection.

Under load, the connection pool naturally grows to whatever size the traffic demands (up to Max Pool Size), and physical connections get handed between concurrent requests as they finish with them. This is exactly the model EF Core relies on too: it opens a connection from the pool at the start of a unit of work, and returns it when the DbContext is disposed — which is one of the reasons a DbContext is designed to be short-lived, as you'll see in the DbContext lesson.

Analogy

A Taxi Rank, Not a Personal Car

Owning a personal car (one permanent, shared connection) means dealing with parking, maintenance, and only one person can drive it at a time. Calling a taxi every single trip and giving it back afterward (open/dispose per call) sounds wasteful — until you realize there's a whole rank of taxis waiting nearby (the connection pool).

You don't own the taxi. You just "check one out" for your trip, and the moment you're done, it goes right back to the rank for the next passenger — engine still running, ready instantly. That's exactly what Open() and Dispose() are doing with pooled connections: borrowing and returning, not building and destroying.

Under the Hood

Common Confusion

1. "Dispose() closes the physical network connection"

It doesn't, in the normal case. Dispose() returns the physical connection to the pool. The actual TCP connection to the database usually stays alive behind the scenes, ready for the next Open() call — you're only ever giving up your use of it, not destroying it.

2. "Keeping one connection open for the app's whole lifetime is more efficient"

It's actually worse: a single shared connection serializes all database access through one physical channel, defeats concurrency, and isn't thread-safe to use from multiple requests simultaneously. Pooling already gives you the performance benefit you were trying to achieve manually — with none of the downsides.

Common Mistakes

Mistake 1 — Forgetting to dispose the connection

Left long enough, this exhausts the pool — new callers eventually get a Timeout expired exception waiting for a free connection. Always use using (or await using) so disposal happens automatically, even on an exception.

Mistake 2 — Storing one shared connection instance as a singleton field

Declaring private static SqlConnection _connection = ... and reusing it across every request in a web application. This breaks under concurrency and defeats the entire point of pooling. Create, open, use, and dispose a connection per unit of work — let the pool handle reuse.

Mistake 3 — Opening the connection long before you actually need it

Opening a connection at the start of a long method that does other, unrelated work first, then finally runs a query near the end — holding a pooled connection open the whole time. Open as late as reasonably possible, and dispose as soon as you're done, so the connection returns to the pool quickly for others to use.

When Should I Use It?

Rule of thumb: Create a new connection object, open it, do your work, and dispose it — every time, for every unit of work — whether that's a raw ADO.NET call or, as you'll see soon, a single DbContext lifetime in EF Core. Don't try to be "efficient" by holding one connection open longer than necessary; the pool is already doing that job for you, far better than manual management could.

Mental Model

SqlConnection object = a claim ticket, not the taxi itself.
Open() = hand in the ticket, get a ready-to-go taxi from the rank.
Dispose() = return the taxi to the rank — it doesn't get scrapped.

Remember:
· Create/open/dispose per unit of work — that's the correct, recommended pattern.
· The connection pool is keyed by the exact connection string.
· Never share one connection instance across concurrent operations.

Key Takeaway

A connection string describes where the database lives and how to authenticate — keep credentials out of source code.
Connection pooling means new SqlConnection() plus Open()/Dispose() per call is cheap — the physical connection is reused, not rebuilt every time.
Always dispose connections — ideally with using — so they return to the pool promptly, even on exceptions.
Never share a single connection instance across concurrent operations — it isn't thread-safe and defeats pooling entirely.
This exact pattern — short-lived connections backed by a pool — is also how EF Core's DbContext manages its own connection.

Check Your Understanding

You've seen why "open and dispose per call" is the right pattern, not a wasteful one. Let's confirm it clicked.

1. A developer is worried that calling a data-access method 500 times in a loop will open 500 separate physical connections to the database, so they refactor to share one connection across all 500 calls. What's the problem with this "fix"?

A) There's no problem — this is the correct and recommended approach
B) It defeats connection pooling's built-in reuse, and a single shared connection isn't safe to use across concurrent operations
C) SqlConnection objects cannot be reused more than once, ever
D) Connection strings expire after a single use

Show answer

Correct: B

Why B is correct: Connection pooling already reuses the underlying physical connection transparently across separate SqlConnection instances. Forcing one shared instance instead removes that safety net and introduces thread-safety problems if that connection is ever used concurrently.

Why A is incorrect: This is the opposite of the recommended pattern — create/open/dispose per unit of work is correct precisely because pooling makes it cheap.

Why C is incorrect: A given SqlConnection object instance can be opened and closed, though the recommended pattern is a new instance per unit of work, backed by the pool.

Why D is incorrect: Connection strings don't "expire" after use — that's not a real ADO.NET concept.

Reinforcement: Trust the pool — open/dispose per call is efficient, not wasteful.

2. When you call connection.Dispose() on a pooled connection, what typically happens to the underlying physical network connection?

A) It is immediately and permanently closed
B) It is returned to the connection pool, ready to be handed to the next Open() call
C) It is upgraded to a higher-privilege connection
D) Nothing — Dispose() has no effect on SqlConnection

Show answer

Correct: B

Why B is correct: In the normal pooled case, disposing a connection returns the physical connection to the pool rather than tearing it down — that's the entire mechanism that makes frequent open/dispose calls cheap.

Why A is incorrect: This would defeat the purpose of pooling — the physical connection generally stays alive, just idle in the pool.

Why C is incorrect: Disposal has nothing to do with privilege levels — that's not a concept connection pooling deals with.

Why D is incorrect: Dispose() has a real, important effect — it's what makes the connection object's resources available again, including returning it to the pool.

Reinforcement: Disposal returns connections to the pool — it usually does not close them at the network level.

3. Two parts of your application connect to the same database, but one uses Trusted_Connection=True and the other uses a SQL login with User Id/Password. Will they share the same connection pool?

A) Yes, because they point at the same database
B) No — connection pools are keyed by the exact connection string, and these two strings differ
C) Yes, but only during business hours
D) It depends on which .NET version is installed

Show answer

Correct: B

Why B is correct: Pools are keyed on the full connection string text, not just the target server/database. Two connection strings that differ in authentication method (or any other part) get separate pools entirely.

Why A is incorrect: Targeting the same database isn't enough — the pooling key is the connection string itself, which differs here.

Why C is incorrect: There's no time-of-day behavior in connection pooling.

Why D is incorrect: This behavior is consistent across modern .NET versions — it's a property of how the provider keys its pools, not a version-specific quirk.

Reinforcement: Keep connection strings consistent across calls that should share a pool — even small differences create separate pools.

4. Which of the following is the best practice for handling a SqlConnection in a method that runs one query?

A) Store it as a static field so it's created only once for the whole application
B) Wrap it in a using statement local to the method, so it's opened, used, and disposed within that method's scope
C) Never call Open() explicitly — ADO.NET opens it automatically forever
D) Open it at application startup and never dispose it

Show answer

Correct: B

Why B is correct: A locally-scoped, disposed-per-call connection is the recommended pattern — it's cheap thanks to pooling, and a using statement guarantees disposal even if an exception occurs mid-method.

Why A is incorrect: A shared static connection isn't thread-safe for concurrent use and defeats the pool's own reuse mechanism.

Why C is incorrect: You must explicitly call Open() (or OpenAsync()) before running commands — it isn't automatic.

Why D is incorrect: Holding a connection open indefinitely ties up a pooled resource unnecessarily and isn't the recommended lifecycle.

Reinforcement: Scope connections tightly around the unit of work that needs them, and let disposal (via using) happen automatically.

You now understand connections, connection strings, and why pooling makes frequent open/dispose calls the right pattern. Next up: sending actual SQL statements with commands.