skip to content

ADO.NET & DbDataSource

Without EF Core, a web app opens ADO.NET connections itself: one DbDataSource registered at startup, a pooled connection per query, opened late and disposed early. Interviewers probe pool exhaustion.

part ofASP.NET Coreread it →
on this pageshow

explore

questions

5

In an ASP.NET Core 10 API using Npgsql without EF Core, how do you register a data source and run a query from a handler?

level: juniorimportance: must knowfreq 60%

answer

  1. package Npgsql.DependencyInjection
  2. AddNpgsqlDataSource(connectionString)
  3. data source owns the pool, Singleton
  4. OpenConnectionAsync returns ValueTask<NpgsqlConnection>
  5. await using on connection, command, reader

basics

~10 s

Add Npgsql.DependencyInjection, call builder.Services.AddNpgsqlDataSource(connectionString), inject NpgsqlDataSource into the handler, and open a pooled connection per query with await using var conn = await dataSource.OpenConnectionAsync(); disposing it hands the connection back to the pool.

solid answer

~40 s

I add the `Npgsql.DependencyInjection` package and call `builder.Services.AddNpgsqlDataSource(connectionString)` in `Program.cs`, with the string read from configuration. That registers one `NpgsqlDataSource` as a Singleton (also exposed as the abstract `DbDataSource`), and it owns the connection pool. A handler injects `NpgsqlDataSource`, opens a connection as late as possible with `await using var conn = await dataSource.OpenConnectionAsync()`, runs a parameterized `NpgsqlCommand`, reads the result with `await using` on the reader, and lets the `await using` blocks hand the physical connection back to the pool. For a single statement, `dataSource.CreateCommand(sql)` is shorter still: it borrows a connection when it executes and returns it when its reader closes. I never build a data source per request and never keep an open connection in a field.

code

xml · 3 lines
xml
<ItemGroup>
  <PackageReference Include="Npgsql.DependencyInjection" Version="10.0.3" />
</ItemGroup>

go deeper

for a junior

Write the AddNpgsqlDataSource line, inject NpgsqlDataSource into a handler and open a connection with await using; say that dispose returns it to the pool.

for a middle

List what AddNpgsqlDataSource registers and with which lifetimes, and explain why the data source must be one per database while connections are short-lived.

for a senior

Show how open-late, dispose-early discipline protects a 100-connection pool under load, and when a data-source command beats an explicit connection.

for a principal

Set the team's data-access convention: one registered data source per database, no hand-built pools, and where raw ADO.NET is allowed next to an ORM.

## The registration Without EF Core, an ASP.NET Core app talks to PostgreSQL through the **Npgsql** ADO.NET driver. The `Npgsql.DependencyInjection` package adds one extension method that does the plumbing: ```csharp var builder = WebApplication.CreateBuilder(args); builder.Services.AddNpgsqlDataSource( builder.Configuration.GetConnectionString("Meters") ?? throw new InvalidOperationException("Connection string 'Meters' not found.")); var app = builder.Build(); ``` A **data source** (`NpgsqlDataSource`, which derives from the BCL's abstract `DbDataSource`) is the object that knows the connection string, owns the **connection pool** and hands out connections. You want exactly one per database for the life of the app. ## What AddNpgsqlDataSource puts in the container | Service | Default lifetime | How it is built | |---|---|---| | `NpgsqlDataSource` | Singleton (`dataSourceLifetime`) | `NpgsqlDataSourceBuilder` with the app's `ILoggerFactory` | | `DbDataSource` | Singleton | forwards to the same `NpgsqlDataSource` | | `NpgsqlConnection` | Transient (`connectionLifetime`) | `dataSource.CreateConnection()`, not yet open | | `DbConnection` | Transient | forwards to the `NpgsqlConnection` registration | An optional `Action<NpgsqlDataSourceBuilder>` overload configures the driver beyond the connection string, and a `serviceKey` argument registers a second database as a keyed service. ## Why a data source rather than a bare connection string Older code passed a connection string around and called `new NpgsqlConnection(cs)` wherever it needed one. That still works, because the driver keeps an internal pool per connection string, but a registered data source is the better shape for a web app: - **one owner** for the pool and every driver setting, configured once at startup instead of in each class; - **logging wired in**: the registration hands the app's `ILoggerFactory` to the `NpgsqlDataSourceBuilder`, so driver logs flow into ASP.NET Core logging without extra code; - **an abstraction for tests and other providers**: code that depends on `DbDataSource` does not care which driver sits behind it; - **several databases side by side**: a second call with a `serviceKey` registers a keyed data source, injected with `[FromKeyedServices]`. ## Running a query in an energy-meter API ```csharp app.MapGet("/meters/{meterId}/latest", async (string meterId, NpgsqlDataSource dataSource) => { await using var conn = await dataSource.OpenConnectionAsync(); await using var cmd = new NpgsqlCommand( "SELECT read_at, kwh FROM meter_readings WHERE meter_id = $1 ORDER BY read_at DESC LIMIT 1", conn); cmd.Parameters.AddWithValue(meterId); await using var reader = await cmd.ExecuteReaderAsync(); return await reader.ReadAsync() ? Results.Ok(new { ReadAt = reader.GetDateTime(0), Kwh = reader.GetDecimal(1) }) : Results.NotFound(); }); ``` `OpenConnectionAsync()` returns a `ValueTask<NpgsqlConnection>` that is already open. The value is bound as a positional parameter (`$1`), never concatenated into the SQL text. ## Open late, dispose early The pool has a fixed number of physical connections (`Max Pool Size`, 100 by default), shared by every concurrent request. So each handler: 1. opens the connection immediately before the first statement, not at the top of a long method; 2. disposes the reader, the command and the connection with `await using`, so an exception path returns the connection too; 3. does not hold the connection across unrelated slow work, such as an outgoing HTTP call. Disposing a pooled `NpgsqlConnection` does **not** close the socket to PostgreSQL; it puts the physical connection back in the pool, and the driver resets the session state before the next borrower's first command. ## The one-statement shortcut `dataSource.CreateCommand(sql)` returns a command bound to the data source rather than to a connection. When it executes, it takes a connection from the pool and adds `CommandBehavior.CloseConnection`, so the connection goes back when the reader is disposed. Such a command cannot be prepared and does not expose its `Connection` or `Transaction`; touching them throws `NotSupportedException`. Use it for single statements, and an explicit connection when several statements or a transaction must share one. ## Mistakes interviewers listen for - building a data source with `NpgsqlDataSource.Create(cs)` inside a handler, which creates and abandons a pool per request; - storing one open `NpgsqlConnection` in a static field or a singleton, which concurrent requests then share; - forgetting `await using` on the reader or connection: `NpgsqlConnection` suppresses finalization, so the garbage collector never hands a forgotten connection back to the pool; - registering the data source with a shorter lifetime than Singleton, which multiplies pools.

  • AddNpgsqlDataSource also registers NpgsqlConnection. When would a handler inject that instead of the data source?
    The registered `NpgsqlConnection` is Transient and created closed by `dataSource.CreateConnection()`; you call `OpenAsync()` yourself, and the request scope disposes it at the end of the request. It suits code that wants exactly one connection object, such as a repository taking a `DbConnection`. Injecting the data source is better when you want to control exactly when a connection is held, or need two at once.
  • Why can't a command from dataSource.CreateCommand(sql) take part in a transaction?
    It owns its connection: it borrows one from the pool when it executes and returns it when its reader closes, so you never see the connection a transaction would be started on. Its `Connection` and `Transaction` properties throw `NotSupportedException`. For a transaction, open a connection with `OpenConnectionAsync()`, call `BeginTransactionAsync()` on it, and create the commands from that connection.

  • Call NpgsqlDataSource.Create(cs) inside each handler to get a fresh connection.
  • Keep one open NpgsqlConnection in a singleton and reuse it for every request.
  • Disposing a pooled NpgsqlConnection closes the socket to PostgreSQL each time.
  • AddNpgsqlDataSource registers the data source as Scoped, one per request.
  • Skip await using on the reader; the garbage collector returns the connection soon enough.
open as a page

With Microsoft.Data.SqlClient in an ASP.NET Core app, what do new SqlConnection(cs), OpenAsync and DisposeAsync actually do when pooling is on?

level: middleimportance: must knowfreq 58%

basics

~20 s

Constructing a SqlConnection allocates only an object. OpenAsync borrows an idle physical connection from the pool for that exact connection string, creating one only if none is free, and DisposeAsync returns it to the pool rather than closing it.

open as a page

In ASP.NET Core 10, how should Dapper code obtain its IDbConnection: an injected DbDataSource, a registered connection, or new SqlConnection per call?

level: middleimportance: should knowfreq 45%

basics

~20 s

Inject a singleton DbDataSource and open a DbConnection per query with await using; Dapper's extension methods take it as an IDbConnection. Never share one connection through a singleton, and note AddNpgsqlDataSource registers DbConnection, not IDbConnection.

open as a page

In an ASP.NET Core 10 energy-meter report endpoint mixing EF Core 10 writes with hand-written SQL reads on SQL Server, why can a TransactionScope escalate, and how do you avoid it?

level: seniorimportance: should knowfreq 30%

basics

~20 s

EF Core 10 injects an Application Name into SQL Server connection strings lacking one, so EF and raw SQL use two strings, pools and connections, and a TransactionScope over both can escalate. Set Application Name, or share EF's connection and transaction.

open as a page

An ASP.NET Core 10 energy-meter API on Npgsql starts throwing 'The connection pool has been exhausted' under load; how do you find the cause and fix it?

level: seniorimportance: should knowfreq 50%

basics

~20 s

All pooled connections stayed busy longer than the open Timeout (15 s by default) while more requests queued. Measure used versus idle connections, fix code that holds connections too long or never disposes them, and only then size the pool.

open as a page