……………………………………………….Expertise in .NET Technologies

  • Categories

  • Advertisements

Important Questions on ADO.NET – Part I

Posted by Ravi Varma Thumati on October 2, 2009

What is ADO.NET?

ADO.NET is a set of classes that expose data access services to the .NET Applications. ADO(or ActiveX Data Objects ) is an easy-to-use yet extensible technology for adding database connectivity to Web pages. ADO.NET is the next generation of ADO, providing the interface for accessing, manipulating, and exchanging data in the Microsoft .NET Framework.

The ADO.NET components have been designed to factor data access from data manipulation. There are two central components of ADO.NET that accomplish this: the DataSet, and the .NET Framework data provider, which is a set of components including the Connection, Command, DataReader, and DataAdapter objects.

What are the main classes/Components of ADO.NET?

ADO.NET is comprised of many classes, but five take center stage:

Represents a connection to a data source.

Represents a query or a command that is to be executed by a data source.

Represents data. The DataSet can be filled either from a data source (using a DataAdapter object) or dynamically.


Used for filling a DataSet from a data source.


Used for fast, efficient, forward-only reading of a data source.

Explain How ADO.NET Works?

ADO.NET uses two types of objects to access the data in a database: datasets, which can contain one or more data tables, and .NET data provider objects, which include data adapters, commands, and connections.

A dataset stores data from the database so that it can be accessed by the application. The .NET data provider objects retrieve data from and update data in the database.

To retrieve data from a database and store it in a data table, a data adapter object issues a Select statement that is stored in a command object. Next, the command object uses a connection object to connect to the database and retrieve the data. Then, the data is passed back to the data adapter, which stores the data in the dataset.

To update the data in a database based on the data in a data table, the data adapter object issues an Insert, Update, or Delete statement that is stored in a command object. Then, the command object uses a connection to connect to the database and update the data.

The data provider remains connected to the database only long enough to retrieve or update the specified data. Then, it disconnects from the database and the application works with the data via the dataset object. This is referred to as disconnected data architecture.

All of the ADO.NET objects are implemented by classes in the System.Data namespace of the .NET Framework. However, the specific classes used to implement the connection, command, and data adapter objects depend on the .NET data provider you use.

What is DataSet?

A DataSet is a cache of records retrieved from a data source. It works like a virtual data store: A dataset includes one or more tables based on the tables in the actual database, and it can include information about the relationships between those tables and constraints on what data the tables can contain.

The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. The DataSet represents a complete set of data, including related tables, constraints, and relationships among the tables.

The DataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. The DataSet class is explicitly designed for data access independent of any data source. As a result it can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint, and relation information about the data in the DataTable objects.

What are the fundamental parts of DataSet?

The fundamental parts of a dataset are exposed to you through standard programming constructs such as properties and collections. For example:

The DataSet class includes the Tables collection of data tables and the Relations collection of DataRelation objects.

The DataTable class includes the Rows collection of table rows, the Columns collection of data columns, and the ChildRelations and ParentRelations collections of data relations.

The DataRow class includes the RowState property, whose values indicate whether and how the row has been changed since the data table was first loaded from the database. Possible values for the RowState property include Deleted, Modified, New, and Unchanged.

What is the use of DataAdapter Class?

DataAdapter Class represents a set of SQL commands and a database connection that are used to fill the DataSet and update the data source. The DataAdapter serves as a bridge between a DataSet and a data source for retrieving and saving data.

Adapters are used to exchange data between a data source and a dataset. In many applications, this means reading data from a database into a dataset, and then writing changed data from the dataset back to the database. You can use a data adapter to perform the following operations:

  • Retrieve rows from a data store into corresponding data tables within the dataset.
  • To retrieve rows into a dataset, use the Fill method on a data adapter object (SqlDataAdapter, OleDbDataAdapter, OdbcDataAdapter, or OracleDataAdapter). When you invoke the Fill method, it transmits an SQL SELECT statement to the data store.
  • Transmit changes made to a dataset table to the corresponding data store.
  • To transmit a dataset table of the dataset to the data store, use the adapter’s Update method. When you invoke the method, it executes whatever SQL INSERT, UPDATE or DELETE statements are needed, depending on whether the affected record is new, changed, or deleted.

 Which DataAdapters are available for use with different Databases in ADO.NET?

ADO.NET makes these data adapters available for use with databases:

  • The OleDbDataAdapter object is suitable for use with any data source exposed by an OLE DB provider.
  • The SqlDataAdapter object is specific to SQL Server. Because it does not have to go through an OLE DB layer, it is faster than the OleDbDataAdapter. However, it can only be used with SQL Server 7.0 or later.
  • The OdbcDataAdapter object is optimized for accessing ODBC data sources.
  • The OracleDataAdapter object is optimized for accessing Oracle databases.

How can you update Database using DataAdapter and the DataSet?

Updating the Database with a DataAdapter and the DataSet

The Update method of the DataAdapter is called to resolve changes from a DataSet back to the data source. The Update method, like the Fill method, takes as arguments an instance of a DataSet, and an optional DataTable object or DataTable name. The DataSet instance is the DataSet that contains the changes that have been made, and the DataTable identifies the table from which to retrieve the changes.

When you call the Update method, the DataAdapter analyzes the changes that have been made and executes the appropriate command (INSERT, UPDATE, or DELETE). When the DataAdapter encounters a change to a DataRow, it uses the InsertCommand, UpdateCommand, or DeleteCommand to process the change.

The Update method will resolve your changes back to the data source, however other clients may have modified data at the data source since the last time you filled the DataSet. To refresh your DataSet with current data, use the DataAdapter and Fill the DataSet again. New rows will be added to the table, and updated information will be incorporated into existing rows. The Fill method determines whether a new row will be added or an existing row will be updated by examining the primary key values of the rows in the DataSet and the rows returned by the SelectCommand. If the Fill method encounters a primary key value for a row in the DataSet that matches a primary key value from a row in the results returned by the SelectCommand, it updates the existing row with the information from the row returned by the SelectCommand and sets the RowState of the existing row to Unchanged. If a row returned by the SelectCommand has a primary key value that does not match any of the primary key values of the rows in the DataSet, the Fill method adds a new row with a RowState of Unchanged.

Explain the Use of connection pooling in ADO.NET

Connection pooling enables an application to use a connection from a pool of connections that do not need to be re-established for each use. Once a connection has been created and placed in a connection pool, an application can reuse that connection without performing the complete connection creation process.

By default, the connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size. Additional connections can be added until the pool reaches the maximum pool size.

When a user request a connection, it is returned from the pool rather than establishing new connection and, when a user releases a connection, it is returned to the pool rather than being released. But be sure than your connections use the same connection string each time. Here is the Syntax

conn.ConnectionString = “integrated Security=SSPI; SERVER=; DATABASE=MY_DB; Min Pool Size=4;Max Pool Size=40;Connect Timeout=14;”;

How can you search the Data in DataSet?

Searching for Data in the DataSet

When querying a DataSet for rows that match particular criteria, you can increase the performance of your searches by taking advantage of index-based lookups. When you assign a PrimaryKey value to a DataTable, an index is created. When you create a DataView for a DataTable, an index is also created. Here are a few tips for taking advantage of index-based lookups.

If the query is against the columns that make up the PrimaryKey of the DataTable, use DataTable.Rows.Find instead of DataTable.Select. For queries involving non-primary key columns, you can improve performance for multiple queries of the data using a DataView. When you apply a sort order to a DataView, an index is built that is used when searching. The DataView exposes the Find and FindRows methods to query the data in the underlying DataTable. If you do not require a sorted view of a table, you can still take advantage of index-based lookups by creating a DataView for the DataTable. Note that this is only an advantage if you are performing multiple queries on the data. If you are only performing a single query, the processing required to create the index reduces the performance gained by using the index.



One Response to “Important Questions on ADO.NET – Part I”

  1. I’m still studying from you, but I’m enhancing myself. I actually love studying all the pieces that’s written on your blog.Keep the stories coming. I loved it!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: