👉 Full lesson with practice questions on DevLadder (free, lesson 2 of 9 in the ADO.NET: Connected & Disconnected Data Access course)
// ExecuteScalar: first column of first row
var count = (int)(await new SqlCommand("SELECT COUNT(*) FROM Orders", conn).ExecuteScalarAsync())!;
// ExecuteNonQuery: INSERT/UPDATE/DELETE/DDL; returns rows affected
var cmd = new SqlCommand("UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = @c", conn);
cmd.Parameters.AddWithValue("@c", 3);
int affected = await cmd.ExecuteNonQueryAsync();
// Insert and get the new identity in one round trip
var ins = new SqlCommand("INSERT INTO Customers(Name) OUTPUT INSERTED.Id VALUES (@n)", conn);
ins.Parameters.Add("@n", SqlDbType.NVarChar, 100).Value = "Acme";
int newId = (int)(await ins.ExecuteScalarAsync())!;
Multiple result sets: reader.NextResult() / NextResultAsync() moves to the next SELECT's results.
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: Parameters and SQL injection →
Top comments (0)