An Introduction to ADO.NET


Sams Teach Yourself ASP.NET in 21 Days, Second Edition
By Chris Payne
Table of Contents
Day 10.  Communicating with ADO.NET

ADO.NET is the next generation of ActiveX Data Objects (ADO). It's a model for data access that was built with scalability, the statelessness of the Web, and XML at its core. ADO.NET provides an interface to all OLE DB compliant data sources and lets you connect to, retrieve, manipulate, and update them. You can use it whether you're in a remote environment, using a distributed application, or using disconnected data.

In terms of ASP.NET development, ADO.NET provides the framework for accessing any type of data for use with ASP.NET pages. This allows users to view or change information stored in any kind of data warehouse, including databases, text files, and XML data stores. You should become very familiar with ADO.NET because it's important for dynamic application development. Knowing its intricacies will save you a lot of headaches down the road.


Although Microsoft has touted ADO.NET as the next evolution of ADO, and though it has some of the same objects, it's really very different than its predecessor. Whereas ADO was connection-based, ADO.NET relies on short, XML message-based interactions with data sources. This makes ADO.NET much more efficient for Internet-based applications.

A fundamental change from ADO to ADO.NET was the adoption of XML for data exchanges. XML is a text-based markup language, similar to HTML, that presents an efficient way to represent data (we'll discuss XML more later today and in Day 11, "XML and ASP.NET"). ADO.NET is intimately familiar with XML and uses it in all transactions. This allows ADO.NET to reach, exchange, and persist data stores much more easily than ADO. It also gives ADO.NET much better performance because XML data is easily converted to and from any type of data. It doesn't require the complex conversions that wasted processor time with classic ADO.

Another major change is the way ADO.NET interacts with databases. ADO requires "locking" of database resources and lengthy connections for its applications, but ADO.NET doesn't; it uses disconnected data sets (with the DataSet object), which eliminates lengthy connections and database locks. This makes ADO.NET much more scalable because users aren't in contention for database resources.

Table 10.1 summarizes the major changes from ADO to ADO.NET.

Table 10.1. Changes from ADO to ADO.NET
Data represented by:
Recordset, resembling a single table or query result DataSet, which can contain multiple tables from any data source
Data access:
Accessing rows in a RecordSet sequentially Allows complete non-sequential access of data in DataSet through collection-based hierarchy
Relationships between multiple tables:
Requires SQL JOINs and UNIONs to combine data from multiple tables into one recordset Uses DataRelation objects, which can be used to navigate between related tables
Sharing data:
Requires data to be converted to data types supported by the receiving system, which degrades performance Uses XML, so no conversions are necessary
Uses a Connection object to transmit commands to a data source's underlying constructs Uses strongly typed characteristics of XML; does not require use of data constructs; can reference everything by name
Database locks and connections resulted in contention for data resources No locks or lengthy active connections, so contentions are eliminated
Problematic because firewalls prohibit many types of requests Not a problem because XML is completely firewall-proof


XML is a very useful tool for data distribution. It's completely text-based, which means that it's easy for people to write and read, and it can be transported around the security measures put in place on the Internet.

XML stores data by providing a hierarchical representation of fields and their data. For instance, if you had a database named Users with the fields Name, UserID, and Birthdate, it would be represented in text form as

 <Users> <User>    <Name />    <UserID />    <Birthdate /> <User> </Users> 

This basic structure is known as an XML schema. (Actually, it's a bit more complicated than that, but that's beyond the scope of this book.) You can then use this schema to represent all the data in your tables:

 <Users> <User>    <name>Chris Payne</name>    <UserID>1</UserID>    <birthdate>June 27</birthdate> </User> <User>    <name>Eva Saravia</name>    <UserID>2</UserID>    <birthdate>July 15</birthdate> </User> </Users> ... ... 

This can be read by anyone with a text editor (such as Notepad), whereas the corresponding database table could only be read by someone using that particular database application or converting it to another database application. XML is an efficient, implementation-independent way of storing and sending data, which is why it has become such a phenomenon on the Web.

Thus, it's only logical for databases and their interfaces to adopt this method of communication. It makes life much easier for everyone. ADO.NET uses XML in all data exchanges and for internal representations of data. As soon as data comes out of a database, it's represented in XML and is sent wherever you need it to be. Because any application can easily understand XML (as you'll see tomorrow), this approach ensures broad compatibility for your data; your data can be sent anywhere, to any type of system, and you'll be guaranteed that that the receiver will be able to comprehend it.

The adoption of XML by ADO.NET is a big step toward delivering applications as services across the Internet, which is the ideology behind the .NET Framework. This is just touching on the basics, but in the next few days you'll see the benefits your applications will reap as you develop more distributed programs.

The ADO.NET Object Model

ADO.NET consists of two key parts: the DataSet, which was discussed thoroughly yesterday, and the managed providers. A DataSet is the representation of data that's passed between ADO.NET components, such as from a data store to an ASP.NET page. It's the mechanism that data is represented outside of the data store.


Managed providers serve as a communication layer between a DataSet and a data store. They provide all the mechanisms for connecting to, accessing, manipulating, and retrieving information from any OLE DB compliant data store, such as Microsoft Access or SQL Server.

Microsoft provides two managed providers with ADO.NET: the SQL managed provider and the OLE DB managed provider. The first is used solely for interacting with Microsoft SQL Server, and it provides all the methods for communication between the SQL Server and the DataSet. The second provider, OLE DB, mediates communication between a DataSet and any OLE DB compliant data source. Both providers offer the same basic functionality for interacting with data stores, so what's the difference?

The SQL managed provider uses a protocol called tabular data stream to talk to SQL Server. This is a very efficient way to communicate with SQL Server, and it doesn't rely on OLE DB, ADO, or ODBC. This protocol is completely managed by the CLR, so it benefits from all the features you learned about in Week 1. This is why Microsoft recommends using SQL Server data stores for use with ADO.NET and ASP.NET.


The SQL provider in ADO.NET works only with SQL Server versions 7.0 and greater. If you are using an earlier version, use the OLE DB provider instead.

The OLE DB provider, on the other hand, allows communication with all other data stores efficiently and it can even be used to access SQL Server if need be.

From a developer standpoint, there is almost no difference between the two. They are both used in the same way, have the same objects, and use the same syntax. The only real difference is what happens behind the scenes (you'll learn how to switch between the two in a moment), and that they belong in different namespaces.

Each managed provider has three components:

  • Interfaces for connecting and commanding data stores and interacting with the DataSet.

  • A data stream for fast, efficient access to data (similar to the DataSet, but faster and with less functionality).

  • Objects that connect to a database and execute low-level database system-specific commands.

Throughout the rest of the book, you'll be using the OLE DB provider because it allows access to the types of data that you'll be using. Much of the syntax is similar in both providers, so converting to the SQL provider shouldn't be difficult.


In fact, all the objects for the ADO provider are prefixed with OleDb. In most cases, simply substituting this with Sql and importing the System.Data. SqlClient namespace allows you to use the SQL provider instead of the OLE DB provider.


    Sams Teach Yourself ASP. NET in 21 Days
    Sams Teach Yourself ASP.NET in 21 Days (2nd Edition)
    ISBN: 0672324458
    EAN: 2147483647
    Year: 2003
    Pages: 307
    Authors: Chris Payne

    Similar book on Amazon © 2008-2017.
    If you may any questions please contact us: