Core objects and the connected model — ADO.NET

Lesson 1 of 9 in the free ADO.NET — Connected & Disconnected Data Access course on DevLadder.

const string cs = "Server=.;Database=Shop;Integrated Security=True;TrustServerCertificate=True"; await using var conn = new SqlConnection(cs); await conn.OpenAsync(ct); await using var cmd = new SqlCommand("SELECT Id, Name, Price FROM Products WHERE CategoryId = @cat", conn); cmd.Parameters.Add("@cat", SqlDbType.Int).Value = categoryId; await using var reader = await cmd.ExecuteReaderAsync(ct); var list = new List<Product>(); while (await reader.ReadAsync(ct)) { list.Add(new Product( reader.GetInt32(0), reader.GetString(1), reader.GetDecimal(2))); } 

Use ordinal getters (GetInt32(0)) or cache reader.GetOrdinal("Name") for speed; check reader.IsDBNull(i) before reading nullable columns.

Interview questions on ADO.NET

What is ADO.NET? Name its main objects.

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...).

What is the difference between connected and disconnected architecture?

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).

What is the difference between ExecuteReader, ExecuteScalar and ExecuteNonQuery?

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).

Read the full lesson with progress tracking and the rest of the course on DevLadder — free step-by-step tutorials and interview questions for developers.

Next in the course: ExecuteReader vs ExecuteScalar vs ExecuteNonQuery →