Skip to content
Tutorials Center
My learning

Topics

Lesson 148 of 182 ยท Data Access

Data Access in .NET

A map of .NET data access: ADO.NET, micro-ORMs like Dapper and full ORMs like EF Core, how to choose, connection strings, and a runnable DataTable and mapper demo.

Almost every real application stores data somewhere: orders in a relational database, sessions in Redis, documents in a cloud store. The program in memory is temporary, the data has to outlive it. This chapter is about how C# code talks to databases.

.NET gives you not one way but a stack of layers, from raw and fast to convenient and automatic. Knowing the layers, what each one costs and what it saves you, is the difference between choosing a tool and following a trend. This lesson draws the map and runs a miniature version of each idea in your browser. The next lessons go deep: ADO.NET, Entity Framework Core, repositories and real SQL and NoSQL work.

Our in-browser runner has the base class library and ASP.NET Core, but no database providers and no NuGet packages (no SQL Server or SQLite driver, no Dapper, no EF Core). Code that needs those is shown read-only and labelled. The runnable examples use DataTable, DataSet and LINQ, which are real parts of .NET and show the same shapes of work.

The layers

Think of data access as a stack. Each layer is built on the one beneath it.

Layer You write Example
Database driver (provider) Network protocol, done for you Microsoft.Data.SqlClient, Npgsql, Microsoft.Data.Sqlite
ADO.NET SQL text, connections, commands, readers DbConnection, DbCommand, DbDataReader
Micro-ORM SQL text, but object mapping is automatic Dapper
Full ORM LINQ queries on objects; SQL is generated Entity Framework Core

At the bottom, a provider is a library that knows how to speak one database's protocol. SQL Server, PostgreSQL, MySQL and SQLite all have providers, and everything above them is mostly the same shape.

ADO.NET is the base API in the framework (System.Data). It is deliberately low-level: you open a connection, send a SQL string, and read rows one at a time. It is fast and predictable, but you write a lot of repetitive code and you map columns to properties by hand.

A micro-ORM such as Dapper adds one thing: it maps rows to objects for you. You still write the SQL, so you control exactly what runs. A full ORM such as EF Core goes further and generates the SQL from C# LINQ expressions, tracks changes to objects, and writes INSERT/UPDATE statements for you.

None of these is "the best". They trade control against convenience.

One query, three ways

Here is the same idea, "get the books cheaper than 20 dollars", at each level. These snippets are read-only, because they need packages and a database server.

C#
// Read-only. ADO.NET (provider: Microsoft.Data.SqlClient). You write everything.
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

using var command = new SqlCommand(
    "SELECT Id, Title, Price FROM Books WHERE Price < @max", connection);
command.Parameters.AddWithValue("@max", 20m);

using var reader = await command.ExecuteReaderAsync();
var books = new List<Book>();
while (await reader.ReadAsync())
{
    books.Add(new Book(
        reader.GetInt32(0),
        reader.GetString(1),
        reader.GetDecimal(2)));
}
C#
// Read-only. Dapper (NuGet: Dapper). Same SQL, mapping done for you.
using var connection = new SqlConnection(connectionString);
var books = await connection.QueryAsync<Book>(
    "SELECT Id, Title, Price FROM Books WHERE Price < @max",
    new { max = 20m });
C#
// Read-only. EF Core (NuGet: Microsoft.EntityFrameworkCore.SqlServer). No SQL at all.
var books = await db.Books
    .Where(b => b.Price < 20m)
    .OrderBy(b => b.Title)
    .ToListAsync();

Notice that the code shrinks and the database knowledge hides further away as you go down the list. That helps until something is slow or wrong, and then you must look at the SQL anyway, which EF Core can log for you.

ADO.NET's disconnected model, runnable

ADO.NET has two halves: the connected classes (DbConnection, DbCommand, DbDataReader) which stream rows from a live connection, and the disconnected classes (DataTable, DataSet) which hold data in memory with no connection. The disconnected classes need no database, so we can explore them here. A DataTable is an in-memory table with typed columns, rows and constraints.

C# DataTable: rows, filters and aggregates

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.

Select takes a string expression language that looks like SQL but is not SQL. Field<T> reads a cell with the right type and gives a clear error if the type is wrong. The primary key is enforced in memory with a ConstraintException, the same idea a real database uses.

LINQ over data

Because DataTable is a collection of rows, you can query it with LINQ, as you saw in LINQ basics. AsEnumerable() (from System.Data.DataSetExtensions) turns a table into IEnumerable<DataRow>:

C# LINQ over a DataTable with a relation

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.

Sam has no orders, and GroupJoin still reports him with a count of 0 (a left join). Keep this in mind: relationships, grouping and joins are the vocabulary of data access, and the C# you write over in-memory data transfers directly to LINQ against a database.

What a micro-ORM really does

It is useful to demystify Dapper-like libraries: their central trick is mapping a data reader's columns to an object's properties. Here is a stripped-down version using reflection. DataTable.CreateDataReader() gives us a real DbDataReader without a database.

C# A tiny micro-ORM 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.

That is the heart of it: names in, values out. Real libraries add caching and compiled mapping delegates so they avoid slow reflection per row, plus parameter handling and buffering options. Understanding this also explains the typical mapping bugs: a column that does not match a property name is silently ignored, and a NULL (DBNull) must be handled separately, which is why IsDBNull appears in the loop.

Choosing a layer

There is no universal answer, but these rules of thumb serve well:

  • EF Core is the default for line-of-business applications: many entities, relationships, changing schemas, and a team that prefers C# over SQL strings. Migrations keep the schema under source control.
  • Dapper (or plain ADO.NET) fits performance-critical read paths, reporting queries, stored procedures, and teams that want to see and tune every statement.
  • Mix them. It is common and fine to use EF Core for writes and Dapper for a few heavy reports, even in the same app, on the same connection and transaction.
  • ADO.NET directly appears in tools, bulk loading (SqlBulkCopy), and libraries that must not depend on an ORM.

Decide based on the queries you will actually run, not on a benchmark headline. For most applications, network latency and missing database indexes cost far more than the difference between ORMs. Measure first.

Connection strings

Every provider is told where the database is through a connection string: a list of key=value; pairs such as server, database name, credentials and options. DbConnectionStringBuilder parses and builds these safely without string concatenation, so a password containing a ; cannot inject extra options:

C# Building and reading a connection string

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.

The builder quotes the awkward password value for you. A few rules matter in production:

  • Do not hard-code connection strings in source. Read them from configuration (builder.Configuration.GetConnectionString("Default"), see Configuration and Options), supplied by environment variables or a secret store.
  • Prefer passwordless authentication where available (managed identities, integrated security) so there is no password to leak.
  • Never concatenate user input into a connection string or into SQL. Parameters are covered in ADO.NET.
  • Connection pooling is automatic in most providers: Close()/Dispose() returns the physical connection to a pool, so open late and close early, and do not keep a connection in a static field.

Async and the database

Database calls wait on the network, so use the async versions (ExecuteReaderAsync, ToListAsync) in server code. Threads then return to the pool while the database works, which lets a web server handle many more concurrent requests; see async/await. Pass a CancellationToken (cancellation) so abandoned requests stop their queries.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does a micro-ORM like Dapper add on top of plain ADO.NET?

    Dapper keeps SQL in your hands and automates mapping. LINQ-to-SQL translation and change tracking are what a full ORM like EF Core adds.
  2. 2. Which ADO.NET types work WITHOUT an open connection?

    DataTable and DataSet are in-memory, disconnected containers. DbCommand and DbDataReader need a live connection.
  3. 3. In the tiny mapper, what happens to a column whose name matches no property?

    The mapper only sets properties it finds in its dictionary, so unmatched columns are skipped silently. That quiet behavior is a classic source of null or default values.
  4. 4. Why use DbConnectionStringBuilder instead of building a connection string by concatenation?

    Concatenating raw values lets special characters change the meaning of the string, a form of injection. The builder escapes values correctly.
  5. 5. What is the best default for a typical business application with many related entities?

    EF Core removes a lot of repetitive code and gives migrations, while a micro-ORM or raw SQL remains available for performance-critical queries.

Summary

  • .NET data access is layered: provider, ADO.NET, micro-ORM, full ORM. Each layer trades control for convenience.
  • ADO.NET has connected types (DbConnection, DbCommand, DbDataReader) and disconnected types (DataTable, DataSet).
  • Micro-ORMs map rows to objects; full ORMs also generate SQL from LINQ and track changes.
  • Mixing EF Core with Dapper or raw SQL is normal and sensible.
  • Build connection strings with a builder, keep them in configuration, prefer passwordless auth, and rely on connection pooling.
  • Use async database calls with cancellation in server code.

Next: ADO.NET Concepts