Interview Q&A

Answer: In ADO.NET, database connections are managed using the Connection object, such as SqlConnection for SQL Server. The process involves:

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.

To execute a stored procedure using ADO.NET:

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.

To bind a DataTable to a GridView in ASP.NET:

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.

DO.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();
}

catch (SqlException ex)

{

Console.WriteLine("Database error: " + ex.Message);

}

catch (Exception ex)

{

Console.WriteLine("General error: " + ex.Message);

}

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:

SqlConnection connection = new SqlConnection(connectionString);

connection.Open();

SqlTransaction transaction1 = connection.BeginTransaction();

SqlTransaction transaction2 = connection.BeginTransaction();

Follow:

try

// First transaction

SqlCommand command1 = new SqlCommand("UPDATE Customers SET

Balance = Balance - 100", connection, transaction1);

command1.ExecuteNonQuery();

transaction1.Commit();

Advanced ADO.NET Questions

Answer: In ADO.NET, the Command object is used to execute SQL queries or stored procedures. The main types of Command objects 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.

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

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

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.

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.

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

DO.NET supports several types of locks during transactions:

• Shared Lock (S): Allows other transactions to read but not modify the locked data.
• Exclusive Lock (X): Prevents other transactions from reading or modifying the

locked data.

• Update Lock (U): Allows reading, but prevents other transactions from acquiring an

exclusive lock.

Connection Pooling is a technique used to optimize the performance of database

connections in ADO.NET. When an application opens a connection to a database, the

connection is not always closed immediately. Instead, it is placed in a connection pool for

reuse by future requests. This avoids the overhead of repeatedly opening and closing

connections to the database.

Key Points:

• When a connection is closed, it is not actually destroyed. Instead, it is returned to

the pool, making it available for reuse.

• Connection Pooling is managed automatically by ADO.NET; developers do not

need to handle the pool directly.

• Connection pooling works by maintaining a pool of database connections for reuse.

When a connection is needed, one is retrieved from the pool; when it is no longer

needed, it is returned to the pool.

Benefits:

• Reduces the overhead of opening and closing database connections.
• Improves application performance by reusing existing connections.

Example:

// The connection string should have pooling enabled by default

string connectionString =
"Server=myServerAddress;Database=myDataBase;Integrated
Security=True;Pooling=True;Max Pool Size=100;";

// Use SqlConnection as usual

SqlConnection connection = new SqlConnection(connectionString);

connection.Open();

The Pooling=True in the connection string ensures that the connections are pooled.

Answer: DataReader offers significant performance advantages over DataSet in specific scenarios, especially when you are working with large volumes of data:

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.

DO.NET?

To retrieve a single row or column of data, you can use a SqlCommand and either execute

it with ExecuteScalar (for single column data) or ExecuteReader (for a single row).

Using ExecuteScalar (for a single column value, like an aggregate):

SqlCommand command = new SqlCommand("SELECT CustomerName FROM

Customers WHERE CustomerID = @CustomerID", connection);

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

connection.Open();

string customerName = (string)command.ExecuteScalar();

Console.WriteLine("Customer Name: " + customerName);

connection.Close();

To retrieve a single row or column of data, you can use a SqlCommand and either execute

it with ExecuteScalar (for single column data) or ExecuteReader (for a single row).

Using ExecuteScalar (for a single column value, like an aggregate):

SqlCommand command = new SqlCommand("SELECT CustomerName FROM

Customers WHERE CustomerID = @CustomerID", connection);

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

connection.Open();

string customerName = (string)command.ExecuteScalar();

Console.WriteLine("Customer Name: " + customerName);

connection.Close();

Answer: You can fetch data from multiple tables in ADO.NET using various SQL techniques, such as JOINs (e.g., INNER JOIN, LEFT JOIN) or subqueries. Steps:

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: nd sync with the database? To update multiple records in a DataTable and sync the changes with the database, you need to follow these steps:

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: To update multiple records in a DataTable and sync the changes with the database, you need to follow these steps:

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.