Interview Q&A

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: 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: 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: 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: 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: 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.

Short answer: In an ASP.NET WebForms application, you can use the GridView control to display data from a database by following these steps:

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: When working with large datasets in ADO.NET, it's crucial to ensure that the application does not run into performance issues, such as excessive memory usage or long wait times. Here are some strategies to efficiently retrieve large data:

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: Concurrency issues occur when multiple users attempt to update the same data simultaneously. There are two main strategies to handle concurrency in 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: Data validation ensures that the data being inserted into the database is correct and consistent. You can perform validation at various stages, including the client side (e.g., using JavaScript in a web application) and server side (using ADO.NET). Steps for Server-Side Validation:

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.