Interview Q&A

Short answer: Isolation levels define the level of visibility one transaction has into the changes made by other concurrent transactions. The four isolation levels in ADO.NET are:

Real-world example (ShopNest)

Placing an order updates stock and inserts the order row in one SqlTransaction—either both succeed or both roll back.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Batch processing allows you to execute multiple SQL commands in a single round trip to the database, which can improve performance when you have a large number of operations to perform. You can use the SqlCommand object to execute a batch of SQL statements separated by semicolons. Example: SqlCommand command = new SqlCommand();

Example code

command.Connection = connection;
command.CommandText = "INSERT INTO Customers (Name) VALUES ('John'); INSERT INTO Orders (OrderDate) VALUES ('2025-01-01');"; command.ExecuteNonQuery();

Real-world example (ShopNest)

ShopNest’s reporting job still uses ADO.NET for a heavy SQL query where raw performance and stored procedures matter more than EF convenience.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: cross multiple data sources (e.g., SQL Server and other databases).

Explain a bit more

It simplifies transaction management by automatically handling the commit and rollback of transactions cross multiple resources. using (TransactionScope scope = new TransactionScope()) { SqlConnection connection1 = new SqlConnection(connectionString1); SqlConnection connection2 = new SqlConnection(connectionString2); connection1.Open(); connection2.Open(); // Execute commands on both connections scope.Complete(); // Commit the transaction if everything is successful } cross multiple data sources (e.g., SQL Server and other databases). It simplifies transaction management by automatically handling the commit and rollback of transactions cross multiple resources.

Example code

using (TransactionScope scope = new TransactionScope()) { SqlConnection connection1 = new SqlConnection(connectionString1); SqlConnection connection2 = new SqlConnection(connectionString2); connection1.Open(); connection2.Open(); // Execute commands on both connections scope.Complete(); // Commit the transaction if everything is successful }

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The TransactionScope class in ADO.NET is used to handle distributed transactions across multiple data sources (e.g., SQL Server and other databases). It simplifies transaction management by automatically handling the commit and rollback of transactions across multiple resources.

Example code

using (TransactionScope scope = new TransactionScope()) { SqlConnection connection1 = new SqlConnection(connectionString1); SqlConnection connection2 = new SqlConnection(connectionString2); connection1.Open(); connection2.Open(); // Execute commands on both connections scope.Complete(); // Commit the transaction if everything is successful }

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: dapter.UpdateCommand = new SqlCommand("UPDATE Customers SET Name = @Name WHERE CustomerID = @CustomerID", connection); dapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 100, "Name"); dapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 4, "CustomerID"); dapter.UpdateCommand = new SqlCommand("UPDATE… Customers SET Name = @Name…… WHERE CustomerID = @CustomerID", connection);…

Explain a bit more

dapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 100, "Name"); dapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 4, "CustomerID"); dapter.UpdateCommand = new SqlCommand("UPDATE Customers SET Name = @Name WHERE CustomerID = @CustomerID", connection); dapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 100, "Name"); dapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 4, "CustomerID"); dapter.UpdateCommand = new SqlCommand("UPDATE… Customers SET Name = @Name WHERE CustomerID = @CustomerID", connection); dapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 100, "Name");…

Real-world example (ShopNest)

Always pass order ids with parameters: cmd.Parameters.AddWithValue("@id", orderId). Never concatenate user input into SQL.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The UpdateCommand property of the DataAdapter specifies the SQL command used to update the database when changes are made to the data in the DataSet. The Update() method of the DataAdapter uses the UpdateCommand to push changes back to the database.

Example code

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection); adapter.UpdateCommand = new SqlCommand("UPDATE Customers SET Name = @Name WHERE CustomerID = @CustomerID", connection); adapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 100, "Name"); adapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 4, "CustomerID");

Real-world example (ShopNest)

Always pass order ids with parameters: cmd.Parameters.AddWithValue("@id", orderId). Never concatenate user input into SQL.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: concurrency in ADO.NET. Optimistic Concurrency: Assumes that data conflicts are rare. It allows multiple users to read and modify the data without locking the record. However, when updating, it checks if the data has been modified by another user since it was last read. Pessimistic Concurrency: Locks the data when it's being read or modified, preventing other users from accessing it until the transaction is…

Explain a bit more

complete. It can reduce conflicts but may result in performance issues.

Real-world example (ShopNest)

ShopNest’s reporting job still uses ADO.NET for a heavy SQL query where raw performance and stored procedures matter more than EF convenience.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Data-Bound Controls: These controls automatically bind to a data source, such as a DataSet or DataTable, and update the UI when the data changes. Examples include GridView, DropDownList, and ListBox. Manually Bound Controls: These controls do not automatically update when the data changes. You need to manually manage data binding, such as updating the display values when the data changes.

Real-world example (ShopNest)

ShopNest’s reporting job still uses ADO.NET for a heavy SQL query where raw performance and stored procedures matter more than EF convenience.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Performance can be optimized by: Using DataReader for large result sets. Using parameterized queries to avoid SQL injection and improve performance. Enabling connection pooling to reduce the overhead of opening and closing database connections. Using asynchronous operations to prevent blocking the main thread. Minimizing the number of round trips to the database.

Real-world example (ShopNest)

ShopNest’s reporting job still uses ADO.NET for a heavy SQL query where raw performance and stored procedures matter more than EF convenience.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Memory Consumption: A DataSet loads the entire result set into memory, which can lead to high memory usage, especially with large datasets. Performance: Since DataSet is an in-memory representation of data, it can be slower compared to DataReader for large result sets or when working with large amounts of data.

Real-world example (ShopNest)

For large order exports, ShopNest uses SqlDataReader (forward-only, fast). DataSet is heavier and mainly for disconnected editing scenarios.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The DataReader is more efficient than DataSet when dealing with large result sets because it reads data in a forward-only, read-only manner, without storing the entire result set in memory. This results in better performance and lower memory consumption.

Real-world example (ShopNest)

For large order exports, ShopNest uses SqlDataReader (forward-only, fast). DataSet is heavier and mainly for disconnected editing scenarios.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Paging is implemented by retrieving a subset of data, typically using SQL's LIMIT (MySQL), TOP (SQL Server), or ROWNUM (Oracle) to limit the number of rows returned. Example (SQL Server): SqlCommand command = new SqlCommand("SELECT * FROM Customers ORDER BY CustomerID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY", connection); SqlDataReader reader = command.ExecuteReader();

Real-world example (ShopNest)

ShopNest’s reporting job still uses ADO.NET for a heavy SQL query where raw performance and stored procedures matter more than EF convenience.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Stored procedure parameters are handled by adding SqlParameter objects to the SqlCommand's Parameters collection. You set the parameter name, data type, and value.

Example code

SqlCommand command = new SqlCommand("GetCustomerDetails", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@CustomerID", customerId);

Real-world example (ShopNest)

Always pass order ids with parameters: cmd.Parameters.AddWithValue("@id", orderId). Never concatenate user input into SQL.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: A DataRelation defines a relationship between two DataTable objects in a DataSet. It is used to enforce referential integrity and allows for navigating between related data in a parent-child relationship.

Example code

DataRelation relation = new DataRelation("ParentChild", parentTable.Columns["ID"], childTable.Columns["ParentID"]); dataSet.Relations.Add(relation);

Real-world example (ShopNest)

ShopNest’s reporting job still uses ADO.NET for a heavy SQL query where raw performance and stored procedures matter more than EF convenience.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: ADO.NET supports several types of locks during transactions: Shared Lock (S): Allows other transactions to read but not modify the locked data. Exclusive Lock (X): Prevents other transactions from reading or modifying the locked data. Update Lock (U): Allows reading, but prevents other transactions from acquiring an exclusive lock.

Real-world example (ShopNest)

Placing an order updates stock and inserts the order row in one SqlTransaction—either both succeed or both roll back.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Connection Pooling is a technique used to optimize the performance of database connections in ADO.NET.

Explain a bit more

When an application opens a connection to a database, the connection is not always closed immediately.

Example code

"Server=myServerAddress;Database=myDataBase;Integrated

Real-world example (ShopNest)

ShopNest opens a SqlConnection only for the query, then disposes it (using). Connection pooling reuses physical connections automatically.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The ExecuteScalar method in ADO.NET is used to execute a query that returns a single value, typically an aggregate value like a count, sum, or average, or a single field from a row.

Explain a bit more

This method is ideal when you expect a single value as the result, rather than a set of rows. : It returns the first column of the first row in the result set. Any other columns or rows are ignored. It is commonly used for queries that return a single value (e.g., COUNT(), MAX(), MIN()).

Example code

SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM Customers", connection); connection.Open(); int customerCount = (int)command.ExecuteScalar(); Console.WriteLine("Number of customers: " + customerCount); In this example, ExecuteScalar returns the number of rows in the Customers table.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: And columns, and it is used to hold data returned from the database. Key Features: Can hold data from one table. view.RowFilter = "Name = 'Alice'"; view.Sort = "ID DESC"; Summary: DataTable holds data, and DataView provides a dynamic view (filtering, sorting) of the DataTable.

Example code

And columns, and it is used to hold data returned from the database. Key Features: Can hold data from one table. view.RowFilter = "Name = 'Alice'";
view.Sort = "ID DESC"; Summary: DataTable holds data, and DataView provides a dynamic view (filtering, sorting) of the DataTable.

Real-world example (ShopNest)

For large order exports, ShopNest uses SqlDataReader (forward-only, fast). DataSet is heavier and mainly for disconnected editing scenarios.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Both DataTable and DataView are used to represent data in ADO.NET, but they have different purposes: DataTable: Represents a single table of data in-memory.

Explain a bit more

It is a container for rows and columns, and it is used to hold data returned from the database. Key Features: Can hold data from one table. Provides methods for data manipulation (e.g., adding, deleting, modifying rows). Can be filled with data from a DataAdapter. Example: DataTable table = new DataTable(); table.Columns.Add("ID"); table.Columns.Add("Name"); table.Rows.Add(1, "Alice"); table.Rows.Add(2, "Bob"); ● DataView: Provides a way to view and filter data from a DataTable without changing the underlying data. It allows you to sort and filter the data dynamically. Key Features: Acts as a view of a DataTable. Allows for sorting, filtering, and searching the data. It does not modify the underlying DataTable. Example: DataView view = new DataView(table);

Example code

view.RowFilter = "Name = 'Alice'";
view.Sort = "ID DESC"; Summary: DataTable holds data, and DataView provides a dynamic view (filtering, sorting) of the DataTable.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: DataReader offers significant performance advantages over DataSet in specific scenarios, especially when you are working with large volumes of data:

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: ADO.NET? To retrieve a single row or column of data, you can use a SqlCommand and either execute it with ExecuteScalar (for single column data) or ExecuteReader (for a single row). Using ExecuteScalar (for a single column value, like an aggregate): SqlCommand command = new SqlCommand("SELECT CustomerName FROM Customers WHERE CustomerID =… @CustomerID",……… connection); command.Parameters.AddWithValue("@CustomerID",…

Explain a bit more

1); connection.Open(); string customerName = (string)command.ExecuteScalar(); Console.WriteLine("Customer Name: " + customerName); connection.Close(); ADO.NET?

Real-world example (ShopNest)

ShopNest’s reporting job still uses ADO.NET for a heavy SQL query where raw performance and stored procedures matter more than EF convenience.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: To retrieve a single row or column of data, you can use a SqlCommand and either execute it with ExecuteScalar (for single column data) or ExecuteReader (for a single row).

Explain a bit more

Using ExecuteScalar (for a single column value, like an aggregate): SqlCommand command = new SqlCommand("SELECT CustomerName FROM Customers WHERE CustomerID = @CustomerID", connection); command.Parameters.AddWithValue("@CustomerID", 1); connection.Open(); string customerName = (string)command.ExecuteScalar(); Console.WriteLine("Customer Name: " + customerName); connection.Close();

Real-world example (ShopNest)

ShopNest’s reporting job still uses ADO.NET for a heavy SQL query where raw performance and stored procedures matter more than EF convenience.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: You can fetch data from multiple tables in ADO.NET using various SQL techniques, such as JOINs (e.g., INNER JOIN, LEFT JOIN) or subqueries. Steps:

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: And sync with the database? To update multiple records in a DataTable and sync the changes with the database, you need to follow these steps:

Real-world example (ShopNest)

For large order exports, ShopNest uses SqlDataReader (forward-only, fast). DataSet is heavier and mainly for disconnected editing scenarios.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: To update multiple records in a DataTable and sync the changes with the database, you need to follow these steps:

Real-world example (ShopNest)

For large order exports, ShopNest uses SqlDataReader (forward-only, fast). DataSet is heavier and mainly for disconnected editing scenarios.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.