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.

Quick revision for interview day. Print it or save it as a PDF.

Core objects

SqlConnection · SqlCommand · SqlParameter · SqlDataReader · SqlDataAdapter · DataSet/DataTable/DataView · SqlTransaction · SqlBulkCopy · SqlCommandBuilder.

Execute methods

Method	Returns	Use
`ExecuteReader`	DataReader	SELECT rows
`ExecuteScalar`	object (1 value)	COUNT, new ID
`ExecuteNonQuery`	int rows affected	INSERT/UPDATE/DELETE/DDL
`ExecuteXmlReader`	XmlReader	FOR XML queries

Template

csharp

await using var conn = new SqlConnection(cs);
await conn.OpenAsync(ct);
await using var cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
await using var r = await cmd.ExecuteReaderAsync(ct);
while (await r.ReadAsync(ct)) { var name = r.GetString(r.GetOrdinal("Name")); }

Connection string keys

Server · Database · Integrated Security=True / User Id+Password / Authentication=Active Directory Default · Encrypt=True · TrustServerCertificate=True (dev) · Max Pool Size · Connect Timeout · MultipleActiveResultSets · Application Name.

Dapper

Query<T> · QueryFirstOrDefault<T> · QuerySingle<T> · Execute · ExecuteScalar<T> · QueryMultiple · multi-mapping splitOn · IN @Ids · commandType: CommandType.StoredProcedure · pass transaction.

Remember

Always parameterise · explicit SqlDbType + size · using/await using · open late, close early · pool max 100 · Encrypt=True default in SqlClient 4+ · SqlBulkCopy for bulk · TVP for lists · retry transient errors.