DEV Community

DIVAKAR UPADHYAY
DIVAKAR UPADHYAY

Posted on Edited on Originally published at devladder.pages.dev

Core objects and the connected model — ADO.NET

#dotnet #beginners #interview #csharp

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

Object Role
SqlConnection Connection to the DB (pooled)
SqlCommand SQL statement or stored procedure
SqlParameter Typed parameter value — prevents SQL injection
SqlDataReader Fast, forward-only, read-only stream of rows
SqlDataAdapter Bridge between DB and DataSet
DataSet / DataTable In-memory relational data
SqlTransaction Groups commands atomically
SqlBulkCopy High-speed bulk insert 
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)));
}
Enter fullscreen mode Exit fullscreen mode

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 →

Top comments (0)