Interview Q&A

CommandType.StoredProcedure.

Review the concept and prepare a concise verbal explanation with a real project example.

Review the concept and prepare a concise verbal explanation with a real project example.

Review the concept and prepare a concise verbal explanation with a real project example.

command or stored procedure.

• Example: SqlCommand command = new SqlCommand("SELECT * FROM

Customers", connection);

ADO.NET (Active Data Objects .NET) is a data access technology in the .NET framework

that enables applications to interact with databases and other data sources. It provides a set

of classes for connecting to databases, retrieving data, manipulating data, and updating

data. ADO.NET is designed for disconnected data access, meaning that data can be

retrieved, modified, and worked with without maintaining an ongoing connection to the

database.

Real-Time Example:

In a C# application, ADO.NET is used to retrieve a list of products from a database and

display it in a UI like a GridView. The data is fetched from the database, stored in a DataSet

or DataTable, and then bound to the UI controls.

executable code.

Review the concept and prepare a concise verbal explanation with a real project example.

empty, check for valid formats, or check for duplicate records).

Review the concept and prepare a concise verbal explanation with a real project example.

hasn't been committed yet. Fast but can result in inconsistencies.

• Optimistic Concurrency assumes that conflicts will be rare and allows multiple

users to read and modify data without locking it.

• When updating data, you compare the current data in the database with the data the

user fetched earlier (usually by checking a timestamp or version number). If the data

has been changed by someone else, you throw a concurrency exception.

Steps:

• Add a timestamp or row version column to the table.
• When updating, check if the timestamp or row version has changed.

Example:

SqlCommand command = new SqlCommand("UPDATE Customers SET

CustomerName = @CustomerName WHERE CustomerID = @CustomerID AND

RowVersion = @RowVersion", connection);

command.Parameters.AddWithValue("@CustomerName", customerName);

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

command.Parameters.AddWithValue("@RowVersion", rowVersion);

Follow:

If the RowVersion has changed between the time the user fetched the data and the time

they attempt to update, the update will fail, and an exception will be thrown.

memory-efficient and fast for large result sets. It reads data row-by-row instead of

loading everything into memory at once, like a DataSet.

DataReader).

SqlCommand command = new SqlCommand("SELECT CustomerName,

ContactName FROM Customers WHERE CustomerID = @CustomerID",

connection);

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

connection.Open();

SqlDataReader reader = command.ExecuteReader();

if (reader.Read()) // Checks if there's data

string customerName = reader["CustomerName"].ToString();

string contactName = reader["ContactName"].ToString();

Console.WriteLine($"Customer: {customerName}, Contact:

{contactName}");

Follow:

reader.Close();

connection.Close();

DataTable.

Review the concept and prepare a concise verbal explanation with a real project example.

The main components of ADO.NET are:

• Connection: Represents the connection to a data source (e.g., SQL Server, Oracle).
• Example: SqlConnection, OleDbConnection, OracleConnection.

Follow:

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

pages) using SQL's LIMIT, OFFSET, or TOP clauses. You can implement paging on

both the server side (in the SQL query) and the client side (in the ASP.NET

application).

Example (Using Paging in SQL):

string query = "SELECT * FROM Customers ORDER BY CustomerID OFFSET

@Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

SqlCommand command = new SqlCommand(query, connection);

command.Parameters.AddWithValue("@Offset", pageNumber * pageSize);

command.Parameters.AddWithValue("@PageSize", pageSize);

connection.Open();

SqlDataReader reader = command.ExecuteReader();

while (reader.Read())

// Process data

Follow:

reader.Close();

connection.Close();

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

Review the concept and prepare a concise verbal explanation with a real project example.

case, a CustomerID).

Practical/Scenario-Based ADO.NET

Questions

Review the concept and prepare a concise verbal explanation with a real project example.

Access, Oracle).

• Example: OleDbCommand command = new OleDbCommand("SELECT *

FROM Customers", connection);

Review the concept and prepare a concise verbal explanation with a real project example.

UpdateCommand, and DeleteCommand properties of the DataAdapter to define

how data changes should be applied to the database.

Example of preventing SQL injection:

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

CustomerName = @CustomerName", connection);

command.Parameters.AddWithValue("@CustomerName", customerName); //

Use parameterized query

Review the concept and prepare a concise verbal explanation with a real project example.

Example:

protected void Page_Load(object sender, EventArgs e)

if (!IsPostBack)

SqlConnection connection = new

SqlConnection(connectionString);

SqlDataAdapter adapter = new SqlDataAdapter("SELECT

CustomerID, CustomerName FROM Customers", connection);

DataTable dataTable = new DataTable();

Follow:

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.

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

Review the concept and prepare a concise verbal explanation with a real project example.

Review the concept and prepare a concise verbal explanation with a real project example.

Review the concept and prepare a concise verbal explanation with a real project example.

Review the concept and prepare a concise verbal explanation with a real project example.

Review the concept and prepare a concise verbal explanation with a real project example.

ExecuteScalar() depending on the type of result.

Example:

Follow:

SqlCommand command = new SqlCommand("GetCustomerDetails",

connection);

command.CommandType = CommandType.StoredProcedure;

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

SqlDataReader reader = command.ExecuteReader();

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

dates).

Example:

string customerName = txtCustomerName.Text;

if (string.IsNullOrWhiteSpace(customerName))

throw new ArgumentException("Customer name cannot be empty");

SqlCommand command = new SqlCommand("INSERT INTO Customers

(CustomerName) VALUES (@CustomerName)", connection);

command.Parameters.AddWithValue("@CustomerName", customerName);

connection.Open();

command.ExecuteNonQuery();

connection.Close();

Follow:

This ensures that the CustomerName is not empty before inserting it into the database.

Example:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM

Customers", connection);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

// Automatically generates insert, update, delete commands

Follow:

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

database operations asynchronously to avoid blocking the main thread and keep the

application responsive.

possible.

Connectivity) drivers.

• Example: OdbcCommand command = new OdbcCommand("SELECT *

FROM Customers", connection);

Example:

// Assuming you already have a populated DataTable

DataTable table = new DataTable();

Follow:

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.

Example:

Follow:

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

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

Follow:

Console.WriteLine("Customer: " + 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";

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.

Example:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM

Customers", connection);

DataTable dt = new DataTable();

Follow:

adapter.Fill(dt);

GridView1.DataSource = dt;

GridView1.DataBind();

Example:

using (SqlConnection connection = new

SqlConnection(connectionString))

connection.Open();

// Execute commands here

connection.Close();

• DataReader is ideal when you only need to read the data sequentially and

don’t need to modify the data or navigate backward.

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

Follow:

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.

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

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

Follow:

restrictive but guarantees the highest level of consistency.

You set the isolation level using the Transaction object:

SqlTransaction transaction =

connection.BeginTransaction(IsolationLevel.Serializable);

Follow:

• Example: OracleCommand command = new OracleCommand("SELECT

* FROM Customers", connection);

• DataSet: A collection of DataTables and relationships that represent the data in a

disconnected mode. 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.

• DataSet: It is a disconnected, in-memory data structure that can hold multiple tables.

It can also be updated and later written back to the database. You can move back

and forth between rows (using DataRow and DataColumn).

• DataReader: A DataReader is a forward-only, read-only data cursor. It provides

faster, streaming access to the data from the database but doesn’t allow

modifications. It maintains an open connection while reading data.

Example:

• DataSet: If you're working on a report with multiple tables, such as Customers,

Orders, and Products, you'd use a DataSet.

• DataReader: If you're fetching customer details one by one for a quick operation, a

DataReader would be used.

Follow:

A DataAdapter serves as a bridge between a DataSet/DataTable and the database. It is

used to fill a DataSet with data and to update changes made in the DataSet back to the

database.

Example:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM

Customers", connection);

DataSet dataset = new DataSet();

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

the Customers table

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:

SqlConnection connection = new SqlConnection(connectionString);

connection.Open();

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:

Follow:

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.

Follow:

• 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

Follow:

Intermediate ADO.NET Questions

The SqlDataAdapter serves as a bridge between a DataSet (or DataTable) and the

database. It is responsible for:

To update the database using a DataSet:

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

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

In ADO.NET, database connections are managed using the Connection object, such as

SqlConnection for SQL Server. The process involves:

To execute a stored procedure using ADO.NET:

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

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

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:

Follow:

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.

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

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

In ADO.NET, the Command object is used to execute SQL queries or stored procedures.

The main types of Command objects are:

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:

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:

SqlCommand command = new SqlCommand("GetCustomerDetails",

connection);

command.CommandType = CommandType.StoredProcedure;

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

SqlDataReader reader = command.ExecuteReader();

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:

Follow:

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

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

Follow:

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

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:

Follow:

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

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:

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.

Follow:

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

Follow:

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

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

ADO.NET supports several types of locks during transactions:

Follow:

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

Follow:

connection.Open();

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

The ExecuteScalar method in ADO.NET is used to execute a query that returns a single

value, typically an aggregate value like a count, sum, or average, or a single field from a row.

This method is ideal when you expect a single value as the result, rather than a set of rows.

Key Points:

• It returns the first column of the first row in the result set. Any other columns or

rows are ignored.

• It is commonly used for queries that return a single value (e.g., COUNT(), MAX(),

MIN()).

Example:

SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM

Customers", connection);

connection.Open();

int customerCount = (int)command.ExecuteScalar();

Console.WriteLine("Number of customers: " + customerCount);

In this example, ExecuteScalar returns the number of rows in the Customers table.

Both DataTable and DataView are used to represent data in ADO.NET, but they have

different purposes:

• DataTable: Represents a single table of data in-memory. It is a container for rows

and columns, and it is used to hold data returned from the database.

Key Features:

Follow:

• Can hold data from one table.
• Provides methods for data manipulation (e.g., adding, deleting, modifying

rows).

• Can be filled with data from a DataAdapter.

Example:

DataTable table = new DataTable();

table.Columns.Add("ID");

table.Columns.Add("Name");

table.Rows.Add(1, "Alice");

table.Rows.Add(2, "Bob");

• DataView: Provides a way to view and filter data from a DataTable without changing

the underlying data. It allows you to sort and filter the data dynamically.

Key Features:

• Acts as a view of a DataTable.
• Allows for sorting, filtering, and searching the data.
• It does not modify the underlying DataTable.

Example:

DataView view = new DataView(table);

view.RowFilter = "Name = 'Alice'";

view.Sort = "ID DESC";

Summary:

• DataTable holds data, and DataView provides a dynamic view (filtering, sorting) of

the DataTable.

Follow:

DataReader offers significant performance advantages over DataSet in specific scenarios,

especially when you are working with large volumes of data:

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

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:

To update multiple records in a DataTable and sync the changes with the database, you

need to follow these steps:

In an ASP.NET WebForms application, you can use the GridView control to display data

from a database by following these steps:

When working with large datasets in ADO.NET, it's crucial to ensure that the application

does not run into performance issues, such as excessive memory usage or long wait times.

Here are some strategies to efficiently retrieve large data:

Concurrency issues occur when multiple users attempt to update the same data

simultaneously. There are two main strategies to handle concurrency in ADO.NET:

Data validation ensures that the data being inserted into the database is correct and

consistent. You can perform validation at various stages, including the client side (e.g.,

using JavaScript in a web application) and server side (using ADO.NET).

Steps for Server-Side Validation:

The BatchUpdate method is used to send multiple SQL commands (such as insert, update,

or delete) in a single round trip to the database. This improves performance by reducing the

overhead of sending multiple individual commands.

While BatchUpdate isn't directly exposed as a method on the DataAdapter object, you

can achieve similar functionality by manually creating a batch of commands and executing

them in a single call.

Example (Using SqlDataAdapter):

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM

Customers", connection);

SqlCommandBuilder builder = new SqlCommandBuilder(adapter); //

Automatically generates commands for Update, Insert, and Delete

DataTable table = new DataTable();

adapter.Fill(table);

// Modify DataTable as needed

// Update changes to the database in one go (BatchUpdate)

adapter.Update(table);

Follow: