Interview Q&A

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

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

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

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.

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

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.

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

Key Features:

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

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:

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: