Top 33 ADO.NET Interview Questions And Answers

Q: What is the `SqlCommandBuilder`?

**CommandBuilder** helps you to generate update, delete, and insert commands on a single database table for a data adapter. Similar to other objects, each data provider has a command builder class. The `OleDbCommandBuilder`, `SqlCommonBuilder`, and `OdbcCommandBuilder` classes represent the `CommonBuilder` object in the OleDb, Sql, and ODBC data providers.

Q: What is _Connection Pooling_ in ADO.NET?

ADO.NET uses a technique called **connection pooling**, which minimizes the cost of repeatedly opening and closing connections. > Connection pooling reuses existing active connections with the same connection string instead of creating new connections when a request is made to the database. It involves the use of a connection manager that is responsible for maintaining a list, or pool, of available connections for a given connection string. Several pools exist if different connection strings ask for connection pooling.

Question 1

What is ADO.NET?

Accepted Answer

**ADO** stands for Active Data Object and ADO.NET is a set of .NET libraries for ADO.
NET is a collection of managed libraries used by .NET applications for data source communication using a driver or provider:

*   Enterprise applications handle a large amount of data. This data is primarily stored in relational databases, such as Oracle, SQL Server, and Access and so on. These databases use Structured Query Language (SQL) for retrieval of data.  
*   To access enterprise data from a .NET application, an interface was needed. This interface acts as a bridge between an RDBMS system and a .NET application. ADO.NET is such an interface that is created to connect .NET applications to RDBMS systems.  
*   In the .NET framework, Microsoft introduced a new version of Active X Data Objects (ADO) called ADO.NET. Any .NET application, either Windows based or web based, can interact with the database using a rich set of classes of the ADO.NET library. Data can be accessed from any database using connected or disconnected architecture.

Question 2

What are the ADO.NET components?

Accepted Answer

ADO.NET components categorized in three modes: 
* disconnected, 
* common or shared and 
* the .NET data providers.

The disconnected components build the basic ADO.NET architecture. You can use these components (or classes) with or without data providers. For example, you can use a `DataTable` object with or without providers and shared or common components are the base classes for data providers. Shared or common components are the base classes for data providers and shared by all data providers. The data provider components are specifically designed to work with different kinds of data sources. For example, ODBC data providers work with ODBC data sources and OleDb data providers work with OLE-DB data sources.

Question 3

What is the `DataAdapter` object in ADO.NET?

Accepted Answer

A **DataAdapter** is used to retrieve data from a data source and populate tables within a `DataSet`. Data Adapters form the bridge between a data source and a dataset. The `DataAdapter` also resolves changes made to the `DataSet` back to the data source. The `DataAdapter` uses the `Connection` object of the .NET Framework data provider to connect to a data source, and it uses `Command` objects to retrieve data from and resolve changes to the data source.

A `DataAdapter` supports mainly the following two methods:

*   **Fill():** The Fill method populates a dataset or a data table object with data from the database. It retrieves rows from the data source using the SELECT statement specified by an associated select command property. The Fill method leaves the connection in the same state as it encountered before populating the data.     
*   **Update():** The Update method commits the changes back to the database. It also analyzes the RowState of each record in the DataSet and calls the appropriate INSERT, UPDATE, and DELETE statements.

Question 4

What do you understand by `DataRelation` class?

Accepted Answer

The **DataRelation** is a class of disconnected architecture in the .NET framework. It is found in the `System.Data` namespace. It represents a relationship between database tables and correlates tables on the basis of matching column.

Question 5

What is the basic difference between ADO.NET and Entity Framework?

Accepted Answer

ADO.NET Entity Framework is an ORM (object-relational mapping) which creates a higher abstract object model over ADO.NET components. ADO.NET is a layer closer to the database (datatables, datasets and etc...). The main and the only benefit of EF is it auto-generates code for the Model (middle layer), Data Access Layer, and mapping code, thus reducing a lot of development time. Consider the following example:

**ADO.NET**:
```csharp
DataTable table = adoDs.Tables[0];
for (int j = 0; j < table.Rows.Count; j++)
{
    DataRow row = table.Rows[j];

// Get the values of the fields
    string CustomerName =
        (string)row["Customername"];
    string CustomerCode =
        (string)row["CustomerCode"];
}
```

**EF**:
```csharp
foreach (Customer objCust in obj.Customers)
{}
```

Question 6

What is the `SqlCommandBuilder`?

Accepted Answer

**CommandBuilder** helps you to generate update, delete, and insert commands on a single database table for a data adapter. Similar to other objects, each data provider has a command builder class. The `OleDbCommandBuilder`, `SqlCommonBuilder`, and `OdbcCommandBuilder` classes represent the `CommonBuilder` object in the OleDb, Sql, and ODBC data providers.

Question 7

What is _Connection Pooling_ in ADO.NET?

Accepted Answer

ADO.NET uses a technique called **connection pooling**, which minimizes the cost of repeatedly opening and closing connections.

> Connection pooling reuses existing active connections with the same connection string instead of creating new connections when a request is made to the database.

It involves the use of a connection manager that is responsible for maintaining a list, or pool, of available connections for a given connection string. Several pools exist if different connection strings ask for connection pooling.

Question 8

What is `SqlCommand` object?

Accepted Answer

The **SqlCommand** carries the SQL statement that needs to be executed on the database. SqlCommand carries the command in the CommandText property and this property will be used when the SqlCommand calls any of its execute methods.

*   The `Command` Object uses the connection object to execute SQL queries.
*   The queries can be in the form of Inline text, Stored Procedures or direct Table access.
*   An important feature of `Command` object is that it can be used to execute queries and Stored Procedures with Parameters.
*   If a select query is issued, the result set it returns is usually stored in either a `DataSet` or a `DataReader` object.

The three important methods exposed by the SqlCommand object is shown below:

*   `ExecuteScalar`
*   `ExecuteNonQuery`
*   `ExecuteReader`

Question 9

How can you define the `DataSet` structure?

Accepted Answer

A **DataSet** object falls in disconnected components series. The `DataSet` consists of a collection of tables, rows, columns and relationships.

`DataSet` contains a collection of `DataTables` and the `DataTable` contains a collection of `DataRows`, `DataRelations`, and `DataColumns`. A `DataTable` maps to a table in the database.

Question 10

What is exactly meaning of _disconnected_ and _connected_ approach in ADO.NET?

Accepted Answer

In short:
* **Disconnected** - Make Connection , Fetch Data , Close Connection
* **Connected** - Make Connection , Keep Connection alive , Close Connection when close is called.

The ADO.NET architecture, in which connection must be kept open till the end to retrieve and access data from database is called as _connected architecture_. Connected architecture is built on the these types - `connection`, `command`, `datareader`

The ADO.NET architecture, in which connection will be kept open only till the data retrieved from database, and later can be accessed even when connection to database is closed is called as _disconnected architecture_. Disconnected architecture of ADO.net is built on these types - `connection`, `dataadapter`, `commandbuilder` and `dataset` and `dataview`.

Top 33 ADO.NET Interview Questions

What is ADO.NET?

What is `SqlCommand` object?

Describe when you would use the `DataView` in ADO.NET?

What is the basic difference between ADO.NET and Entity Framework?

What is Connection Pooling in ADO.NET?

What is the `SqlCommandBuilder`?

What is exactly meaning of disconnected and connected approach in ADO.NET?

What are the ADO.NET components?

What is the `DataAdapter` object in ADO.NET?

How can you define the `DataSet` structure?

What do you understand by `DataRelation` class?

What is the difference between `DataView`, `DataTable` and `DataSet`?

What is the difference between `Integrated Security = True` and `Integrated Security = SSPI`?

What is the difference between `ExecuteScalar`, `ExecuteReader` and `ExecuteNonQuery`?

What is Unit Of Work?

What are the differences between using `SqlDataAdapter` vs `SqlDataReader` for getting data from a DB?

Could you explain me some of the main differences between Connection-oriented access and Connectionless access in ADO.NET?

How could you control connection pooling behavior?

Mention what is the difference between ADO.NET and classic ADO?

Where should I use connected architecture approach?

What is the difference between OLE DB and ODBC data sources?

What is the difference between ADODB, OLEDB and ADO.NET?

Name types of transactions in ADO.NET

Can you explain the difference between a `DataReader`, a `DataAdapter`, a `Dataset`, and a `DataView`?

What's better: `DataSet` or `DataReader`?

Is there anything faster than `SqlDataReader` in .NET?

How could you monitor connection pooling behavior?

Could you explain some benefits of Repository Pattern?

Where should I use disconnected architecture approach?

Is it necessary to manually close and dispose of `SqlDataReader`?

What is the best and fast way to insert 2 million rows of data into SQL Server?

Name some problems that could occur with connection pooling

Under what scenarios would setting `pooling=false` in an ADO.NET connection string be of value when connecting to SQL Server?

Top 33 ADO.NET Interview Questions

What is ADO.NET?

What is SqlCommand object?

Describe when you would use the DataView in ADO.NET?

What is the basic difference between ADO.NET and Entity Framework?

What is Connection Pooling in ADO.NET?

What is the SqlCommandBuilder?

What is exactly meaning of disconnected and connected approach in ADO.NET?

What are the ADO.NET components?

What is the DataAdapter object in ADO.NET?

How can you define the DataSet structure?

What do you understand by DataRelation class?

What is the difference between DataView, DataTable and DataSet?

What is the difference between Integrated Security = True and Integrated Security = SSPI?

What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

What is Unit Of Work?

What are the differences between using SqlDataAdapter vs SqlDataReader for getting data from a DB?

Could you explain me some of the main differences between Connection-oriented access and Connectionless access in ADO.NET?

How could you control connection pooling behavior?

Mention what is the difference between ADO.NET and classic ADO?

Where should I use connected architecture approach?

What is the difference between OLE DB and ODBC data sources?

What is the difference between ADODB, OLEDB and ADO.NET?

Name types of transactions in ADO.NET

Can you explain the difference between a DataReader, a DataAdapter, a Dataset, and a DataView?

What's better: DataSet or DataReader?

Is there anything faster than SqlDataReader in .NET?

How could you monitor connection pooling behavior?

Could you explain some benefits of Repository Pattern?

Where should I use disconnected architecture approach?

Is it necessary to manually close and dispose of SqlDataReader?

What is the best and fast way to insert 2 million rows of data into SQL Server?

Name some problems that could occur with connection pooling

Under what scenarios would setting pooling=false in an ADO.NET connection string be of value when connecting to SQL Server?

What is `SqlCommand` object?

Describe when you would use the `DataView` in ADO.NET?

What is the `SqlCommandBuilder`?

What is the `DataAdapter` object in ADO.NET?

How can you define the `DataSet` structure?

What do you understand by `DataRelation` class?

What is the difference between `DataView`, `DataTable` and `DataSet`?

What is the difference between `Integrated Security = True` and `Integrated Security = SSPI`?

What is the difference between `ExecuteScalar`, `ExecuteReader` and `ExecuteNonQuery`?

What are the differences between using `SqlDataAdapter` vs `SqlDataReader` for getting data from a DB?

Can you explain the difference between a `DataReader`, a `DataAdapter`, a `Dataset`, and a `DataView`?

What's better: `DataSet` or `DataReader`?

Is there anything faster than `SqlDataReader` in .NET?

Is it necessary to manually close and dispose of `SqlDataReader`?

Under what scenarios would setting `pooling=false` in an ADO.NET connection string be of value when connecting to SQL Server?