Chapter 18: Data Access Essentials

Most real applications persist and retrieve data. In C# you work with data through .NET’s data access APIs; at the lowest level this is ADO.NET, which provides consistent abstractions across providers. This chapter focuses on the essential building blocks you can use in any .NET application, with attention to safety, performance, and clarity.

Connections, commands, and readers

ADO.NET centers on three workhorses. A DbConnection manages a connection to a data source, a DbCommand represents a statement to execute, and a DbDataReader streams rows forward only. Concrete providers such as SqlConnection, NpgsqlConnection, or SqliteConnection implement these abstractions with provider-specific behavior. Connection pooling is handled by .NET, so creating and disposing connections per operation is the normal pattern.

Choosing a provider

Select a provider that matches your database engine. For SQL Server use Microsoft.Data.SqlClient. For PostgreSQL use Npgsql. For SQLite use Microsoft.Data.Sqlite. Each follows the same core patterns, so switching is mainly about connection strings and SQL dialect details.

Connecting and executing a scalar

The following example opens a connection, runs a scalar query, and disposes resources deterministically. The pattern is the same for all providers; only types and connection strings differ.

using Microsoft.Data.SqlClient;

const string cs = "Server=localhost;Database=AppDb;User Id=app;Password=…;TrustServerCertificate=true";
using var conn = new SqlConnection(cs);
await conn.OpenAsync();

using var cmd = new SqlCommand("SELECT COUNT(*) FROM dbo.Users", conn);
var count = (int)await cmd.ExecuteScalarAsync();

Console.WriteLine($"Users: {count}");

Streaming rows with a DbDataReader

A reader streams rows as they arrive and keeps memory use low. Read columns by ordinal for best performance, or by name for clarity.

using var cmd = new SqlCommand("SELECT Id, Email FROM dbo.Users ORDER BY Id", conn);
using var rdr = await cmd.ExecuteReaderAsync();

while (await rdr.ReadAsync())
{
  var id = rdr.GetInt32(0);
  var email = rdr.GetString(1);
  Console.WriteLine($"{id}: {email}");
}

Parameterization and safety

Never concatenate user input into SQL. Use parameters to avoid injection and to let the provider cache efficient execution plans. Parameters also handle type conversion and nulls correctly.

Using parameters

This example inserts a row using parameters. The provider infers types from values, or you can set types explicitly for full control.

const string sql = "INSERT INTO dbo.Users (Email, DisplayName) VALUES (@email, @name)";
using var cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@email", emailInput);
cmd.Parameters.AddWithValue("@name", displayNameInput);
var rows = await cmd.ExecuteNonQueryAsync();

Explicit types and null handling

When precision matters, declare parameter types and sizes. Use DBNull.Value for database nulls.

var p = cmd.Parameters.Add("@bio", System.Data.SqlDbType.NVarChar, 4000);
p.Value = string.IsNullOrWhiteSpace(bio) ? DBNull.Value : bio;

Transactions and isolation

Transactions group operations so they succeed or fail together. Start a transaction on an open connection, enlist commands in it, and commit or roll back. Isolation levels control visibility of concurrent changes and the types of anomalies that can happen.

Transactional pattern

Use a DbTransaction and ensure it is committed or rolled back even if an exception occurs.

await using var tx = await conn.BeginTransactionAsync(System.Data.IsolationLevel.ReadCommitted);

try
{
  using var debit = new SqlCommand("UPDATE Accounts SET Balance = Balance - @amt WHERE Id = @id", conn, (SqlTransaction)tx);
  debit.Parameters.AddWithValue("@amt", amount);
  debit.Parameters.AddWithValue("@id", fromId);
  await debit.ExecuteNonQueryAsync();

  using var credit = new SqlCommand("UPDATE Accounts SET Balance = Balance + @amt WHERE Id = @id", conn, (SqlTransaction)tx);
  credit.Parameters.AddWithValue("@amt", amount);
  credit.Parameters.AddWithValue("@id", toId);
  await credit.ExecuteNonQueryAsync();

  await tx.CommitAsync();
}
catch
{
  await tx.RollbackAsync();
  throw;
}

Common isolation levels

The table shows typical semantics. Higher isolation reduces anomalies; it can also reduce concurrency.

Basic object mapping

Many applications map rows to objects manually for clarity and control. This keeps dependencies small and performance predictable. You can write small helper methods that translate a reader row into a record or class.

Manual mapping to a record

Read fields by ordinal and construct a record. This keeps mapping code explicit and easy to test.

public readonly record struct User(int Id, string Email, string? DisplayName);

static User MapUser(System.Data.Common.DbDataReader r)
{
  var id = r.GetInt32(0);
  var email = r.GetString(1);
  var name = r.IsDBNull(2) ? null : r.GetString(2);
  return new User(id, email, name);
}

Query method returning a list

Encapsulate data access behind methods that take a connection and optional transaction. This keeps call sites simple and testable.

public static async Task<List<User>> GetUsersAsync(SqlConnection conn, SqlTransaction? tx = null)
{
  const string sql = "SELECT Id, Email, DisplayName FROM dbo.Users ORDER BY Id";
  using var cmd = new SqlCommand(sql, conn, tx);
  using var rdr = await cmd.ExecuteReaderAsync();
  var list = new List<User>();
  while (await rdr.ReadAsync())
    list.Add(MapUser(rdr));
  return list;
}

Async database patterns

Databases are I/O bound; async methods free threads while the server works. Use OpenAsync, ExecuteReaderAsync, ExecuteScalarAsync, and ExecuteNonQueryAsync consistently. Flow a CancellationToken so operations can be abandoned when callers no longer need results.

Async with cancellation

Pass a token through to every async call that accepts one. Ensure you handle OperationCanceledException without treating it as an error in logs.

using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(2));
await conn.OpenAsync(cts.Token);

using var cmd = new SqlCommand("WAITFOR DELAY '00:00:05'; SELECT 42;", conn);
try
{
  var value = (int)await cmd.ExecuteScalarAsync(cts.Token);
  Console.WriteLine(value);
}
catch (OperationCanceledException)
{
  Console.WriteLine("Query cancelled");
}

Avoiding sync-over-async

Do not call .Result or .Wait() on tasks from data access; doing so can cause thread pool starvation. Keep the entire call chain async.

Resilience and retries

Transient faults happen. Build retry policies for well-known transient errors and add jitter to backoffs to reduce thundering herds. Also consider idempotency; only retry operations that are safe to repeat or that can detect duplicates.

Lightweight retry with a loop

You can implement simple retries without extra libraries. Limit attempts and back off between tries.

static async Task<T> WithRetryAsync<T>(Func<Task<T>> action, int attempts = 3)
{
  var rnd = new Random();
  for (var i = 1; i <= attempts; i++)
  {
    try { return await action(); }
    catch (SqlException ex) when (IsTransient(ex) && i < attempts)
    {
      await Task.Delay(TimeSpan.FromMilliseconds(100 * i + rnd.Next(0, 200)));
    }
  }
  return await action();
}

static bool IsTransient(SqlException ex)
{
  // Check error numbers known to be transient; e.g., 4060, 40197, …
  return ex.Errors.Cast<SqlError>().Any(e => e.Number is 4060 or 40197);
}

Connection resiliency and timeouts

Use command and connection timeouts that reflect normal latencies. For SQL Server, connection resiliency options can reconnect automatically in some scenarios; still design for idempotency where practical.