Database Programming with ADO.NET

ADO.NET is the low-level data access layer of .NET.

Concept

ADO.NET is the low-level data access layer of .NET. Every database has a provider that implements the abstract classes in System.Data.Common:

SQL Server provider (Microsoft.Data.SqlClient for SQL Server, Npgsql for PostgreSQL, MySqlConnector for MySQL — see the SQL and MySQL courses for the SQL itself.)

Statements without results (INSERT, UPDATE, DDL) ExecuteNonQuery[Async] → affected rows

ExecuteReader[Async] → DbDataReader (forward-only, read-only stream)

Connection strings hold the data source and options; build them with a ConnectionStringBuilder and keep credentials out of source code.

Connection pooling: Open() usually takes an existing physical connection from a pool and Dispose() returns it. So: open late, close early, always with using.

Parameters send values separately from the SQL text. They prevent SQL injection and let the server reuse execution plans. Identifiers (table/column names) cannot be parameters — validate them against a whitelist.

Transactions group statements atomically: BeginTransaction → Commit, or Rollback on error.

Prefer the async methods in servers and UIs.

Why It Matters

ADO.NET is what ORMs (EF Core, Dapper) use internally, and it is still the right tool for bulk operations, stored procedures, and tools that run hand-written SQL (test result databases, calibration stores). SQL injection remains one of the most exploited vulnerabilities.

Syntax

await using var conn = new SqliteConnection(connectionString);
await conn.OpenAsync();
await using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT firmware FROM ecu WHERE code = $code";
cmd.Parameters.AddWithValue("$code", code);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync()) { string fw = reader.GetString(0); }

Example

49.1 Connections, commands, parameters, transactions, readers and SQL injection

The SQLite provider comes from the Microsoft.Data.Sqlite package.

#:package [email protected]
using System.Data;
using System.Data.Common;
using Microsoft.Data.Sqlite;

// A file database so pooling and multiple connections behave like a real server database
string path = Path.Combine(Path.GetTempPath(), "eh_adonet_demo.db");
File.Delete(path);
string cs = new SqliteConnectionStringBuilder { DataSource = path, Pooling = true }.ToString();
Console.WriteLine($"connection string: {cs.Replace(Path.GetTempPath(), "<temp>/")}");

// Connection -> Command -> ExecuteNonQuery
await using (var conn = new SqliteConnection(cs))
{
    await conn.OpenAsync();
    Console.WriteLine($"state: {conn.State}, server version: SQLite {conn.ServerVersion}");
    await using DbCommand ddl = conn.CreateCommand();
    ddl.CommandText = """
        CREATE TABLE ecu (id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, firmware TEXT NOT NULL);
        CREATE TABLE dtc (id INTEGER PRIMARY KEY, ecu_id INTEGER NOT NULL REFERENCES ecu(id), code TEXT NOT NULL, active INTEGER NOT NULL);
        """;
    await ddl.ExecuteNonQueryAsync();

    // Parameters: values travel separately from the SQL text (no injection, plan reuse)
    await using var insert = conn.CreateComma
…

Expected Result

The failed second transaction was rolled back completely, so the UPDATE to 9.9.9 never became visible. The concatenated query turned the input into SQL (OR '1'='1') and matched every row; the parameterized query treated the same text as a plain value and matched nothing.

connection string: Data Source=<temp>/eh_adonet_demo.db;Pooling=True
state: Open, server version: SQLite 3.53.3
committed 3 DTC rows
rolled back: SQLite Error 19: 'UNIQUE constraint failed: ecu.code'.
VCU firmware after rollback: 2.4.1
columns: code, total, active
  BMS  total 1  active 1
  GW   total 0  active 0
  VCU  total 2  active 1
concatenated query: SELECT COUNT(*) FROM ecu WHERE code = '' OR '1'='1'
  rows matched: 3   <- every row leaked
parameterized query rows matched: 0
generic DbConnection count ecu: 3
generic DbConnection count dtc: 3

Common Mistakes

Building SQL with string concatenation or interpolation from user input.

Not disposing connections/readers → pool exhaustion ("timeout expired... all pooled connections were in use").

Keeping one connection open for the application's lifetime.

Forgetting to assign cmd.Transaction (some providers throw, others run outside the transaction).

Reading columns by ordinal after the query's column order changed — use GetOrdinal.

Treating DBNull.Value as null (check with IsDBNull).

Best Practices

Always parameterize; whitelist identifiers.

using/await using for connections, commands, readers and transactions.

Keep transactions short; never wait for user input inside one.

Program against DbConnection when you need provider independence.

Consider Dapper for convenient mapping on top of ADO.NET, and EF Core for full ORM features.

Interview Relevance

High: "how do you prevent SQL injection?", "what is connection pooling?", "ExecuteNonQuery vs ExecuteScalar vs ExecuteReader?", "how do transactions work in ADO.NET?", "ADO.NET vs Dapper vs EF Core?".

Practice Questions

Insert 10 000 rows with and without a transaction and compare the time.

Write Task<List<Ecu>> GetEcusAsync(DbConnection conn) that maps a reader to records.

Add a nullable column and read it safely with IsDBNull.

Implement a whitelist-based ORDER BY column parameter.

Quick Quiz

Q1. (Multiple choice) Which method returns the first column of the first row? A) ExecuteNonQuery B) ExecuteScalar C) ExecuteReader D) Prepare Answer: B.

Q2. (Predict the output) Input ' OR '1'='1 concatenated into WHERE code = '...' on a table with 3 rows. How many rows match?

Answer: 3 — every row (verified). With a parameter: 0.

Q3. (True/False) Disposing a pooled connection closes the physical database connection.

Answer: False. It returns the connection to the pool.

Q4. (Find the bug) cmd.CommandText = "SELECT * FROM @table"; cmd.Parameters.AddWithValue("@table", name);

Answer: Identifiers cannot be parameters; validate name against a whitelist and insert it into the SQL text.

Coding Challenge

Using Microsoft.Data.Sqlite in memory, create a reading(sensor TEXT, value REAL) table, insert five readings with one parameterized command inside a transaction, and print the average per sensor with a reader.

#:package [email protected]
using Microsoft.Data.Sqlite;

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
using (var ddl = conn.CreateCommand())
{
    ddl.CommandText = "CREATE TABLE reading (sensor TEXT NOT NULL, value REAL NOT NULL)";
    ddl.ExecuteNonQuery();
}

using (var tx = conn.BeginTransaction())
using (var insert = conn.CreateCommand())
{
    insert.Transaction = tx;
    insert.CommandText = "INSERT INTO reading VALUES ($s, $v)";
    var s = insert.Parameters.Add("$s", SqliteType.Text);
    var v = insert.Parameters.Add("$v", SqliteType.Real);
    foreach (var (sensor, value) in new[] { ("temp", 21.5), ("temp", 22.5), ("volt", 12.6), ("volt", 12.4), ("volt", 12.5) })
    {
        s.Value = sensor; v.Value = value;
        insert.ExecuteNonQuery();
    }
    tx.Commit();
}

using var query = conn.CreateCommand();
query.CommandText = "SELECT sensor, AVG(value), COUNT(*) FROM reading GROUP BY sensor ORDER BY sensor";
using var reader = query.ExecuteReader();
while (reader.Read())
    Console.WriteLine($"{reader.GetString(0)}: avg {reader.GetDouble(1):F2} over {reader.GetInt64(2)} readings");