Interview Q&A

Short answer: The Connection object represents a connection to a specific data source (e.g., SQL Server). It is used to establish and manage the connection to the database, execute queries, and close the connection when done.

Example code

SqlConnection connection = new SqlConnection(connectionString); connection.Open();

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: Disconnected model: ADO.NET uses a disconnected model (DataSet/DataTable), which allows applications to work offline, reducing the load on the database. Better performance: ADO.NET allows better resource management and can handle large data volumes efficiently. XML support: ADO.NET has built-in support for XML, making it easier to work with XML 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: examples. DataSet: Works in a disconnected mode and holds multiple tables and relationships. You can navigate and manipulate the data offline. Example: Use a DataSet to hold customer and order data for offline processing. DataReader: A forward-only, read-only cursor that requires an open connection to the database. It is faster for reading large amounts of data in a streaming manner. Example: Use DataReader when…

Explain a bit more

fetching records to display in a report or grid in a single-pass, forward-only manner.

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 the database using a DataSet:

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 Fill() method of the DataAdapter is used to populate a DataSet or DataTable with data from the database. It executes the SELECT query defined in the DataAdapter and fills the specified DataSet or DataTable with the results.

Example code

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection); DataSet dataset = new DataSet(); adapter.Fill(dataset, "Customers"); // Fills the DataSet with data from the "Customers" table

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: In ADO.NET, database connections are managed using the Connection object, such as SqlConnection for SQL Server. The process involves:

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: To execute a stored procedure using 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 bind a DataTable to a GridView in ASP.NET:

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: ADO.NET exceptions are typically handled using try-catch blocks. This helps to capture any errors during database operations such as connection failures, query issues, or command execution errors. Example: try { SqlConnection connection = new SqlConnection(connectionString); connection.Open(); SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection); SqlDataReader reader = command.ExecuteReader();

Example code

} catch (SqlException ex) { Console.WriteLine("Database error: " + ex.Message); } catch (Exception ex) { Console.WriteLine("General error: " + ex.Message); }

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 execute multiple transactions sequentially by managing multiple SqlTransaction objects. Each transaction can either be committed or rolled back based on the success or failure of the operations.

Example code

SqlConnection connection = new SqlConnection(connectionString); connection.Open(); SqlTransaction transaction1 = connection.BeginTransaction(); SqlTransaction transaction2 = connection.BeginTransaction(); try { // First transaction SqlCommand command1 = new SqlCommand("UPDATE Customers SET Balance = Balance - 100", connection, transaction1); command1.ExecuteNonQuery(); transaction1.Commit(); // Advanced ADO.NET Questions

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: In ADO.NET, the Command object is used to execute SQL queries or stored procedures. The main types of Command objects are:

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? ADO.NET supports asynchronous database operations using the async and await keywords in C#. This allows the application to remain responsive while waiting for the database operation to complete. ExecuteNonQueryAsync: Executes a SQL command asynchronously. ExecuteReaderAsync: Executes a query and returns a SqlDataReader synchronously.………… ExecuteScalarAsync: Executes a query and returns a single value…

Explain a bit more

asynchronously. public async Task GetDataAsync() { SqlConnection connection = new SqlConnection(connectionString); wait connection.OpenAsync(); SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection); SqlDataReader reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { Console.WriteLine(reader["CustomerName"].ToString()); } reader.Close(); } ADO.NET? ADO.NET supports asynchronous database operations using the async and await keywords in C#. This allows the application to remain responsive while waiting for the database operation to complete. ExecuteNonQueryAsync: Executes a SQL command asynchronously. ExecuteReaderAsync: Executes a query and returns a SqlDataReader synchronously.…… ExecuteScalarAsync: Executes a query and returns a single value asynchronously.

Example code

public async Task GetDataAsync() { SqlConnection connection = new SqlConnection(connectionString); wait connection.OpenAsync(); SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection); SqlDataReader reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { Console.WriteLine(reader["CustomerName"].ToString()); } reader.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: ADO.NET supports asynchronous database operations using the async and await keywords in C#.

Explain a bit more

This allows the application to remain responsive while waiting for the database operation to complete. ExecuteNonQueryAsync: Executes a SQL command asynchronously. ExecuteReaderAsync: Executes a query and returns a SqlDataReader asynchronously. ExecuteScalarAsync: Executes a query and returns a single value asynchronously. Example: public async Task GetDataAsync()

Example code

{
SqlConnection connection = new SqlConnection(connectionString);
await connection.OpenAsync(); SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection); SqlDataReader reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { Console.WriteLine(reader["CustomerName"].ToString()); } reader.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: ADO.NET. A stored procedure is a precompiled set of SQL statements that are stored and executed on the database server. They can improve performance and security by encapsulating complex operations. In ADO.NET, stored procedures are executed using the SqlCommand object, where the CommandType property is set to CommandType.StoredProcedure.

Example code

SqlCommand command = new SqlCommand("GetCustomerDetails", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@CustomerID", customerId); 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: SQL injection attacks can be prevented by:

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