Interview Q&A

The Command object is used to execute SQL queries or stored procedures against a

database. It encapsulates the SQL statement or stored procedure and returns results.

Example:

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

connection);

SqlDataReader reader = command.ExecuteReader();

Follow:

• ExecuteNonQuery: Executes SQL commands that do not return data (e.g., INSERT,

UPDATE, DELETE).

• Example: command.ExecuteNonQuery(); (used to insert a record).
• ExecuteReader: Executes SQL commands that return rows (e.g., SELECT queries).

It returns a DataReader.

• Example: command.ExecuteReader(); (used to select records).
• ExecuteScalar: Executes SQL commands and returns a single value (e.g., a single

cell of data).

• Example: command.ExecuteScalar(); (used for retrieving the count or

aggregate values).

The ConnectionString contains information required to connect to the database, such as

the database server, database name, credentials, and other configurations.

Example:

string connectionString = "Data Source=server_name;Initial
Catalog=database_name;User ID=user_name;Password=password;";

• SqlConnection: Used specifically for connecting to SQL Server databases.
• OleDbConnection: A more general connection class that can connect to a variety of

data sources, such as MS Access, Excel, Oracle, etc.

Example:

Use SqlConnection for SQL Server:

SqlConnection sqlConnection = new SqlConnection(connectionString);

• Use OleDbConnection for Access or other databases:

OleDbConnection oleDbConnection = new

OleDbConnection(connectionString);

• SQLCommand: A SQL command is a string of SQL code (like SELECT, INSERT,

etc.) that is executed directly against the database.

• Stored Procedure: A precompiled collection of SQL statements that can be

executed as a unit. It can contain more complex logic, including control-of-flow and

error handling.

Example:

• SQL Command: SELECT * FROM Customers;
• Stored Procedure: EXEC GetCustomerDetails;

Connection Pooling allows multiple applications or threads to reuse existing database

connections instead of opening a new connection each time. It improves performance by

reducing the overhead of opening and closing connections repeatedly.

Example: When using SQL Server, the connection pool automatically manages the reuse of

connections.

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

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 fetching records to display in a report or grid

in a single-pass, forward-only manner.

The CommandTimeout property specifies the amount of time (in seconds) before a

command is considered to have timed out. If the command execution takes longer than the

specified time, an error is raised.

Example:

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

connection);

command.CommandTimeout = 30; // Timeout after 30 seconds
Intermediate ADO.NET Questions

Answer: The SqlDataAdapter serves as a bridge between a DataSet (or DataTable) and the database. It is responsible for:

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 update the database using a DataSet:

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.

mount of data quickly and do not need to modify the data. It requires an open connection to

the database and reads data sequentially, one row at a time.

Example:

SqlCommand command = new SqlCommand("SELECT CustomerName FROM

Customers", connection);

SqlDataReader reader = command.ExecuteReader();

while (reader.Read())

{

Console.WriteLine(reader["CustomerName"]);

}

reader.Close();

The SqlDataReader is used to retrieve data from the database in a forward-only and

read-only manner. It is more efficient than a DataSet when you need to retrieve a large

amount of data quickly and do not need to modify the data. It requires an open connection to

the database and reads data sequentially, one row at a time.

Example:

SqlCommand command = new SqlCommand("SELECT CustomerName FROM

Customers", connection);

SqlDataReader reader = command.ExecuteReader();

while (reader.Read())

Console.WriteLine(reader["CustomerName"]);

reader.Close();

Answer: dapter.Fill(dataset, "Customers"); // Fills the DataSet with data from the "Customers" table

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.

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:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM

Customers", connection);

DataSet dataset = new DataSet();

Follow:

adapter.Fill(dataset, "Customers"); // Fills the DataSet with data

from the "Customers" table

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.

• DataSet is a collection of DataTable objects, and it can hold multiple tables and

relationships between them. It is used for handling disconnected data and can work

offline, and it can also support complex structures, such as parent-child relationships.

• DataTable represents a single table in-memory and contains rows and columns. It

can be used for simpler scenarios where only one table of data is needed.

Example:

• DataSet: Holds multiple tables, such as Customers and Orders.
• DataTable: Holds data from a single table like Customers.

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

}

The SqlConnection object is responsible for opening a connection to a SQL Server

database. It represents the physical connection to the data source and must be open before

executing commands (like SqlCommand) or reading data (using SqlDataReader or

SqlDataAdapter).

Example:

SqlConnection connection = new SqlConnection(connectionString);

connection.Open();

// Execute database commands

connection.Close();

• Strongly Typed DataSet: A DataSet that is generated from an XSD (XML Schema

Definition) file, which defines the structure of the data (tables, columns,

relationships). It provides type safety and compile-time checking.

• Loosely Typed DataSet: A DataSet that does not have predefined schemas,

meaning you access tables and columns by name at runtime. This offers flexibility but

no compile-time checking.

Example:

• Strongly Typed: You get intellisense and type safety when accessing columns.
• Loosely Typed: You need to access tables and columns by name as strings, without

intellisense.

Answer: transaction in ADO.NET is a sequence of database operations that are executed as a single unit. If one operation fails, all previous operations are rolled back. You can manage transactions using the SqlTransaction object. 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.

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.