Interview Q&A

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.

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.