Skip to content
Tutorials Center
My learning

Topics

Lesson 149 of 182 ยท Data Access

ADO.NET Concepts

Learn ADO.NET in C#: connections, commands, DataReader, parameters against SQL injection, transactions, DataTable change tracking and DataView, with runnable demos.

ADO.NET is the foundation under every .NET database library. Entity Framework Core and Dapper both call it, so understanding its small set of ideas pays off even if you never write raw ADO.NET in your own code. The ideas are few: open a connection, build a command, read results with a reader, pass values as parameters, and group work in a transaction.

Because our in-browser runner has no database driver, the code that talks to a real server is shown read-only and labelled. You will still run the parts that do not need a server: reading rows through a real DbDataReader, DataTable change tracking (which is how ADO.NET models "pending changes"), DataView filtering, and a transaction-shaped pattern. This follows the map in Data Access in .NET.

The connected flow

The classes below live in System.Data.Common, and every provider supplies concrete versions (SqlConnection, NpgsqlConnection, SqliteConnection). Program against the shapes and you can read any provider's code.

Class Role
DbConnection A session with the database. Expensive to create, so it is pooled.
DbCommand One SQL statement or stored procedure call, with parameters.
DbDataReader A forward-only, read-only stream of result rows.
DbTransaction A unit of atomic work on one connection.
DbParameter A typed value passed alongside the SQL text.

The lifecycle never changes: open, command, execute, read, dispose.

C#
// Read-only: needs the NuGet package Microsoft.Data.Sqlite and a database file.
using Microsoft.Data.Sqlite;

await using var connection = new SqliteConnection("Data Source=shop.db");
await connection.OpenAsync();

await using var command = connection.CreateCommand();
command.CommandText = "SELECT Id, Title, Price FROM Books WHERE Price < $max ORDER BY Title";
command.Parameters.AddWithValue("$max", 20m);

await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    int id = reader.GetInt32(0);
    string title = reader.GetString(1);
    decimal price = reader.GetDecimal(2);
    Console.WriteLine($"{id}: {title} ({price})");
}

Three execute methods cover almost everything:

  • ExecuteReader returns rows. Use it for SELECT.
  • ExecuteNonQuery returns the number of affected rows. Use it for INSERT, UPDATE, DELETE and DDL.
  • ExecuteScalar returns the first column of the first row. Use it for COUNT(*) or for the new id from an insert.

The await using lines matter. A connection is a scarce resource: dispose it promptly so it goes back to the connection pool. Opening a connection per operation looks wasteful, but the pool makes it cheap, and keeping one connection for the life of the app causes thread-safety bugs and starves the pool.

Reading with a DataReader

A DbDataReader hands you one row at a time, which means memory stays flat for huge results. The row is not a copy: the reader holds the current position, so you read the columns before calling Read again. Key members:

  • Read() advances to the next row and returns false at the end.
  • GetOrdinal("name") finds a column's index once, so you can avoid string lookups in a hot loop.
  • GetInt32, GetString, GetDecimal read typed values quickly. They throw if the type does not match.
  • IsDBNull(i) checks for a SQL NULL. Calling GetString on a null throws, so check first.
  • NextResult() moves to the next result set when a command returns several.

We cannot open a database here, but DataTable.CreateDataReader() gives us a genuine DbDataReader over in-memory data, so the reading code is exactly what you would write against a server:

C# Reading rows with DbDataReader

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 null subtitle is shown as (no subtitle). Skipping IsDBNull for that column would throw an InvalidCastException. Note that SQL NULL is the type DBNull.Value in ADO.NET, not C# null; this mismatch is behind a surprising number of bugs. Mapping it to nullable types such as string? and int? is what micro-ORMs and EF Core do for you; see nullable reference types.

Parameters: the SQL injection defense

SQL injection happens when user text becomes part of the SQL statement and changes its meaning. The fix is not escaping quotes by hand. The fix is to send values as parameters, which travel separately from the SQL text and can never be parsed as SQL.

Here is the problem. Imagine a login lookup built by string interpolation:

C# How concatenation changes the query

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.

With the hostile text, the unsafe statement becomes ... WHERE Name = 'x' OR '1'='1', which is true for every row. An attacker could also append ; DROP TABLE Users;--. With the parameterized form, the server receives the statement and the value separately, so the quote characters are just part of a string value.

C#
// Read-only. The safe way with ADO.NET (any provider).
command.CommandText = "SELECT Id FROM Users WHERE Name = @name AND Active = @active";

var name = command.CreateParameter();
name.ParameterName = "@name";
name.Value = userInput;           // never part of the SQL text
command.Parameters.Add(name);

var active = command.CreateParameter();
active.ParameterName = "@active";
active.DbType = DbType.Boolean;
active.Value = true;
command.Parameters.Add(active);

Parameters protect values. You cannot parameterize a table name, a column name or an ORDER BY direction. If those come from user input, map them through an allow-list (sortBy switch { "title" => "Title", "price" => "Price", _ => "Id" }) and never insert raw text. Also remember that $"..." with an ordinary string is not a safe API. EF Core's FromSql accepts an interpolated string and converts the holes into parameters, but FromSqlRaw and string.Format do not.

Parameters also help performance: the database can cache and reuse the plan for the same statement text with different values.

Transactions

A transaction makes several statements succeed or fail as one unit. The classic example is moving money: subtract from one account and add to another. If the second step fails, the first must be undone. Databases promise four properties (ACID): atomicity, consistency, isolation, durability.

C#
// Read-only. A transaction with ADO.NET.
await using var transaction = await connection.BeginTransactionAsync();
try
{
    await using var debit = connection.CreateCommand();
    debit.Transaction = transaction;
    debit.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @from";
    // ... add parameters, then:
    await debit.ExecuteNonQueryAsync();

    await using var credit = connection.CreateCommand();
    credit.Transaction = transaction;
    credit.CommandText = "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @to";
    await credit.ExecuteNonQueryAsync();

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

The key rule: every command that belongs to the transaction must be attached to it, and a transaction that is disposed without Commit is rolled back. That makes "forgetting to commit" safe by default. The shape is easy to model in plain C#, which makes the control flow clear:

C# Commit and rollback with a fake transaction

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.

After the failed transfer the balances are exactly what the successful first transfer left: ada=70, lin=50. Real databases implement rollback with logs rather than snapshots, but the contract is the same.

A few practical notes:

  • Keep transactions short. They hold locks. Never wait for user input, an HTTP call, or a sleep inside one.
  • A transaction belongs to one connection. Work on a second connection is outside it.
  • TransactionScope (System.Transactions) can wrap work across several operations, but prefer an explicit DbTransaction or EF Core's SaveChanges, which is transactional by itself.

The disconnected model and change tracking

DataTable remembers what happened to each row. Every DataRow has a RowState (Unchanged, Added, Modified, Deleted, Detached) and keeps both the original and current values. A DbDataAdapter uses this to send only the changed rows back to the database. You can see the mechanism without any database:

C# RowState, GetChanges and RejectChanges

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.

This "track the original, send the delta" idea reappears in EF Core's change tracker (EF Core), just with entity classes instead of rows.

DataView: sort and filter without copying

A DataView is a sorted, filtered window onto a DataTable. It does not copy the data. UI controls and reports use it to show different slices of one table:

C# Filtering and sorting with DataView

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.

Provider-neutral code and pitfalls

You can write code against DbConnection and obtain the concrete type from a DbProviderFactory, but most applications pick one database and use its types directly (or let EF Core abstract it). Dependency injection (DI) usually creates connections or a factory in a scoped way.

Mistakes that cost teams time:

  • Leaking readers. An open DbDataReader ties up its connection. Dispose it and avoid running another command on the same connection while a reader is open (unless the provider enables "multiple active result sets").
  • String SQL built from input. Always parameters, as above.
  • SELECT *. Name your columns; the mapping stays stable when the schema changes.
  • Loading everything. Use WHERE, paging (OFFSET/FETCH or LIMIT) and projection. Do filtering in the database, not in C#.
  • Sync over async. Calling .Result on a database task blocks a thread and can deadlock; use await (async pitfalls).
  • Money as double. Use decimal (and a matching DECIMAL(p,s) column); see floating point.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which ADO.NET execute method should you use to run SELECT COUNT(*) FROM Books?

    ExecuteScalar returns the first column of the first row, which is exactly one value. ExecuteNonQuery is for statements that change data and returns affected rows.
  2. 2. A user types x' OR '1'='1 into a name box. Why does a parameterized query stay safe?

    The statement text is fixed and the value travels as a typed parameter, so it can never change the structure of the SQL.
  3. 3. What happens when a DbTransaction is disposed without calling Commit?

    Uncommitted work is rolled back when the transaction is disposed (or the connection closes), which makes forgetting Commit fail safe.
  4. 4. What does DataTable.GetChanges() return?

    GetChanges returns a new table with only the rows that were added, modified or deleted since the last AcceptChanges, which is what an adapter needs to send.
  5. 5. Which part of a query can NOT be sent as a SQL parameter?

    Parameters carry values, not identifiers. Dynamic column or table names must be chosen from an allow-list in code.

Summary

  • ADO.NET's flow is connection, command, execute, read, dispose; ExecuteReader, ExecuteNonQuery and ExecuteScalar cover the cases.
  • A DbDataReader streams rows forward only; check IsDBNull before reading nullable columns.
  • Always use parameters for values, and an allow-list for identifiers, to prevent SQL injection.
  • Transactions group statements atomically; keep them short, attach every command, and rely on rollback when Commit is never reached.
  • DataTable tracks RowState and original values, GetChanges finds the delta, and DataView filters and sorts without copying.
  • Dispose readers and connections promptly; pooling keeps reopening cheap.

Next: Entity Framework Core