ADO vs. ADO.NET

I l @ ve RuBoard

The History of Microsoft Data Access

You guessed it ”Microsoft has changed the data access object model again. Before we jump right into the "how" of ADO.NET, we should spend a moment on the "why."

A long, long time ago (say four years ), there were several object models for accessing data. If you wanted to talk to Access, you used Data Access Objects (DAO). Through DAO, you could take advantage of many Access-specific features. For example, you could create, repair, and compact databases. You could attach tables. You could create QueryDefs. For talking to Access, DAO was great, but Access wasn't the only database you ever wanted to talk to. What about Oracle? What about SQL Server?

The solution was Remote Data Objects (RDO). RDO was essentially for everything other than Access. RDO would let you talk to any Open Database Connectivity (ODBC) data source, so through it, you could access SQL, Oracle, Informix, Sybase, and others. You could execute stored procedures. You could even create something as exotic as a "disconnected recordset."

See any problems? All you want to do is talk to the database, and for what is logically the same operation, you have to use two different APIs. And going from DAO and Access to RDO and SQL was a significant porting operation. Even DAO and RDO weren't enough for everything. There were nontraditional data stores that had their own API. For example, Index Server and Exchange have custom APIs to access their data.

Along came ADO and OLEDB. During 1998, ADO really started to take off with Microsoft's marketing of "Universal Data Access." Finally, only one API existed for talking to any data source. At least, that was the claim. ADO was supposed to combine the best of DAO and RDO. It didn't really. With ADO, you lost the ability to compact your Access database. And, in many instances, it wasn't as fast as RDO. But, ADO did have some useful features. You could create hierarchical recordsets. It did let you access more types of data. You didn't have to go through ODBC because there were "native providers" for most databases.

But ADO had one fatal flaw. It was built around the concept of connectedness. ADO assumed that you would want to connect to the data source, and stay connected while you retrieved data and performed operations on it. You could create a disconnected recordset, but it wasn't the default. The world of development, on the other hand, was moving in a different direction. With n- tier architectures, the demand was for disconnected data. XML was going through a technological big bang. Microsoft.NET had a different vision of the world, and ADO just didn't fit. ADO.NET was born.

ADO.NET is all about disconnected access. In general, you connect to the database only for the instant when you are retrieving or updating. Otherwise, the connection is closed. This is the default. When you retrieve the data, it is typically stored in something known as a " DataSet ". You can think of the DataSet as a recordset on steroids. For starters, a single DataSet can store the results of many SQL queries. For example, you could retrieve all the authors, and store the results in a DataSet . You could then retrieve all the publishers, and store the results in the same DataSet . With another line of code, you can tell the DataSet that the authors and publishers are related on the PubId column (see Figure 4.1). In other words, the DataSet can act like a limited in-memory database, with a full understanding of the relationship between tables.

Figure 4.1. Multiple tables in a DataSet .

And again, this in-memory database is designed to not require an active connection to the actual database.

There's another fundamental difference between ADO and ADO.NET. With ADO, the Recordset was all-powerful. It not only allowed access to the data, but it provided the facilities to filter and sort the data. The Recordset , in other words, controlled the data and how you viewed it. ADO.NET divides this functionality between two objects. The DataSet is just concerned with storing the data. For your actual view of the data, which includes filtering and sorting, you use a DataView object.

As stated earlier, the DataSet keeps an in-memory copy of the data returned by a select statement. Often, this is exactly what you want. However, for very large DataSet s, this isn't practical. For example, what if you wanted to do some massive import/export operations on your database? You're certainly not going to load the entire database into memory. There's an object for just such scenarios. It's called the DataReader .

The DataReader serves the same purpose as an ADO forward-only, read-only recordset. The DataReader allows you to iterate through a set of rows, one at a time. Also, for many scenarios, iterating through the result set a single time is all the functionality you need. For example, think of the times you've looped through a RecordSet to generate an HTML drop-down list. A DataReader provides all the functionality you need for this type of operation.

ADO vs. ADO.NET Object Model

Tables 4.1 and 4.2 compare the objects in ADO with the new objects in ADO.NET.

Table 4.1. ADO Objects
Object Description
Connection Allows your code to connect to a data source. Through the connection, you can issue commands that update the database, or return records.
Command Used to send commands to the database. The command object was specifically useful for calling stored procedures because it contained a Parameters collection that you could use to access the input and output parameters of a stored procedure.
Recordset Stores the results of a select statement. By default, the recordset was read-only, and used a server-side cursor. You could create server- or client-side cursors , as well as specify a number of locking options.

ADO was referred to as a flat object model, meaning that a lot of duplicate functionality was available through different objects. The connection object would connect to the database, but you could also issue commands through it directly. The recordset was used to store results, but it could maintain its own internal connection, and issue database commands. Although this could be convenient , in practice, it resulted in confusion and complexity.

In ADO.NET, they didn't try to make every object do everything (see Table 4.2). A connection is just a connection. You can't issue commands through it like you could in ADO. A command is only for issuing commands. A DataSet is only for storing data. With an ADO recordset, you got a lot of different and inconsistent behavior depending on the underlying database. With the DataSet , you get exactly the same behavior across all databases because it has no knowledge of the underlying database.

Table 4.2. ADO.NET Objects
Object Description
DBConnection , SqlConnection , ADOConnection Used to establish a connection to the database
DBCommand , SqlCommand , ADOCommand Used to send INSERT , UPDATE , SELECT , and DELETE statements to the database
DataSet Stores the results of one or more SELECT statements
DataView Used to filter and sort a DataSet
DBDataReader , SQLDataReader , ADODataReader Used to iterate through a set of records

Let's take a look at these objects in action.

I l @ ve RuBoard


Asp. Net. By Example
ASP.NET by Example
ISBN: 0789725622
EAN: 2147483647
Year: 2001
Pages: 154

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net