ADO.NET — Connected & Disconnected Data Access

In short: ADO.NET is .NET's core data access library (System.Data + provider packages).

• Main objects: Connection (connects to the DB), Command (executes SQL/procs), Parameter, DataReader (forward-only fast read), DataAdapter (fills/updates DataSets), DataSet/DataTable (in-memory data), Transaction.
• For SQL Server use Microsoft.Data.SqlClient (SqlConnection, SqlCommand...).

In short: Connected: the connection stays open while you read with a DataReader — forward-only, read-only, very fast, minimal memory.

• Disconnected: a DataAdapter fills a DataSet/DataTable, then closes the connection;
• you can navigate, edit and later push changes back with Update() — more memory, more flexibility.
• Modern apps mostly use the connected model (directly or via Dapper/EF).

In short: ExecuteReader returns a DataReader for one or more result sets.

• ExecuteScalar returns the first column of the first row (COUNT, SUM, new identity via OUTPUT/SCOPE_IDENTITY()).
• ExecuteNonQuery runs INSERT/UPDATE/DELETE/DDL and returns the number of rows affected (-1 for statements that don't affect rows, or when SET NOCOUNT ON is set).

In short: DataReader: connected, forward-only, read-only, one row at a time, fastest and lowest memory — use for streaming results to APIs.

• DataSet: disconnected, in-memory collection of DataTables with relations and constraints, random access and editable, can be serialised to XML — heavier;
• used in legacy WinForms/reporting.
• EF/Dapper map to objects instead.

In short: Always use parameters (@name placeholders + SqlParameter with explicit type/size) or stored procedures with parameters — never concatenate user input into SQL.

• Validate input, use least-privilege DB accounts, and for dynamic identifiers (table/column names) use an allow-list.
• ORMs (EF, Dapper) parameterise automatically.

In short: ADO.NET reuses open physical connections from a pool keyed by the exact connection string.

• Open() borrows, Close()/Dispose() returns.
• Default max is 100 per pool.
• Exhaustion symptoms: "Timeout expired… obtaining a connection from the pool".
• Causes: missing using/dispose, connections held across long work or awaits of slow external calls, long transactions, or traffic beyond 100 concurrent DB operations.
• Fix: dispose properly, shorten connection lifetimes, async I/O, raise Max Pool Size only after fixing leaks, monitor with performance counters/sys.dm_exec_sessions.

In short: var tx = conn.BeginTransaction(isolationLevel), assign tx to every command (new SqlCommand(sql, conn, tx)), execute, then tx.Commit();

• on exception tx.Rollback().
• Disposing an uncommitted transaction rolls it back.
• For multiple connections/resources use TransactionScope (async flow enabled), avoiding distributed escalation in the cloud.
• Keep transactions short to reduce locking.

In short: It infers the SQL type from the .NET value: strings become nvarchar with a length equal to the value, decimals get inferred precision.

• Mismatches with column types (e.g. varchar column vs nvarchar parameter) force implicit conversions that can turn index seeks into scans, and varying lengths create many cached plans.
• Use Parameters.Add("@p", SqlDbType.VarChar, 50).Value = x with explicit type and size.

Set CommandType = CommandType.StoredProcedure, add input parameters, add an output parameter with Direction = ParameterDirection.Output (and size for strings), optionally a ReturnValue parameter, execute (ExecuteNonQuery), then read param.Value after execution (after closing the reader if you used ExecuteReader).

In short: Dapper is a micro-ORM: extension methods (Query<T>, Execute, QueryMultiple) on IDbConnection that map rows to objects using cached IL.

• Compared with raw ADO.NET it removes mapping boilerplate with almost the same speed;
• compared with EF Core it has no change tracking, LINQ, migrations or relationship management — you write all SQL.
• Use Dapper for read-heavy, performance-critical or complex SQL/stored procedures; EF Core for domain-rich write models.
• They combine well.

In short: Pool exhaustion.

• 1) Search for connections/readers not in using blocks and for code holding connections across slow calls (HTTP, file I/O) or long transactions.
• 2) Check sync-over-async blocking that keeps connections open longer.
• 3) Check slow queries (Query Store) — slow queries hold connections.
• 4) Monitor active connections (sys.dm_exec_sessions by program name).
• 5) Only then tune Max Pool Size, and consider caching or fewer round trips (batching).
• Also ensure one consistent connection string.

In short: Stream the file (don't load it all) with a CSV reader (CsvHelper) exposing an IDataReader or batches of DataTables;

• SqlBulkCopy into a staging table with TableLock and a batch size (10k–50k);
• then a set-based stored procedure validates and MERGEs/INSERTs into target tables inside a transaction;
• log rejected rows;
• make it idempotent (truncate staging first, track file/batch IDs).
• Alternatives: SSIS or Azure Data Factory for ops-managed pipelines.