ADO.NET Interview Questions and Answers

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.

It depends on your needs. One of the most important differences is that a DataReader will retain an open connection to your database until you're done with it while a DataSet will be an in-memory object. If you bind a control to a DataReader then it's still open. In addition, a DataReader is a forward only approach to reading data that can't be manipulated. With a DataSet you can move back and forth and manipulate the data as you see fit. Some additional features: DataSets can be serialized and represented in XML and, therefore, easily passed around to other tiers. DataReaders can't be serialized. On the other hand if you have a large amount of rows to read from the database that you hand off to some process for a business rule a DataReader may make more sense rather than loading a DataSet with all the rows, taking up memory and possibly affecting scalability.

Try to avoid using readers like this:

SqlConnection connection = new SqlConnection("connection string");
SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection);
SqlDataReader reader = cmd.ExecuteReader();
connection.Open();
if (reader != null)
{
  while (reader.Read())
  {
    //do something
  }
}
reader.Close();
// <- too easy to forget
reader.Dispose();
// <- too easy to forget
connection.Close();
// <- too easy to forget

The using statement will ensure correct disposal of the object and freeing of resources. If you forget then you are leaving the cleaning up to the garbage collector, which could take a while. Instead consider:

using(SqlConnection connection = new SqlConnection("connection string"))
{
  connection.Open();
  using(SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection))
  {

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
      if (reader != null)
      {
        while (reader.Read())
        {
          //do something
        }
    }
}
// reader closed and disposed up here
}
// command disposed here
}
//connection closed and disposed here

About the fastest access you will get to SQL is with the SqlDataReader . If SqlDataReader isn't fast enough, perhaps you should store your stuff somewhere else, such as an (in-memory) cache or rewrite it as T-SQL.

I would choose disconnected Data Access i.e. Dataset for below points. If a row set is intended to be bound to more than one read-only ASP.NET server control, you should consider using a Dataset instead. If a Data Reader was bound to more than one control (such as three Dropdown List controls), the same query would hit the database three times since the Data Reader can only move forward. The Dataset also works well when a row set must be persisted between page calls to the Session or Cache objects. Another good use of the Dataset is the situation in which data must be retrieved and a complex action performed on each row. For example, an application might retrieve a hundred stock and mutual fund symbols from a 401k table that needs to be edited. This data might have to include the stock and mutual fund prices on screen, as well. A Dataset could be used to store the row set and some code could loop through the Dataset and perform a lookup of each stock's price through a third-party Web service. Finally, one of the more compelling reasons to use a DataSet instead of a DataReader is that the DataSet can be serialized when the rowset needs to be passed around a network or the Internet. A Data Reader cannot be serialized to XML due to its connected nature.

a DataReader is a forward-only iterator over a set of results. It's usually the most efficient way to deal with records when you don't need random access (in other words you can't go backwards). It is "scalable" to any number of records, at least in terms of memory pressure, since it only loads one record at a time. One typical way to get a DataReader is by using the ExecuteReader method of a DbCommand.

a DataSet represents a set of DataTable objects. More often than not, it will just contain one table, but if you do a query with multiple SELECT statements, the DataSet will contain a table for each. Because this is an in- memory representation, you have to be careful about how much data you pull into a DataSet. You can "Fill" a DataSet using the Fill method of a DataAdapter. a DataAdapter is a kind of "pipe" that funnels data from a DB engine into a DataSet. That's why you'll have one DataAdapter implementation for each DB provider type. One DataSet, many providers. a DataView is like a virtual subset of a DataTable.

You can monitor connection pooling behavior by executing the sp_who or sp_who2 stored procedures in SQL Server. You can also use the SQL Server Profiler or take advantage of Performance Counters to monitor connection pooling behavior.

ADO is a COM-based library for accessing databases. OleDB and ODBC are standards for communicating with databases. ADO uses the OleDB to talk to any database that exposes an OleDB driver. There is also an OleDB driver that can wrap any ODBC driver. Thus ADO can also talk to any database that exposes an ODBC driver.

ADO.NET (a.k.a. System.Data) is a .NET based library for accessing databases.

ADO.NET has built-in support for SQL Server, OleDB, and ODBC Third parties can expose their database to ADO.NET by building a ADO.NET compatible library Third parties can also expose their database to ADO.NET by offering an OleDB or ODBC driver

ODBC provides access only to relational databases OLE DB provides the following features Access to data regardless of its format or location Full access to ODBC data sources and ODBC drivers

A transaction is considered to be a local transaction when it is a single-phase transaction and is handled by the database directly. A transaction is considered to be a distributed transaction when it is coordinated by a transaction monitor and uses fail-safe mechanisms (such as two-phase commit) for transaction resolution. Essentially a distributed transaction is a transaction that affects several resources. Transactions are most efficient when they are performed on the server. If you are working with a SQL Server database that makes extensive use of explicit transactions, consider writing them as stored procedures using the Transact-SQL BEGIN TRANSACTION statement.

Very often, you will find SQL queries scattered in the codebase and when you come to add a column to a table you have to search code files to try and find usages of a table. The impact of the change is far-reaching. With the repository pattern, you would only need to change one object and one repository. The impact is very small. Basically, repository hides the details of how exactly the data is being fetched/persisted from/to the database. Under the covers: for reading, it creates the query satisfying the supplied criteria and returns the result set

for writing, it issues the commands necessary to make the underlying persistence engine (e.g. an SQL database) save the data

I would choose connected Data Access i.e Data Reader for below points. Data Readers are a good choice when an application has to be sensitive to changes in the underlying database The connected Approach (Data Reader) is a better choice for applications that require optimized read-only and forward-only access to data such as binding to a Data Grid control. The sooner the data is off-loaded from the Data Reader and the connection is closed the better the application performance. A Data Reader can be the right choice, such as when populating a list or retrieving 10,000 records for a business rule. When a huge amount of data must be retrieved to a business process, even on a middle tier, it can take a while to load a DataSet, pass the data to it on the business tier from the database, and then store it in memory. The footprint could be quite large and with numerous instances of it running (such as in a Web application where hundreds of users may be connected), scalability would become a problem. If this data is intended to be retrieved and then traversed for business rule processing, the DataReader could speed up the process as it retrieves one row at a time and does not require the memory resources that the DataSet requires.

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 .

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.

A DataView enables you to create different views of the data stored in a DataTable , a capability that is often used in data binding applications. Using a DataView , you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression. A DataView provides a dynamic view of data whose content, ordering, and membership reflect changes to the underlying DataTable as they occur. This is different from the Select method of the DataTable , which returns a DataRow array from a table per particular filter and/or sort order and whose content reflects changes to the underlying table, but whose membership and ordering remain static. The dynamic capabilities of the DataView make it ideal for data-binding.

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.

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.

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:

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:
foreach (Customer objCust in obj.Customers)
{}

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

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.

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.

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.

You can try with SqlBulkCopy class. Another option is using bcp (Bulk Copy Program) utility. Consider:

// connect to SQL
using (SqlConnection connection =
new SqlConnection(connString))
{
  // make sure to enable triggers
  SqlBulkCopy bulkCopy =
  new SqlBulkCopy
  (
  connection,
  SqlBulkCopyOptions.TableLock |
  SqlBulkCopyOptions.FireTriggers |
  SqlBulkCopyOptions.UseInternalTransaction,
  null
);// set the destination table name
bulkCopy.DestinationTableName = this.tableName;
connection.Open();// write the data in the "dataTable"
bulkCopy.WriteToServer(dataTable);
connection.Close();
}
// reset
this.dataTable.Clear();

Pool fragmentation is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open and consuming memory, which results in poor performance. Note that pool fragmentation might occur if you are using Integrated Security or even when your application is using too many connection pools. In order to ensure that connection pooling works efficiently, you should ensure that you close the database connections when they are no longer needed. You should never call the Close or Dispose method on a Connection instance in the Finalize method. You should also close the transaction instances before the related connection objects are closed or disposed.

As an application developer, you almost always want to keep connection pooling enabled. The only practical reason I can think of to disable connection pooling is when debugging connection pooling issues. For example, if your application is getting a lot of timeout exceptions under load, you may need to disable pooling temporarily as part of the root cause analysis. At a higher level, connection pooling provides an abstraction over managing the database connection, but the ability to disable it provides more flexibility in scenarios that may apply to framework or library developers.

SqlDataReader: Holds the connection open until you are finished (don't forget to close it or use using). Can typically only be iterated over once Is not as useful for updating back to the database

On the other hand, it: Only has one record in memory at a time rather than an entire result set (this can be huge) Is about as fast as you can get for that one iteration Allows you start processing results sooner (once the first record is available) SqlDataAdapter/DataSet: Lets you close the connection as soon it's done loading data, and may even close it for you automatically All of the results are available in memory You can iterate over it as many times as you need, or even look up a specific record by index Has some built-in faculties for updating back to the database At the cost of: Much higher memory use You wait until all the data is loaded before using any of it So really it depends on what you're doing, but I tend to prefer a DataReader until I need something that's only supported by a dataset. SqlDataReader is perfect for the common data access case of binding to a read- only grid.

You can manage connection pools using certain keywords in your connection string. The important ones include the following: ConnectionTimeout -- this is used to specify the wait period (in seconds) when a new database connection is requested for. The default value is 15. MinPoolSize -- this represents the minimum number of connections in the pool. MaxPoolSize -- this represents the maximum number of connections in the pool. The default value is 100. Pooling -- this controls if connection pooling is turned on or off and can have a value of true of false. When this is set to true, the requested connection is retrieved from the appropriate connection pool.

To connect to the database server is recommended to use Windows Authentication, commonly known asintegrated security. To specify the Windows authentication, you can use any of the following two key-value pairs with the data provider. NET Framework for SQL Server:

Integrated Security = true;
Integrated Security = SSPI;

However, only the second works with the data provider .NET Framework OleDb. If you set Integrated Security = true for ConnectionString an exception is thrown.

Connection Oriented means : connection is exist throw out your process. Example : using DataReader in ADO.NET you can get data as connection oriented database connection type. Connection Less means : Your connection is not available throw out your whole process. Example: using DataAdapter in ADO.NET you can get data as connection less database connection type.

A DataTable is a collection of DataRow s whose DataColumn s satisfy a particular schema and may be subject to certain Constraint s. As such, it is normally used as an 'in memory' representation of the rows, columns and constraints of a relational database table. A DataSet is a collection of DataTable s and DataRelation s between them. As such, it can be used to represent all the tables within a relational database file and the parent/child relationships between them. A DataView is a customized view of a DataTable for sorting, filtering, searching, editing, and navigation. It corresponds to a view (i.e. a virtual table produced by a query) in a relational database.

ExecuteScalar is typically used when your query returns a single value. If it returns more, then the result is the first column of the first row. An example might be SELECT @@IDENTITY AS 'Identity' . ExecuteReader is used for any result set with multiple rows/columns (e.g., SELECT col1, col2 from sometable ). ExecuteNonQuery is typically used for SQL statements without results (e.g., UPDATE, INSERT, etc.).

Unit of Work is referred to as a single transaction that involves multiple operations of insert/update/delete and so on kinds. To say it in simple words, it means that for a specific user action (say registration on a website), all the transactions like insert/update/delete and so on are done in one single transaction, rather than doing multiple database transactions.

In NET, we have data-set while ADO we have record-set In record-set we can only have one table and to insert more than one table you have to do inner join. While the dataset in ADO.NET can have multiple tables In NET, all data persist in XML while in classic ADO the data persists in binary format also