Interview Q&A

DO.NET?

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

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

asynchronously.

• ExecuteScalarAsync: Executes a query and returns a single value asynchronously.

Example:

public async Task GetDataAsync()

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();

Follow:

DO.NET.

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:

SqlCommand command = new SqlCommand("GetCustomerDetails",

connection);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@CustomerID", customerId);

SqlDataReader reader = command.ExecuteReader();

way to safely and securely inject data into queries, reducing the risk of SQL injection

ttacks.

In ADO.NET, you handle parameters using the Parameters collection of a SqlCommand

object.

Example:

SqlCommand command = new SqlCommand("SELECT * FROM Customers WHERE

CustomerID = @CustomerID", connection);

command.Parameters.AddWithValue("@CustomerID", customerId);

Parameters are used to pass values to SQL commands or stored procedures. They provide

a way to safely and securely inject data into queries, reducing the risk of SQL injection

attacks.

In ADO.NET, you handle parameters using the Parameters collection of a SqlCommand

object.

Example:

SqlCommand command = new SqlCommand("SELECT * FROM Customers WHERE

CustomerID = @CustomerID", connection);

command.Parameters.AddWithValue("@CustomerID", customerId);

SQL injection attacks can be prevented by:

What interviewers expect

• A clear definition tied to ADO.NET in ADO.NET projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production ADO.NET application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in ADO.NET architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Answer: snapshot of the data and remains unchanged even if the data in the database changes during the operation. It is slower and consumes more memory compared to forward-only cursor.

What interviewers expect

• A clear definition tied to ADO.NET in ADO.NET projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production ADO.NET application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in ADO.NET architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

• Forward-only cursor: A cursor that allows you to read rows sequentially in a forward

direction only. It is fast and lightweight, but you cannot go back to previous rows.

• Static cursor: A cursor that allows both forward and backward navigation. It provides

a snapshot of the data and remains unchanged even if the data in the database

changes during the operation. It is slower and consumes more memory compared to

a forward-only cursor.

n ADO.NET DataProvider is a set of classes used to interact with different types of data

sources (such as SQL Server, Oracle, etc.). It provides methods for opening connections,

executing commands, and retrieving data.

The main types of DataProviders are:

• SqlClient (for SQL Server)
• OleDb (for OLE DB-compatible data sources)
• Odbc (for ODBC-compatible data sources)
• Oracle (for Oracle databases)

llows you to define the name, data type, size, and value of a parameter. SqlParameter is

used to protect against SQL injection and to pass data to SQL queries safely.

Example:

SqlCommand command = new SqlCommand("SELECT * FROM Customers WHERE

CustomerID = @CustomerID", connection);

SqlParameter parameter = new SqlParameter("@CustomerID",

SqlDbType.Int);

parameter.Value = customerId;

command.Parameters.Add(parameter);

The SqlParameter class represents a parameter to a SQL command or stored procedure. It

allows you to define the name, data type, size, and value of a parameter. SqlParameter is

used to protect against SQL injection and to pass data to SQL queries safely.

Example:

SqlCommand command = new SqlCommand("SELECT * FROM Customers WHERE

CustomerID = @CustomerID", connection);

SqlParameter parameter = new SqlParameter("@CustomerID",

SqlDbType.Int);

parameter.Value = customerId;

command.Parameters.Add(parameter);

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:

What interviewers expect

• A clear definition tied to ADO.NET in ADO.NET projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production ADO.NET application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in ADO.NET architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

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();
command.Connection = connection;
command.CommandText = "INSERT INTO Customers (Name) VALUES ('John');

INSERT INTO Orders (OrderDate) VALUES ('2025-01-01');";

command.ExecuteNonQuery();

cross multiple data sources (e.g., SQL Server and other databases). It simplifies

transaction management by automatically handling the commit and rollback of transactions

cross multiple resources.

Example:

using (TransactionScope scope = new TransactionScope())
{

SqlConnection connection1 = new

SqlConnection(connectionString1);

SqlConnection connection2 = new

SqlConnection(connectionString2);

connection1.Open();

connection2.Open();

// Execute commands on both connections

scope.Complete(); // Commit the transaction if everything is

successful

}

The TransactionScope class in ADO.NET is used to handle distributed transactions

across multiple data sources (e.g., SQL Server and other databases). It simplifies

transaction management by automatically handling the commit and rollback of transactions

across multiple resources.

Example:

using (TransactionScope scope = new TransactionScope())

SqlConnection connection1 = new

SqlConnection(connectionString1);

SqlConnection connection2 = new

SqlConnection(connectionString2);

connection1.Open();

connection2.Open();

// Execute commands on both connections

scope.Complete(); // Commit the transaction if everything is

successful

Follow:

dapter.UpdateCommand = new SqlCommand("UPDATE Customers SET Name =

@Name WHERE CustomerID = @CustomerID", connection);

dapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar,

100, "Name");

dapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int,

4, "CustomerID");

The UpdateCommand property of the DataAdapter specifies the SQL command used to

update the database when changes are made to the data in the DataSet. The Update()

method of the DataAdapter uses the UpdateCommand to push changes back to the

database.

Example:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM

Customers", connection);

adapter.UpdateCommand = new SqlCommand("UPDATE Customers SET Name =

@Name WHERE CustomerID = @CustomerID", connection);

adapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar,

100, "Name");

adapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int,

4, "CustomerID");

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 complete. It can

reduce conflicts but may result in performance issues.

• Data-Bound Controls: These controls automatically bind to a data source, such as a

DataSet or DataTable, and update the UI when the data changes. Examples include

GridView, DropDownList, and ListBox.

• Manually Bound Controls: These controls do not automatically update when the

data changes. You need to manually manage data binding, such as updating the

display values when the data changes.

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.

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

The DataReader is more efficient than DataSet when dealing with large result sets because

it reads data in a forward-only, read-only manner, without storing the entire result set in

memory. This results in better performance and lower memory consumption.

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();

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:

SqlCommand command = new SqlCommand("GetCustomerDetails",

connection);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@CustomerID", customerId);

DataRelation defines a relationship between two DataTable objects in a DataSet. It is

used to enforce referential integrity and allows for navigating between related data in a

parent-child relationship.

Example:

DataRelation relation = new DataRelation("ParentChild",

parentTable.Columns["ID"], childTable.Columns["ParentID"]);

dataSet.Relations.Add(relation);