Interview Q&A

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.

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.