Interview Questions

The data access technology built into .NET. Providers such as Microsoft.Data.SqlClient supply connections, commands, data readers and adapters, so C# code can run SQL and stored procedures and read the results. EF Core and Dapper are built on top of it.

Connected: open a SqlConnection, run a SqlCommand and stream rows with a SqlDataReader while the connection stays open. Disconnected: a SqlDataAdapter fills a DataSet/DataTable, closes the connection, and you work with the in-memory copy, later pushing changes back with Update.

ExecuteNonQuery returns the number of rows affected (for INSERT/UPDATE/DELETE). ExecuteScalar returns the first column of the first row (or null if there are no rows). ExecuteReader returns a SqlDataReader for streaming result rows.

Parameters send values separately from the SQL text, so input can never change the query (preventing SQL injection). They also handle types, dates and quotes correctly and let SQL Server reuse execution plans.

1using var cmd = new SqlCommand("SELECT Id, Name FROM Products WHERE Name LIKE @q", conn);
2cmd.Parameters.Add("@q", SqlDbType.NVarChar, 100).Value = 
quot;%{search}%";

using guarantees Dispose (which closes the connection) runs even if an exception is thrown, returning the physical connection to the pool. Leaked connections exhaust the pool and cause timeout errors under load.

The provider keeps open physical connections in a pool per unique connection string (and identity). Open takes one from the pool; Close/Dispose returns it. The default maximum is 100 connections. Open late, close early, and keep connection strings identical.

Set CommandType.StoredProcedure, add input parameters, add an output parameter with Direction = ParameterDirection.Output (and a size for strings), execute, then read parameter.Value. Use ParameterDirection.ReturnValue for the RETURN code.

Database NULL arrives as DBNull.Value, not null. Check reader.IsDBNull(ordinal) before calling typed getters, map to nullable C# types, and send (object?)value ?? DBNull.Value when setting parameter values.

Call connection.BeginTransaction(), assign the transaction to every command, call Commit when all succeed and Rollback (or simply dispose without committing) on failure. Keep transactions short to avoid blocking.

1using var tx = conn.BeginTransaction();
2debit.Transaction = tx; credit.Transaction = tx;
3await debit.ExecuteNonQueryAsync();
4await credit.ExecuteNonQueryAsync();
5tx.Commit();

OpenAsync, ExecuteReaderAsync and ReadAsync free the thread while waiting for SQL Server, so the server handles more concurrent requests with fewer threads. Pass a CancellationToken so abandoned requests stop their queries.

A DataTable is one in-memory table of rows and columns. A DataSet is a container of several DataTables plus DataRelations between them. Both track row state (Added, Modified, Deleted) for adapter updates.

Use SqlBulkCopy with a DataTable or IDataReader source, explicit ColumnMappings, a sensible BatchSize and optionally TableLock. It uses the bulk insert protocol and is orders of magnitude faster than row-by-row INSERTs.

A user-defined table type in SQL Server lets you pass a whole set of rows as one parameter (SqlDbType.Structured with TypeName). Ideal for “WHERE Id IN (list)” or batch inserts without building dynamic SQL.

2627/2601 duplicate key, 547 foreign key or check constraint violation, 1205 deadlock victim (retry), -2 client timeout. Map them to meaningful results such as HTTP 409 instead of a generic 500.

For performance-critical or complex hand-tuned SQL, reporting, bulk operations and stored-procedure-heavy databases. EF Core is more productive for typical CRUD with change tracking and migrations. Many apps mix them.

Read the connection string from configuration, register a small connection factory and repositories in DI, keep SQL in repositories behind interfaces, use async methods and return DTOs or domain objects — never SqlDataReader — to callers.