DLDevLadder📒 My Notebook
Roadmap › 1. .NET › Step 13
ADO

ADO.NET — Connected & Disconnected Data Access

Connected vs disconnected architecture, SqlConnection, SqlCommand, DataReader, DataSet/DataAdapter, parameters, stored procedures, transactions, connection pooling, bulk copy, async and Dapper micro-ORM.
What it is & where it is used

ADO.NET is the low-level data access API in .NET (System.Data). Every ORM — Entity Framework, Dapper — is built on it. Interviews ask about it because it shows you understand what happens between your code and the database.

Two architectures:

  • Connected — DbConnection + DbCommand + DbDataReader: open a connection, stream rows forward-only, close. Fast, low memory.
  • Disconnected — DataAdapter fills an in-memory DataSet/DataTable; the connection is closed while you work; changes are pushed back with Update().

Providers: Microsoft.Data.SqlClient (SQL Server — the current, recommended package; System.Data.SqlClient is deprecated), Npgsql (PostgreSQL), MySqlConnector, Oracle.ManagedDataAccess.Core, Microsoft.Data.Sqlite.

Dapper (Stack Overflow, 2011) is a micro-ORM: extension methods on IDbConnection that map query results to objects, keeping SQL fully in your control with near-ADO.NET speed.

Versions & history
YearMilestone
2002ADO.NET 1.0 with .NET Framework 1.0 (successor of COM-based ADO)
2005ADO.NET 2.0: provider factories (DbProviderFactory), SqlBulkCopy, MARS, async-ish APIs
2008.NET 3.5: LINQ to SQL, LINQ to DataSet; EF 1.0 (SP1)
2012.NET 4.5: real async (OpenAsync, ExecuteReaderAsync)
2011Dapper released by Stack Overflow
2019Microsoft.Data.SqlClient 1.0 — new SQL Server driver shipped via NuGet
2022–2025Microsoft.Data.SqlClient 5.x/6.x: TDS 8 / strict encryption, Encrypt=true by default (4.0+), Entra ID (Azure AD) authentication, SqlBatch (.NET 6 DbBatch)

Breaking-change gotcha: Microsoft.Data.SqlClient 4.0+ defaults to Encrypt=True. Local dev servers with self-signed certificates need TrustServerCertificate=True (dev only).

Lifecycle

Connected access lifecycle:

  1. Create SqlConnection with a connection string.
  2. Open()/OpenAsync() — gets a physical connection from the pool (or creates one).
  3. Create SqlCommand (SQL text or stored procedure) + parameters.
  4. Execute: ExecuteReader (rows), ExecuteScalar (single value), ExecuteNonQuery (rows affected).
  5. Read with SqlDataReader.Read() forward-only, column by column.
  6. Close/Dispose reader → command → connection (with using) — the connection returns to the pool, not physically closed.

Disconnected lifecycle: SqlDataAdapter.Fill(dataSet) (opens and closes the connection automatically) → work offline (edit rows; each DataRow has a RowState: Added, Modified, Deleted, Unchanged) → adapter.Update(dataSet) generates INSERT/UPDATE/DELETE (via SqlCommandBuilder or explicit commands) → AcceptChanges().

Transaction lifecycle: BeginTransaction() → assign to every command → execute → Commit() or Rollback() (automatic rollback on dispose without commit).

Tools used to develop
  • SQL Server Management Studio (SSMS) / Azure Data Studio (retiring Feb 2026 in favour of the VS Code MSSQL extension) — write and test SQL.
  • SQL Server Profiler / Extended Events — see what your app sends.
  • Visual Studio Server Explorer / SQL Server Object Explorer.
  • Dapper, Dapper.Contrib, DbUp (migrations as SQL scripts), RepoDB.
  • Testcontainers for .NET — real SQL Server in Docker for tests.
  • Polly — retry transient SQL errors (or SqlRetryLogicProvider in SqlClient 3.0+).