Top 10 ADO.NET Questions for Junior Devs – Answers & Code

Are you sure you can explain why a SqlDataReader can’t rewind—but a DataSet can? 70 % of junior candidates stumble on fundamentals like this and lose easy points. Let’s make sure you’re in the other 30 %!

Why ADO.NET still matters in 2025

Even if your day-to-day is Entity Framework Core or Dapper, many interviewers start with raw ADO.NET to probe your grasp of connections, commands, and data flow. Nail these, and higher-level ORMs suddenly make a lot more sense.

10 Core Questions, Answers & Code Samples

What is ADO.NET and what are its main components?

Answer: ADO.NET is the low-level data-access layer in the .NET Base Class Library (BCL). It exposes two programming models:

• Connected – streams data directly from the database (e.g., SqlDataReader).
• Disconnected – caches data in memory (e.g., DataSet, DataTable).

Key classes (SQL Server provider):

Analogy: Think of the connected model as listening to live radio—you hear each song once, in order. The disconnected model is downloading a playlist—you can replay, sort, or modify offline.

Explain the difference between the connected and disconnected models

• Connected (SqlDataReader)
  • Keeps the network socket open—low memory, high throughput.
  • Read-only, forward-only.
  • Requires manual loop:

using var conn = new SqlConnection(cs);
using var cmd  = new SqlCommand("SELECT Id,Name FROM Users", conn);
conn.Open();
using var rdr = cmd.ExecuteReader();
while (rdr.Read())
    Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)}");

• Disconnected (DataSet)
  • Fills an in-memory table, then closes the connection.
  • Editable rows, supports constraints, XML, and serialization.
  • Higher RAM; suitable for caching or binding to UI controls.

Common interview pitfall: Claiming that DataTable is “thread-safe”—it is not.

DataReader vs DataSet—when would you choose each?

• Use SqlDataReader when you need speed and only read data once (e.g., populating a dropdown).
• Use DataSet/DataTable when you must:
  • Edit rows offline.
  • Bind to WinForms/WPF grids.
  • Serialize to XML/JSON for a web service.

My rule-of-thumb: Reader for reports, Dataset for workbooks.

How do you execute a stored procedure with parameters?

using var conn = new SqlConnection(cs);
using var cmd  = new SqlCommand("usp_AddUser", conn) {
    CommandType = CommandType.StoredProcedure
};
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = "Alice";
cmd.Parameters.Add("@Id",   SqlDbType.Int).Direction = ParameterDirection.Output;

conn.Open();
cmd.ExecuteNonQuery();
int newId = (int)cmd.Parameters["@Id"].Value;

Key points interviewers expect:

• Set CommandType.StoredProcedure.
• Use strongly-typed SqlParameters—don’t rely on implicit adds.
• Retrieve output values after ExecuteNonQuery.

What is SQL injection and how do you prevent it in ADO.NET?

• Problem: Concatenating user data into SQL lets attackers run arbitrary commands.
• Solution: Always use parameterized queries or stored procedures. ADO.NET will send parameter metadata separately, so the DB treats user input as data, not code.

var cmd = new SqlCommand(
  "SELECT * FROM Products WHERE Name LIKE @search + '%'", conn);
cmd.Parameters.Add("@search", SqlDbType.NVarChar, 100).Value = userInput;

Tip: Never call SqlCommand.EscapeString()—because it doesn’t exist. The framework forces you toward parameters for a reason!

Show an example of a parameterized INSERT and explain @ScopeIdentity

string sql = @"INSERT INTO Orders(CustomerId,Total)
VALUES (@custId,@total);
SELECT SCOPE_IDENTITY();";
using var cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@custId", custId);
cmd.Parameters.AddWithValue("@total",  total);

conn.Open();
int orderId = Convert.ToInt32(cmd.ExecuteScalar());

• SCOPE_IDENTITY() returns the last identity value only within the current scope—safer than @@IDENTITY which can jump across triggers.

What is connection pooling and how does it work in ADO.NET?

• Connection pool: A cache of open physical connections keyed by connection string.
• Why: Opening TCP, TLS, and TDS handshakes is costly (≈20 ms).
• How: You call SqlConnection.Open()—ADO.NET grabs a ready connection from the pool. When you call Dispose()/Close(), the connection returns to the pool instead of truly closing.
• Turn it off? Add Pooling=false to the connection string—but you almost never should.

Interview landmine: Confusing pooling (client-side) with maximum connections on the SQL Server.

Walk through reading data with SqlDataReader step by step

1. Create & open connection.
2. Create command with SQL or stored procedure.
3. ExecuteReader(CommandBehavior.CloseConnection) – ensures the connection closes when the reader is closed.
4. Loop while reader.Read().
5. Extract columns via GetInt32, GetString, etc. (avoid boxing with object).
6. Dispose reader (using or await using).

await using var conn = new SqlConnection(cs);
await conn.OpenAsync();

await using var cmd = new SqlCommand("SELECT Id,Email FROM Users", conn);
await using var rdr = await cmd.ExecuteReaderAsync(
    CommandBehavior.CloseConnection);

while (await rdr.ReadAsync())
    Console.WriteLine($"{rdr.GetInt32(0)} => {rdr.GetString(1)}");

How do you perform CRUD with a SqlDataAdapter?

var adapter = new SqlDataAdapter(
    "SELECT Id,Name FROM Categories", conn);

var builder = new SqlCommandBuilder(adapter); // auto-generates INSERT/UPDATE/DELETE
var table   = new DataTable();
adapter.Fill(table);

// modify in memory
table.Rows.Add(null, "New Category");

// push changes
adapter.Update(table);

• SqlCommandBuilder inspects the SELECT command and auto-creates the other commands.
• Good for quick admin tools; avoid it in high-volume code—hand-tune your SQL instead.

Which exceptions are common in ADO.NET and how do you handle them?

Always wrap only the minimal code in try/catch; let polite errors surface to callers but never swallow exceptions silently.

FAQ: Quick ADO.NET Interview Prep

Conclusion: Own Your Next Junior .NET Interview

Mastering these ten questions flips the interview script—you’ll move from reactive guessing to confident teaching. Try coding each snippet tonight, test the exceptions, and watch how naturally the answers roll off your tongue tomorrow.

Your turn: Which ADO.NET concept here felt least intuitive, and how would you explain it to a friend? Drop your thoughts below—I read every comment!

Please enable JavaScript in your browser to complete this form.

Did you find this post useful? *

• Post Was Useful
• Need To Improve

Submit Feedback