Interview Q&A

Short answer: A DataReader is a forward-only, read-only cursor, meaning it streams data from the database and does not store the entire result set in memory. DataSet, on the other hand, loads the entire result set into memory, which can consume significant memory for large datasets.

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: Optimistic Concurrency assumes that conflicts will be rare and allows multiple users to read and modify data without locking it.

Explain a bit more

When updating data, you compare the current data in the database with the data the user fetched earlier (usually by checking a timestamp or version number). If the data has been changed by someone else, you throw a concurrency exception. Steps: Add a timestamp or row version column to the table. When updating, check if the timestamp or row version has changed.

Example code

SqlCommand command = new SqlCommand("UPDATE Customers SET CustomerName = @CustomerName WHERE CustomerID = @CustomerID AND RowVersion = @RowVersion", connection); command.Parameters.AddWithValue("@CustomerName", customerName); command.Parameters.AddWithValue("@CustomerID", customerId); command.Parameters.AddWithValue("@RowVersion", rowVersion); If the RowVersion has changed between the time the user fetched the data and the time they attempt to update, the update will fail, and an exception will be thrown.

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: SqlCommand command = new SqlCommand("SELECT CustomerName, ContactName FROM Customers WHERE CustomerID = @CustomerID", connection); command.Parameters.AddWithValue("@CustomerID", 1); connection.Open(); SqlDataReader reader = command.ExecuteReader();

Example code

if (reader.Read()) // Checks if there's data
{
string customerName = reader["CustomerName"].ToString();
string contactName = reader["ContactName"].ToString(); Console.WriteLine($"Customer: {customerName}, Contact: {contactName}"); } reader.Close(); 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: command or stored procedure. Example: SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection);

Example code

command or stored procedure. Example: SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection);

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 (Active Data Objects .NET) is a data access technology in the .NET framework that enables applications to interact with databases and other data sources.

Explain a bit more

It provides a set of classes for connecting to databases, retrieving data, manipulating data, and updating data. ADO.NET is designed for disconnected data access, meaning that data can be retrieved, modified, and worked with without maintaining an ongoing connection to the database. Real-Time Example: In a C# application, ADO.NET is used to retrieve a list of products from a database and display it in a UI like a GridView. The data is fetched from the database, stored in a DataSet or DataTable, and then bound to the UI controls.

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: pplication). Example (Using Paging in SQL): string query = "SELECT * FROM Customers ORDER BY CustomerID OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@Offset", pageNumber * pageSize); command.Parameters.AddWithValue("@PageSize", pageSize);… connection.Open();……… SqlDataReader reader = command.ExecuteReader(); while…

Explain a bit more

(reader.Read()) { // Process data } reader.Close(); connection.Close(); pplication).

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: dapter.Fill(dataTable); GridView1.DataSource = dataTable; GridView1.DataBind(); } } Here, GridView1 is bound to the data returned from the SQL query (SELECT CustomerID, CustomerName FROM Customers), and the DataBind() method displays it in the grid.

Explain a bit more

dapter.Fill(dataTable); GridView1.DataSource = dataTable; GridView1.DataBind(); } } Here, GridView1 is bound to the data returned from the SQL query (SELECT CustomerID, CustomerName FROM Customers), and the DataBind() method displays it in the grid. dapter.Fill(dataTable); GridView1.DataSource = dataTable; GridView1.DataBind(); } } Here, GridView1 is bound to the data returned from the SQL query (SELECT CustomerID, CustomerName FROM Customers), and the DataBind() method displays it in the grid.

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: Pessimistic Concurrency locks the data when it is being read or modified to ensure that no other transaction can access it until the current operation is complete. This is usually done with SQL transactions and locking hints.

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: Example: protected void Page_Load(object sender, EventArgs e) {

Example code

if (!IsPostBack)
{ SqlConnection connection = new SqlConnection(connectionString); SqlDataAdapter adapter = new SqlDataAdapter("SELECT CustomerID, CustomerName FROM Customers", connection); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); GridView1.DataSource = dataTable; GridView1.DataBind(); }
} Here, GridView1 is bound to the data returned from the SQL query (SELECT CustomerID, CustomerName FROM Customers), and the DataBind() method displays it in the grid.

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: UpdateCommand, and DeleteCommand properties of the DataAdapter to define how data changes should be applied to the database.

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: case, a CustomerID). Practical/Scenario-Based ADO.NET Questions

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 is faster than DataSet because it retrieves data in a streaming manner, one row at a time, without creating copies of the data. DataSet requires more processing to maintain its structure (tables, relationships) and populate in memory.

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: Example of preventing SQL injection: SqlCommand command = new SqlCommand("SELECT * FROM Customers WHERE CustomerName = @CustomerName", connection); command.Parameters.AddWithValue("@CustomerName", customerName); // Use parameterized query

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: Access, Oracle). Example: OleDbCommand command = new OleDbCommand("SELECT * FROM Customers", connection);

Example code

Access, Oracle). Example: OleDbCommand command = new OleDbCommand("SELECT * FROM Customers", connection);

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 main components of ADO.NET are: Connection: Represents the connection to a data source (e.g., SQL Server, Oracle).

Explain a bit more

Example: SqlConnection, OleDbConnection, OracleConnection. Command: Represents a SQL query or stored procedure to be executed against the data source. Example: SqlCommand, OleDbCommand. DataReader: Provides a forward-only, read-only cursor for retrieving data from the database. Example: SqlDataReader. DataSet: Represents an in-memory cache of data, which can hold multiple DataTables and relationships. Example: DataSet. DataAdapter: Acts as a bridge between a DataSet and a data source. It fills a DataSet or updates a data source. Example: SqlDataAdapter.

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 CustomerName = @CustomerName WHERE CustomerID = @CustomerID", connection); dapter.UpdateCommand.Parameters.Add("@CustomerName", SqlDbType.NVarChar, 100, "CustomerName"); dapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 4, "CustomerID"); // Update the database……… dapter.Update(table);… fter modifying the DataTable, the Update method pushes…

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: dapter.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); dapter.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); dapter.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); dapter.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind();

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: dapter.Fill(dataset, "Customers"); // Modify data in the DataSet dataset.Tables["Customers"].Rows[0]["CustomerName"] = "New Name"; // Update the database with the modified data dapter.Update(dataset, "Customers"); dapter.Fill(dataset, "Customers"); // Modify data in the DataSet dataset.Tables["Customers"].Rows[0]["CustomerName"] =… "New Name"; // Update… the… database with the modified data dapter.Update(dataset,…

Explain a bit more

"Customers"); dapter.Fill(dataset, "Customers"); // Modify data in the DataSet dataset.Tables["Customers"].Rows[0]["CustomerName"] = "New Name"; // Update the database with the modified data dapter.Update(dataset, "Customers"); dapter.Fill(dataset, "Customers"); // Modify data in the DataSet dataset.Tables["Customers"].Rows[0]["CustomerName"] =… "New Name"; // Update the database with the modified data dapter.Update(dataset, "Customers");

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: dapter.Fill(dataset, "Customers"); // Populates the DataSet with data from the "Customers" table dapter.Fill(dataset, "Customers"); // Populates the DataSet with data from the "Customers" table dapter.Fill(dataset, "Customers"); // Populates the DataSet with data from the "Customers" table dapter.Fill(dataset, "Customers"); // Populates the DataSet with…

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: database operations asynchronously to avoid blocking the main thread and keep the application responsive.

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: Example: // Assuming you already have a populated DataTable DataTable table = new DataTable(); SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection); // Set commands for Insert, Update, and Delete adapter.UpdateCommand = new SqlCommand("UPDATE Customers SET CustomerName = @CustomerName WHERE CustomerID = @CustomerID", connection); adapter.UpdateCommand.Parameters.Add("@CustomerName",…

Explain a bit more

SqlDbType.NVarChar, 100, "CustomerName"); adapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 4, "CustomerID"); // Update the database adapter.Update(table); After modifying the DataTable, the Update method pushes those changes to the database.

Example code

// Assuming you already have a populated DataTable DataTable table = new DataTable(); SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection); // Set commands for Insert, Update, and Delete adapter.UpdateCommand = new SqlCommand("UPDATE Customers SET CustomerName = @CustomerName WHERE CustomerID = @CustomerID", connection); adapter.UpdateCommand.Parameters.Add("@CustomerName", SqlDbType.NVarChar, 100, "CustomerName"); adapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 4, "CustomerID"); // Update the database adapter.Update(table); After modifying the DataTable, the Update method pushes those changes 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: the data in a DataReader. Example: string query = "SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, Orders.OrderDate " + "FROM Customers " + "INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine("Customer: " +…

Explain a bit more

reader["CustomerName"] + ", Order ID: " + reader["OrderID"]); } reader.Close(); connection.Close(); Alternatively, you can use a DataSet to load data from multiple tables, which will maintain the relationships between the tables. Example (Using DataSet): string query = "SELECT * FROM Customers; SELECT * FROM Orders";

Example code

SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataSet dataSet = new DataSet(); adapter.Fill(dataSet); Here, dataSet.Tables[0] will contain Customers, and dataSet.Tables[1] will contain Orders.

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: DataReader is ideal when you only need to read the data sequentially and don’t need to modify the data or navigate backward.

Explain a bit more

DataSet is better when you need to work with disconnected data, modify it offline, and later update the database. Example (using DataReader for better performance): SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader["CustomerName"].ToString()); } reader.Close(); When to Use: Use DataReader for large result sets where you need fast, sequential data access without requiring complex data manipulation. Use DataSet when you need to work with a disconnected data model or need to modify data offline.

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: Connectivity) drivers. Example: OdbcCommand command = new OdbcCommand("SELECT * FROM Customers", connection);

Example code

Connectivity) drivers. Example: OdbcCommand command = new OdbcCommand("SELECT * FROM Customers", connection);

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: Example: SqlConnection connection = new SqlConnection(connectionString); connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { SqlCommand command1 = new SqlCommand("UPDATE Customers SET Balance = Balance - 100", connection, transaction); SqlCommand command2 = new SqlCommand("UPDATE Accounts SET Balance = Balance + 100", connection, transaction); command1.ExecuteNonQuery();…

Explain a bit more

command2.ExecuteNonQuery(); transaction.Commit(); // Commit the transaction } catch (Exception) { transaction.Rollback(); // Rollback if there is an error } finally { connection.Close(); }

Example code

SqlConnection connection = new SqlConnection(connectionString); connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { SqlCommand command1 = new SqlCommand("UPDATE Customers SET Balance = Balance - 100", connection, transaction); SqlCommand command2 = new SqlCommand("UPDATE Accounts SET Balance = Balance + 100", connection, transaction); command1.ExecuteNonQuery(); command2.ExecuteNonQuery(); transaction.Commit(); // Commit the transaction } catch (Exception) { transaction.Rollback(); // Rollback if there is an error } finally { connection.Close(); }

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.