Focus on Microsoft Technologies - Tutorials, Articles, Code Samples.

Wednesday, August 30, 2006

Introduction to ADO.NET



ADO.NET provides consistent access to data sources such as Microsoft SQL Server, as well as data sources exposed through OLE DB and XML.


ADO.NET includes .NET Framework data providers for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, or placed in an ADO.NET DataSet object in order to be exposed to the user in an ad-hoc manner, combined with data from multiple sources, or remoted between tiers. ADO.NET DataSet object can also be used independently of a .NET Framework data provider to manage data local to the application or sourced from XML.


The ADO.NET classes are found in System.Data.dll, and are integrated with the XML classes found in System.Xml.dll. When compiling code that uses the System.Data namespace, reference both System.Data.dll and System.Xml.dll.


ADO.NET Components

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.

The following table outlines the four core objects that make up a .NET Framework data provider.




Establishes a connection to a specific data source.


Executes a command against a data source. Exposes Parameters and can execute within the scope of a Transaction from a Connection.


Reads a forward-only, read-only stream of data from a data source.


Populates a DataSet and resolves updates with the data source.


The following table shows the providers have been tested with ADO.NET.




Microsoft OLE DB Provider for SQL Server


Microsoft OLE DB Provider for Oracle


OLE DB Provider for Microsoft Jet





The DataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. 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 represents a complete set of data including related tables, constraints, and relationships among the tables.


The difference between the two lies in the fact that a Typed DataSet has a schema and an Untyped DataSet does not have one.


string sSQL = "SELECT * FROM Products";

string sConnString =

    "Server=(local);Database=Northwind;Integrated Security=SSPI;";

using (SqlConnection oCn = new SqlConnection(sConnString))


    SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);

    oSelCmd.CommandType = CommandType.Text;


    SqlDataReader oDr = oSelCmd.ExecuteReader();

    DataGrid1.DataSource = oDr;





string sSQL = "SELECT * FROM Products";

string sConnString =

    "Server=(local);Database=Northwind;Integrated Security=SSPI;";

SqlDataAdapter oDa = new SqlDataAdapter();

DataSet oDs = new DataSet();

using(SqlConnection oCn = new SqlConnection(sConnString))


    SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);

    oSelCmd.CommandType = CommandType.Text;

    oDa.SelectCommand = oSelCmd;

    oDa.Fill(oDs, "Products");




Difference between ADO &


ADO and ADO.NET are different in several ways:


·         ADO works with connected data. This means that when you access data, such as viewing and updating data, it is real-time, with a connection being used all the time. This is barring, of course, you programming special routines to pull all your data into temporary tables.


ADO.NET uses data in a disconnected fashion. When you access data, ADO.NET makes a copy of the data using XML. ADO.NET only holds the connection open long enough to either pull down the data or to make any requested updates. This makes ADO.NET efficient to use for Web applications. It's also decent for desktop applications.


·         ADO has one main object that is used to reference data, called the Recordset object. This object basically gives you a single table view of your data, although you can join tables to create a new set of records. With ADO.NET, you have various objects that allow you to access data in various ways. The DataSet object will actually allow you to store the relational model of your database. This allows you to pull up customers and their orders, accessing/updating the data in each related table individually.


·         ADO allows you to create client-side cursors only, whereas ADO.NET gives you the choice of either using client-side or server-side cursors. In ADO.NET, classes actually handle the work of cursors. This allows the developer to decide which is best. For Internet development, this is crucial in creating efficient applications.


·         Whereas ADO allows you to persist records in XML format, ADO.NET allows you to manipulate your data using XML as the primary means. This is nice when you are working with other business applications and also helps when you are working with firewalls because data is passed as HTML and XML.



Difference between Dataset and DataReader


The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables.


ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Using the DataReader can increase application performance and reduce system overhead because only one row at a time is ever in memory.





A DataReader is specific to a data provider (for example, SqlDataReader, OdbcDataReader, and OleDbDataReader).

The DataSet class isn’t a part of any data provider. It’s specific to .NET only. However, the DataAdapter used to fill the DataSet with Fill() is specific to a data provider.

The data retrieved through a DataReader is read-only.

The data retrieved through a DataSet is read-write.

The data retrieved through a DataReader is forward-only. Once the data has been cycled through, the DataReader must be closed and re-created in order to reaccess the data.

You can work with data in a DataSet in any order you choose as many times as you like.

A DataReader presents data through a direct connection to the data source. Only one row of data is stored in Internet Information Services (IIS) memory at any one time.

A DataSet stores all the data from the data source in IIS memory at once.

A DataReader is fast.

A DataSet is slower than a DataReader.

A DataReader takes up few IIS and memory resources but annexes the databaseconnection until it’s closed.

A DataSet takes up a lot more memory/IIS resources to store all the data, but it doesn’t hold up a database connection until it’s closed. The connection needs to be open only when Fill() is called.

A DataReader lasts as long as the connection to the database is open. It can’t be persisted in a cookie or a session variable.

A DataSet lasts only until the page is reloaded (posted back) unless it’s somehow persisted (for example, in a session variable).

Fields in a DataReader are referenced by index number or name.

You can reference fields in a DataSet by name, but you must also name the DataTable and identify the row (index) that contains the field.

A DataReader has no concept of primary keys, constraints, views, or any other relational database management system (RDBMS) concept except row and field.

A DataSet contains DataTables. A primary key may be set for each DataTable, and relationships and constraints may be established between them. DataViews may be created over the DataSet.

You can’t update a data source through a DataReader.

You can make changes to data in a DataSet and then upload those changes back to the data source.

A DataReader connects to only onedata source.

A DataSet can be filled with Fill() from multiple data sources.



Choosing a DataReader or a DataSet:


Use a DataSet to do the following:


Remote data between tiers or from an XML Web service.  Interact with data dynamically such as binding to a Windows Forms control or combining and relating data from multiple sources. Cache data locally in your application. Provide a hierarchical XML view of relational data and use tools like an XSL Transformation or an XML Path Language (XPath) Query on your data.

For more information, see XML and the DataSet. Perform extensive processing on data without requiring an open connection to the data source, which frees the connection to be used by other clients.


If you do not require the functionality provided by the DataSet. You can improve the performance of your application by using the DataReader to return your data in a forward-only read-only fashion. Although the DataAdapter uses the DataReader to fill the contents of a DataSet (see Populating a DataSet from a DataAdapter), by using the DataReader you can receive performance gains because you will save memory that would be consumed by the DataSet, as well as saving the processing required to create and fill the contents of the DataSet.


An "untyped" dataset is an instance of the DataSet class from the System.Data namespace. Its called untyped because all columns are provided as the base System.Object type (object in C# and Object in VB.NET) and must be coerce to the appropriate type, e.g.


The problem, of course, is that its a pain to get the coercion code is tedious to write and easy to get wrong.















Post a Comment