ADO.NET and Dapper

Talk to a database from C# at the lowest level — connections, commands, parameters and readers — and with Dapper.

The foundation: ADO.NET

Every way .NET talks to a relational database — EF Core, Dapper, anything else — is built on ADO.NET, a small set of abstractions implemented by each database's provider:

Concept	SQL Server	PostgreSQL	SQLite
Connection	`SqlConnection`	`NpgsqlConnection`	`SqliteConnection`
Command	`SqlCommand`	`NpgsqlCommand`	`SqliteCommand`
Reader	`SqlDataReader`	`NpgsqlDataReader`	`SqliteDataReader`

You rarely write raw ADO.NET in application code today, but knowing it makes everything built on top understandable.

Querying

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
 
await using var command = new SqlCommand(
    "SELECT Id, Name, Price FROM Products WHERE Price < @maxPrice ORDER BY Name", connection);
command.Parameters.AddWithValue("@maxPrice", 50m);
 
await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    int id = reader.GetInt32(0);
    string name = reader.GetString(1);
    decimal price = reader.GetDecimal(2);
    Console.WriteLine($"{id}: {name} — {price:C}");
}

await using disposes the connection — which returns it to the connection pool instead of closing it, so the next "open" is instant.
The reader streams rows: only the current row is in memory.

Commands that change data

var insert = new SqlCommand(
    "INSERT INTO Products (Name, Price) VALUES (@name, @price); SELECT SCOPE_IDENTITY();", connection);
insert.Parameters.AddWithValue("@name", "Lamp");
insert.Parameters.AddWithValue("@price", 39.99m);
 
var newId = Convert.ToInt32(await insert.ExecuteScalarAsync());   // single value
 
var update = new SqlCommand("UPDATE Products SET Price = Price * 0.9 WHERE Category = @c", connection);
update.Parameters.AddWithValue("@c", "Furniture");
int affected = await update.ExecuteNonQueryAsync();               // rows changed

Always use parameters

Never build SQL by concatenating values. Parameters keep the query text fixed and send values separately: input can't change the query's meaning (no SQL injection), and the database can reuse the cached query plan for every value.

Transactions

await using var tx = await connection.BeginTransactionAsync();
try
{
    // commands with .Transaction = (SqlTransaction)tx ...
    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

Dapper: ADO.NET without the boilerplate

Mapping readers by hand is tedious. Dapper — a micro-ORM created at Stack Overflow — adds extension methods to connections:

record Product(int Id, string Name, decimal Price);
 
var cheap = await connection.QueryAsync<Product>(
    "SELECT Id, Name, Price FROM Products WHERE Price < @MaxPrice ORDER BY Name",
    new { MaxPrice = 50m });                      // anonymous object → parameters
 
var product = await connection.QuerySingleOrDefaultAsync<Product>(
    "SELECT Id, Name, Price FROM Products WHERE Id = @id", new { id = 42 });
 
int rows = await connection.ExecuteAsync(
    "UPDATE Products SET Price = @Price WHERE Id = @Id", product);

You still write SQL — full control, very fast — and Dapper handles parameters and mapping.

ADO.NET, Dapper or EF Core?

	Raw ADO.NET	Dapper	EF Core
You write SQL	✓	✓	Usually not (LINQ)
Mapping to objects	Manual	Automatic	Automatic
Change tracking, migrations	✗	✗	✓
Typical use	Libraries, special cases	Read-heavy queries, reports, performance-critical paths	Most application data access

Many apps combine them: EF Core for everyday CRUD, Dapper for complex reporting queries.

Key takeaways

ADO.NET = connections, commands, parameters and readers; every data library builds on it.
Dispose connections (await using) to return them to the pool.
Always pass values as parameters.
Dapper maps SQL results to objects with minimal overhead; EF Core adds LINQ, change tracking and migrations.

You will learn