Basic ADO.NET Interview Questions

What is Microsoft ADO.NET?

Visual Studio .NET provides access to databases through the set of tools and namespaces collectively referred to as Microsoft ADO.NET

What are the 3 major types of connection objects in ADO.NET? 
OleDbConnection object: Use an OleDbConnection object to connect to a Microsoft Access or third-party database, such as MySQL. OLE database connections use the OleDbDataAdapter object to perform commands and return data.
SqlConnection object: Use a SqlConnection object to connect to a Microsoft SQL Server database. SQL database connections use the SqlDataAdapter object to perform commands and return data.
OracleConnection object : Use an OracleConnection object to connect to Oracle databases. Oracle database connections use the OracleDataAdapter object to perform commands and return data. This connection object was introduced in the Microsoft .NET Framework version 1.1.

List the 4 common ADO.NET Namespaces?
System.Data : Contains Classes, types, and services for creating and accessing data sets and their subordinate objects
System.Data.SqlClient : Contains Classes and types for accessing Microsoft SQL Server databases
System.Data.OracleClient : Contains Classes and types for accessing Oracle databases (Microsoft .NET Framework version 1.1 and later)
System.Data.OleDb : Contains Classes and types for accessing other databases

List all the steps in order, to access a database through ADO.NET?

• Create a connection to the database using a connection object.
• Invoke a command to create a DataSet object using an adapter object.
• Use the DataSet object in code to display data or to change items in the database.
• Invoke a command to update the database from the DataSet object using an adapter object.
• Close the database connection if you explicitly opened it in step 2 using the Open method. Invoking commands without first invoking the Open method implicitly opens and closes the connection with each request.

Why will you usually create an ASPNET user account in the Database for an ASP.NET web application?
Web applications run using the ASPNET user account. The SQL database administrator will have to set up this account and grant it permissions before your Web application will have access to a SQL database. For file-based databases, such as Microsoft Access, you must grant permissions on the database file to the ASPNET user account using Windows file security settings.

What is the difference between DataReader and DataAdapter? 
1. Data Reader is read-only forward only and much faster than DataAdapter.
2. If you use DataReader you have to open and close connection explicitly whereas if you use DataAdapter the connection is automatically opened and closed.
3. DataReader is connection-oriented whereas Data Adapter is disconnected

Can you inherit from SqlConnection Class?
No, you cannot inherit from SqlConnection Class. SqlConnection Class is a sealed class. It is a compile-time error.

Will the connection be closed, if the SqlConnection object goes out of scope? 
No, If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose of.

What happens if connection pooling is enabled?
If connection pooling is enabled and when you call Close or Dispose of methods, then the connection is returned to the connection pool. This connection can then be reused. If connection pooling is disabled and when you call Close or Dispose of methods, the underlying connection to the server is actually closed.

How do you ensure that the database connections are always closed? 
To ensure that the database connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.
using (SqlConnection ConnectionObject = new SqlConnection())
{
ConnectionObject.Open();
//The database connection will be closed when the control exits the using code block
}

How do you read an XML file into a DataSet?
Using the DataSet object’s ReadXML method.

When do you use ExecuteReader, ExecuteNonQuery, ExecuteScalar methods?
If the command or stored procedure that is being executed returns a set of rows, then we use the ExecuteReader method.
If the command or stored procedure that is being executed returns a single value then we use the ExecuteScalar method.
If the command or stored procedure performs INSERT, DELETE or UPDATE operations, then we use the ExecuteNonQuery method. ExecuteNonQuery method returns an integer specifying the number of rows inserted, deleted or updated.

Can your class inherit from SqlCommand Class? 
No, you cannot inherit from SqlCommand Class. SqlCommand Class is a sealed class. It is a compile-time error.

Give an example that shows how to execute a stored procedure in ADO.NET?
using (SqlConnection ConnectionObject = new SqlConnection())
{
//Specify the name of the stored procedure to execute and the Connection Object to use
SqlCommand CommandObject = new SqlCommand("StoredProcedureName", ConnectionObject);
//Specify the SQL Command type is a stored procedure
CommandObject.CommandType = CommandType.StoredProcedure;
//Open the connection
ConnectionObject.Open();
//Execute the Stored Procedure
int RecordsAffected = CommandObject.ExecuteNonQuery();
}

Can you reuse a SqlCommand object? 
Yes, you can reset the CommandText property and reuse the SqlCommand object.

What are the methods that can ensure the asynchronous execution of the Transact-SQL statement or stored procedure?
BeginExecuteNonQuery
BeginExecuteReader

What is SqlCommand.CommandTimeout Property used for? 
CommandTimeout Property is used to Get or set the wait time before terminating the attempt to execute a command and generating an error.
//Specify the CommandTimeout property value
SqlCommand CommandObject = new SqlCommand("StoredProcedureName", ConnectionObject);
//Wait for 10 seconds to execute the Stored procedure
CommandObject.CommandTimeout = 10; 
The time is in seconds. The default is 30 seconds.