Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 52 ยท Data Access

Data Access without an ORM

Learn ADO.NET basics, parameterized queries, the repository pattern, an in-memory ConcurrentDictionary store, Dapper-style row mapping and async data access.

Almost every web application stores something: users, orders, posts. ASP.NET Core does not decide for you how to talk to a database. You can use a full object-relational mapper such as Entity Framework Core (the next two lessons), a micro-ORM such as Dapper, or plain ADO.NET, the low-level data access API that has been part of .NET since the beginning and sits underneath most of the others.

Learning the no-ORM way first pays off. You will understand what an ORM actually saves you, you will know how to write safe parameterized SQL, and you will see the repository pattern, which keeps endpoints independent of the storage technology. The live runner has no database server or NuGet packages, so the real database code in this lesson is read-only. The runnable examples use an in-memory store and a small row mapper built on DataTable, which is part of .NET itself.

ADO.NET in one picture

ADO.NET is a set of abstract classes in System.Data.Common; each database ships a provider that implements them. The shapes are the same for SQL Server, PostgreSQL and SQLite.

flowchart LR
    A["Your code"] --> B["DbConnection<br/>(open the database)"]
    B --> C["DbCommand<br/>(SQL + parameters)"]
    C --> D["DbDataReader<br/>(forward-only rows)"]
    D --> A

You open a connection, run a command and stream rows back through a reader.

Here is a typical query with the SQLite provider (Microsoft.Data.Sqlite, a NuGet package, so this is read-only):

C#
await using var connection = new SqliteConnection(connectionString);
await connection.OpenAsync(cancellationToken);

await using var command = connection.CreateCommand();
command.CommandText = "SELECT id, name, price FROM products WHERE price <= @max ORDER BY name";
command.Parameters.AddWithValue("@max", maxPrice);

await using var reader = await command.ExecuteReaderAsync(cancellationToken);

var products = new List<Product>();
while (await reader.ReadAsync(cancellationToken))
{
    products.Add(new Product(
        reader.GetInt32(0),
        reader.GetString(1),
        reader.GetDecimal(2)));
}

Three habits in that snippet matter more than any other:

  1. Always use parameters. @max is sent separately from the SQL text, so a value like 5; DROP TABLE products is treated as data, never as code. Building SQL by string concatenation or interpolation is the classic SQL injection bug (Web Security Essentials comes back to it).
  2. Dispose connections. await using returns the connection to the provider's pool. A leaked connection starves the pool and the app starts to hang under load.
  3. Pass the cancellation token. If the client disconnects, the query can stop early instead of burning database time.

For commands that change data you use ExecuteNonQueryAsync (returns the number of rows changed), and for a single value ExecuteScalarAsync. Related transaction and connection-pool details are covered in the ADO.NET lesson of the C# tutorial.

The repository pattern

If every endpoint contains SQL, three things hurt: you cannot test endpoints without a database, a schema change means editing many handlers, and switching technology touches everything. A repository is an interface that describes what your app needs from storage, in the vocabulary of your domain, and a class that implements how:

C#
public record Product(int Id, string Name, decimal Price);

public interface IProductRepository
{
    Task<Product?> GetAsync(int id, CancellationToken ct = default);
    Task<IReadOnlyList<Product>> ListAsync(CancellationToken ct = default);
    Task<Product> AddAsync(Product product, CancellationToken ct = default);
    Task<bool> DeleteAsync(int id, CancellationToken ct = default);
}

Endpoints depend on IProductRepository and receive it through dependency injection. In production it is backed by SQL; in tests and demos it is backed by memory. The Repository and Unit of Work lesson discusses when this abstraction is worth its cost; for a small app, using a DbContext directly can be simpler.

Methods return Task and take a CancellationToken even for the in-memory version, because the interface should not care whether the implementation is fast. Note also that GetAsync returns Product?: absence is a normal outcome, not an exception.

An in-memory implementation

Dictionary<TKey, TValue> is not safe for concurrent use, and a web server handles many requests at once. ConcurrentDictionary is. We also need unique ids even when requests race, which is what Interlocked.Increment guarantees. (The concurrent collections lesson explains these types in detail.)

C# A thread-safe in-memory repository

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

TryUpdate(key, newValue, comparisonValue) is the in-memory cousin of an SQL UPDATE ... WHERE id = @id AND version = @version. Because Product is a record, equality compares all properties, so writer B's stale copy no longer matches and its update is rejected. Databases express the same idea with a version column; EF Core in Practice shows concurrency tokens.

Mapping rows to objects

Reading columns by index (reader.GetInt32(0)) is tedious and breaks when someone reorders the SELECT. A micro-ORM such as Dapper adds one idea on top of ADO.NET: you write the SQL, it maps the rows to objects by column name:

C#
// Dapper (NuGet package, read-only here)
var products = await connection.QueryAsync<Product>(
    "SELECT id, name, price FROM products WHERE price <= @max",
    new { max = maxPrice });

There is no change tracking, no query translation, no hidden SQL: just your SQL and a mapper. That is why micro-ORMs are fast and predictable, and why people pair them with hand-tuned queries.

You can see how the mapping works by building a miniature version. ADO.NET's DataTable is part of .NET itself, and its CreateDataReader() gives a real DbDataReader without any database. The mapper below finds the constructor of a record, matches parameter names to column names (ignoring case and underscores), converts DBNull to null, and builds one object per row.

C# A Dapper-style row mapper

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Real micro-ORMs cache a compiled mapper per query shape instead of using reflection per row, handle many more types, and support parameter objects, but the principle is the one you just read: column name in, constructor parameter out.

Putting it behind an endpoint

The payoff of the repository is that endpoints only know the interface. Register one implementation as a singleton (an in-memory store must be shared by all requests; a SQL repository would normally be scoped), inject it into handlers, and pass the request's cancellation token along.

C# Endpoints that depend on a repository interface

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Swap InMemoryProductRepository for a SqlProductRepository in the one AddSingleton/AddScoped line and none of the endpoints change. That is the whole point of the pattern.

Async all the way

Database calls spend almost all their time waiting for the network. With async/await, the thread that started the call goes back to the pool and serves other requests while the database works; blocking calls such as .Result or .Wait() hold a thread hostage and cause thread-pool starvation under load. So every data-access method should be async, carry a CancellationToken, and be awaited by its caller up to the endpoint. The async/await and cancellation lessons cover the language side.

When to pick which tool

  • Plain ADO.NET gives maximum control and no dependencies, at the price of repetitive code.
  • Dapper or similar removes the mapping boilerplate and keeps your SQL visible. A great fit for reporting queries and performance-critical paths.
  • EF Core adds change tracking, LINQ-to-SQL translation and migrations, so you write less code but must understand what it does for you: the next lesson, Entity Framework Core Overview.

Mixing is normal: many apps use EF Core for writes and a micro-ORM or raw SQL for a few heavy reads.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should values always be passed as command parameters instead of being concatenated into the SQL text?

    A parameter value is transmitted separately from the SQL text, so the database treats it as data. Concatenated input can add its own SQL and is the root of SQL injection.
  2. 2. Why does the in-memory repository use ConcurrentDictionary and Interlocked.Increment?

    Many requests can read and write the store at the same time. A plain Dictionary can be corrupted by that, and a plain ++ on a shared counter can hand out the same id twice.
  3. 3. What does a Dapper-style micro-ORM do that plain ADO.NET does not?

    A micro-ORM keeps your SQL and only automates the row-to-object mapping. Change tracking, query translation and migrations belong to a full ORM like EF Core.
  4. 4. What is the main benefit of endpoints depending on IProductRepository rather than on SQL code?

    The interface hides how data is stored. You can swap implementations in the DI registration, and tests can use an in-memory version with no database.

Summary

  • ADO.NET (DbConnection, DbCommand, DbDataReader) is the low-level layer under most .NET data access; providers are NuGet packages.
  • Always use parameters, dispose connections and pass CancellationToken.
  • A repository interface keeps endpoints independent of storage and makes testing easy; register it with the right lifetime.
  • ConcurrentDictionary, Interlocked and TryUpdate give a thread-safe in-memory store with optimistic updates.
  • Micro-ORMs such as Dapper map rows to objects by column name while you keep control of the SQL.
  • Data access should be async all the way, never .Result or .Wait().

Next: Entity Framework Core Overview