Interview Q&A

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.

Short answer: Example: SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection); DataTable dt = new DataTable(); adapter.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind();

Example code

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection); DataTable dt = new DataTable(); adapter.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind();

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: using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Execute commands here connection.Close(); }

Example code

Example: using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Execute commands here connection.Close(); }

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: Example: SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection); SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter); // Automatically generates insert, update, delete commands DataSet dataset = new DataSet(); adapter.Fill(dataset, "Customers"); // Modify data in the DataSet dataset.Tables["Customers"].Rows[0]["CustomerName"] = "New Name"; // Update the database with the…

Explain a bit more

modified data adapter.Update(dataset, "Customers");

Example code

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection); SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter); // Automatically generates insert, update, delete commands DataSet dataset = new DataSet(); adapter.Fill(dataset, "Customers"); // Modify data in the DataSet dataset.Tables["Customers"].Rows[0]["CustomerName"] = "New Name"; // Update the database with the modified data adapter.Update(dataset, "Customers");

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: SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection); DataSet dataset = new DataSet(); adapter.Fill(dataset, "Customers"); // Populates the DataSet with data from the "Customers" table

Example code

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

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 (ActiveX Data Objects): ADO is a COM-based technology that works with databases in a connected environment (requiring an open connection to the database for the duration of data retrieval).

Explain a bit more

ADO.NET: ADO.NET, in contrast, focuses on disconnected data access. It allows you to retrieve data, work on it offline (disconnected), and then send any changes back to the database. Key Differences: ADO relies on active connections to the database, whereas ADO.NET uses a disconnected model (DataSet, DataTable). ADO.NET provides better support for XML, scalability, and performance in multi-tier applications.

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: database table or view. It's also capable of handling data relationships, such as parent-child relationships. Example: A DataSet might hold a DataTable for customers and another for their orders. DataTable: A single table of in-memory data, which is part of a DataSet. It represents one database table and allows you to work with the data… offline. Example:…… A… DataTable for the Customers table that contains rows…

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: restrictive but guarantees the highest level of consistency. You set the isolation level using the Transaction object: SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable);

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: OracleCommand command = new OracleCommand("SELECT * FROM Customers", connection);

Example code

Example: OracleCommand command = new OracleCommand("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: DataSet: A collection of DataTables and relationships that represent the data in a disconnected mode.

Explain a bit more

A DataSet can hold multiple DataTables, each corresponding to a database table or view. It's also capable of handling data relationships, such as parent-child relationships. Example: A DataSet might hold a DataTable for customers and another for their orders. DataTable: A single table of in-memory data, which is part of a DataSet. It represents one database table and allows you to work with the data offline. Example: A DataTable for the Customers table that contains rows representing each customer.

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: And close the connection when done. SqlConnection connection = new SqlConnection(connectionString); connection.Open(); nd close the connection when done.

Example code

SqlConnection connection = new SqlConnection(connectionString); connection.Open(); And close the connection when done. Example: SqlConnection connection = new SqlConnection(connectionString); connection.Open(); nd close the connection when done. Example: SqlConnection connection = new SqlConnection(connectionString); connection.Open();

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.