Interview question and answer on ADO .Net

Interview question and answer on ADO .Net with full explanation and code example

Here are the top 25 interview questions and answers on ADO .NET, along with explanations and code examples:

1. What is ADO .NET? ADO .NET (ActiveX Data Objects .NET) is a data access technology in the .NET framework used to interact with databases. It provides a set of classes and methods for connecting to databases, executing queries, and retrieving and manipulating data.

2. What are the key components of ADO .NET? The key components of ADO .NET are:

• Connection: Represents a connection to a data source.
• Command: Executes queries and commands against the database.
• DataReader: Retrieves data from the database in a read-only, forward-only manner.
• DataAdapter: Populates datasets and updates the database.
• DataSet: Represents an in-memory cache of data retrieved from a database.

3. How do you establish a database connection using ADO .NET? To establish a database connection using ADO .NET, you need to create an instance of the SqlConnection class and pass the connection string as a parameter. Here's an example:

csharp
string connectionString = "Data Source=myServerAddress;Initial Catalog=myDatabase;User ID=myUsername;Password=myPassword;";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

4. How do you execute a query using ADO .NET? To execute a query using ADO .NET, you need to create an instance of the SqlCommand class and pass the SQL query and the connection object. Here's an example:

csharp
string sqlQuery = "SELECT * FROM Customers";
SqlCommand command = new SqlCommand(sqlQuery, connection);
SqlDataReader reader = command.ExecuteReader();

5. How do you retrieve data using a DataReader in ADO .NET? To retrieve data using a DataReader in ADO .NET, you can use the Read() method to move to the next record and retrieve the values using the column index or column name. Here's an example:

csharp
while (reader.Read())
{
    string name = reader.GetString(0); // Retrieve value by column index
    int age = reader.GetInt32(1);
    Console.WriteLine("Name: " + name + ", Age: " + age);
}
reader.Close();

6. How do you close a database connection in ADO .NET? To close a database connection in ADO .NET, you can use the Close() method of the connection object. It's good practice to close the connection once you're done with it. Here's an example:

csharp
connection.Close();

7. What is a DataSet in ADO .NET? A DataSet is an in-memory cache of data retrieved from a database. It can hold multiple DataTables, relationships between tables, and constraints. It provides a disconnected data model, allowing you to work with the data even when not connected to the database.

8. How do you populate a DataSet using a DataAdapter? To populate a DataSet using a DataAdapter, you need to create an instance of the SqlDataAdapter class, specify the SQL query or stored procedure, and provide the connection object. Here's an example:

csharp
string sqlQuery = "SELECT * FROM Customers";
SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connection);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Customers");

9. How do you retrieve data from a DataTable in a DataSet? To retrieve data from a DataTable in a DataSet, you can iterate over the rows of the DataTable and access the values using the column index or column name. Here's an example:

csharp
DataTable customersTable = dataSet.Tables["Customers"];
foreach (DataRow row in customersTable.Rows)
{
    string name = row["Name"].ToString(); // Retrieve value by column name
    int age = Convert.ToInt32(row["Age"]);
    Console.WriteLine("Name: " + name + ", Age: " + age);
}

10. How do you perform parameterized queries in ADO .NET? To perform parameterized queries in ADO .NET, you can use the Parameters property of the SqlCommand object to specify the parameter name, data type, and value. This helps prevent SQL injection attacks and improves performance. Here's an example:

csharp
string sqlQuery = "SELECT * FROM Customers WHERE Country = @Country";
SqlCommand command = new SqlCommand(sqlQuery, connection);
command.Parameters.AddWithValue("@Country", "USA");
SqlDataReader reader = command.ExecuteReader();

11. How do you handle transactions in ADO .NET? To handle transactions in ADO .NET, you can use the SqlTransaction class along with the BeginTransaction(), Commit(), and Rollback() methods. Transactions ensure that a set of database operations either succeed or fail as a single unit. Here's an example:

csharp
SqlTransaction transaction = connection.BeginTransaction();
try
{
    // Perform database operations
    transaction.Commit();
}
catch (Exception ex)
{
    transaction.Rollback();
    Console.WriteLine("Transaction rolled back: " + ex.Message);
}

12. What is the difference between SqlCommand.ExecuteNonQuery() and ExecuteScalar()?

• ExecuteNonQuery(): Executes a query or command that doesn't return any results, such as INSERT, UPDATE, DELETE. It returns the number of affected rows.
• ExecuteScalar(): Executes a query that returns a single value, such as SELECT COUNT(*). It returns the first column of the first row of the result set.

13. How do you handle exceptions in ADO .NET? To handle exceptions in ADO .NET, you can use try-catch blocks and catch the SqlException class. It provides information about the SQL error that occurred, such as error code, message, and inner exception. Here's an example:

csharp
try
{
    // Database operations
}
catch (SqlException ex)
{
    Console.WriteLine("SQL Exception: " + ex.Message);
}
catch (Exception ex)
{
    Console.WriteLine("Exception: " + ex.Message);
}

14. How do you retrieve output parameters from a stored procedure in ADO .NET? To retrieve output parameters from a stored procedure in ADO .NET, you can access the parameter value after executing the command. Make sure to specify the parameter direction as ParameterDirection.Output. Here's an example:

csharp
SqlCommand command = new SqlCommand("MyStoredProcedure", connection);
command.CommandType = CommandType.StoredProcedure;

SqlParameter outputParam = new SqlParameter("@OutputParam", SqlDbType.VarChar);
outputParam.Direction = ParameterDirection.Output;
command.Parameters.Add(outputParam);

command.ExecuteNonQuery();

string outputValue = command.Parameters["@OutputParam"].Value.ToString();

15. How do you handle NULL values retrieved from a database in ADO .NET? To handle NULL values retrieved from a database in ADO .NET, you can use the IsDBNull() method of the DataReader object to check if the value is NULL before retrieving it. Here's an example:

csharp
object value = reader["ColumnName"];
if (value != DBNull.Value)
{
    // Value is not NULL
    // Process the value
}
else
{
    // Value is NULL
}

16. How do you improve performance when working with large datasets in ADO .NET? To improve performance when working with large datasets in ADO .NET, you can use the CommandBehavior.SequentialAccess flag when executing the command. This optimizes memory usage by reading data sequentially rather than loading the entire dataset into memory. Here's an example:

csharp
SqlCommand command = new SqlCommand(sqlQuery, connection);
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

17. How do you handle connection pooling in ADO .NET? ADO .NET automatically handles connection pooling by default. It reuses connections from a pool instead of creating a new connection for each request, improving performance. You don't need to explicitly manage connection pooling in your code.

18. How do you handle multiple result sets in ADO .NET? To handle multiple result sets in ADO .NET, you can use the NextResult() method of the DataReader object to move to the next result set. It allows you to retrieve and process data from each result set sequentially. Here's an example:

csharp
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    // Process data from first result set
}

reader.NextResult();

while (reader.Read())
{
    // Process data from second result set
}

19. How do you retrieve metadata about a database using ADO .NET? To retrieve metadata about a database using ADO .NET, you can use the GetSchema() method of the SqlConnection object. It provides information about tables, columns, indexes, constraints, and more. Here's an example:

csharp
DataTable schemaTable = connection.GetSchema("Tables");
foreach (DataRow row in schemaTable.Rows)
{
    string tableName = row["TABLE_NAME"].ToString();
    Console.WriteLine("Table Name: " + tableName);
}

20. How do you execute a stored procedure in ADO .NET? To execute a stored procedure in ADO .NET, you can set the CommandType of the SqlCommand object to CommandType.StoredProcedure and specify the stored procedure name. Here's an example:

csharp
SqlCommand command = new SqlCommand("MyStoredProcedure", connection);
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();

21. How do you use transactions with multiple commands in ADO .NET? To use transactions with multiple commands in ADO .NET, you can associate the commands with the same SqlTransaction object. This ensures that all the commands are part of the same transaction. Here's an example:

csharp
SqlTransaction transaction = connection.BeginTransaction();
command1.Transaction = transaction;
command2.Transaction = transaction;

try
{
    command1.ExecuteNonQuery();
    command2.ExecuteNonQuery();

    transaction.Commit();
}
catch (Exception ex)
{
    transaction.Rollback();
    Console.WriteLine("Transaction rolled back: " + ex.Message);
}

22. How do you retrieve the identity (auto-increment) value after an INSERT operation in ADO .NET? To retrieve the identity value after an INSERT operation in ADO .NET, you can use the SCOPE_IDENTITY() function in SQL Server or the OUTPUT clause in other databases. Here's an example using SCOPE_IDENTITY():

csharp
string sqlQuery = "INSERT INTO Customers (Name) VALUES (@Name); SELECT SCOPE_IDENTITY();";
SqlCommand command = new SqlCommand(sqlQuery, connection);
command.Parameters.AddWithValue("@Name", "John Doe");
int customerId = Convert.ToInt32(command.ExecuteScalar());

23. How do you handle database transactions in a multi-tier application? In a multi-tier application, you can handle database transactions by implementing a transactional boundary at the appropriate layer (e.g., service layer or data access layer). You can use the TransactionScope class to manage distributed transactions across multiple resources. Here's an example:

csharp
using (TransactionScope scope = new TransactionScope())
{
    // Perform database operations

    scope.Complete();
}

24. How do you implement connection pooling with ADO .NET? ADO .NET automatically handles connection pooling by default. You can enable or disable connection pooling in the connection string by setting the Pooling attribute. For example, Pooling=true enables connection pooling, and Pooling=false disables it.

25. How do you handle concurrent access and concurrency conflicts in ADO .NET? To handle concurrent access and concurrency conflicts in ADO .NET, you can use optimistic concurrency control. This involves using a version column in the database table and checking the version during update operations. If the version doesn't match, it indicates that another user has modified the data. You can handle this situation by throwing an exception or implementing custom conflict resolution logic.

These are some of the top interview questions and answers on ADO .NET. Familiarizing yourself with these concepts and practicing with code examples will help you confidently discuss ADO .NET in interviews.