From ADO to ADO.NET

From ADO to ADO.NET

Many of you are familiar with ADO from your work with classic Visual Basic. A main feature in ADO is the Recordset object. ADO recordsets allow enough flexibility that an application can navigate between records and apply filters and bookmarks with ease. Recordsets also provide sorting, automatic pagination, and persistence. They can even be worked with when an application is disconnected from the data source. In short, the ADO recordset provides a rich and attractive programming interface.

Recordsets can also be marshaled very efficiently across tiers or the wire in a COM environment because of their native and compact binary format, the Advanced Data Table Gram (ADTG) format. This efficiency is possible because the ADO recordset is based on COM, however, only COM objects can use ADO recordsets. In a homogeneous architecture, where COM and DCOM (Distributed COM) are used exclusively, the reliance on COM is a nonissue. The Internet, however, has many environments that use neither COM nor Windows.

ADO recordsets quickly become rather unusable in a distributed and heterogeneous environment in which varied platforms are involved. Today's Web-based systems require significant interoperability and scalability. When we need to communicate with Unix systems or mainframes, work comes to a screeching halt when we use COM objects. While ADO is powerful for manipulating data in a Windows and COM–based scenario, it has quickly lost its appeal as systems have moved in the direction of total Internet interoperability. The ADO recordset had to evolve to meet the challenges of the Internet. Enter ADO.NET.

ADO.NET from 50,000 Feet

At the heart of ADO.NET is the DataSet object, the successor to the ADO Recordset object. A data set is an in-memory resident representation of data that provides a consistent relational programming model regardless of the source of the data it contains. A DataSet object can model data logically or abstractly because, unlike the Recordset, the DataSet is not a data container that can hold only rows of data. The DataSet can actually hold multiple tables and the relationships between them.

Let's say we perform a join on two tables in Microsoft's well-known sample database, Northwind Traders. We could write an SQL query that retrieved the current products; for example:

SELECT DISTINCTROW Products.*, Categories.CategoryName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE (((Products.Discontinued)=No));

We would get back a recordset that looks like the one shown in Figure 10-1. Notice that the Supplier and Category columns contain quite a bit of repeated data, which is pretty wasteful and somewhat limiting. To retrieve information for a specific supplier or category, we would have to write code that looked for the next unique supplier or write another query looking for unique items.

Figure 10-1

This recordset contains duplicate information.

Individual Tables, Not the Join, Are in a DataSet

Unlike an ADO recordset, an ADO.NET data set would actually contain both the Products and Categories tables in memory, not just the results of the join that are shown in Figure 10-1. You would create the joins and constraints and relationships within the data set itself, after the tables were contained in memory.

Figure 10-2 depicts data flow from a data source to a data consumer using ADO.NET. First we connect to and retrieve tables from a relational database. The tables are each placed in an in-memory DataSet object using DataSetCommand objects. These DataSetCommand objects specify the tables (or a subset thereof) that we are interested in. After the DataSet is filled, the connection to the database is immediately closed. We build the relationship between the individual tables within the DataSet itself. We can then send the data, via text-based XML, to any client application.

Because data sets can be constructed entirely from XML, we can build local data sets in either a Windows or a Web-based .NET application. We might also want to work with Electronic Data Interchange (EDI) by passing the XML file to a vendor's Web service.

As you can see, the old notion of client/server doesn't apply to the .NET world. Instead we use an n-tier paradigm in which each tier accomplishes a specific task. In a Windows Forms application, we pass the data in XML to a data set within our application. Because the XML data has a schema that fully describes its contents, we can construct a data set within our application from the XML. The user can then edit or otherwise manipulate the data within the data set contained in the application. When the user wants to update the data source, an XML file is constructed from the application's DataSet object and sent back through an intranet or the Internet to the business tier. Here the DataSet object is updated, and it then updates the data source. Keep in mind that all of the data passing though an intranet or the Internet is passed in XML. Getting your mind around these concepts might take a while, but the programming that's required is actually easier than using COM objects.

Figure 10-2

ADO.NET data flow.

The biggest difference between an ADO Recordset and an ADO.NET DataSet is that the DataSet is disconnected from the data source. To use the DataSet you simply connect to a data source, execute one or more queries to get the data you want, and then immediately disconnect. The DataSet itself contains all the logic required to scroll, edit, and manipulate the data inside. When the user (program or human) is finished manipulating the data in the DataSet, the object has the built-in ability to reconnect to and update the data source. The data logic and the user interface are completely separate. In Figure 10-2, we have a data tier, a business tier, and a presentation tier. Each tier is separated from the others, which permits us to scale an application for use by thousands of users around the globe.

	As you've learned in previous chapters, the .NET Framework uses strong typing, and ADO.NET is no exception. Typed programming is easier to read, of course, but it is also easier to use in a program. For example, with ADO.NET we can now write code like this: DataSet.Employee("Smith").EmployeeID Contrast this statement with earlier syntax, such as this: Table("Employee")("Smith").Column("EmployeeID")

Comparing Classic ADO and ADO.NET

A good way to understand the advantages of ADO.NET over classic ADO as used in Visual Basic 6 is to compare their features. One glance at the information in Table 10-1 shows you that ADO.NET is an improvement over ADO.

Table 10-1  A Comparison of ADO and ADO.NET Features

Feature	ADO	ADO.NET
Memory-resident data	Uses the Recordset object, which looks like a single table.	Uses the DataSet object, which can contain one or more tables represented by DataTable objects.
Relationships between multiple tables	Requires the JOIN query to assemble data from multiple database tables in a single result table.	Supports the DataRelation object to associate rows in one Data Table object with rows in another DataTable object.
Accessing data	Scans Recordset rows sequentially.	Permits nonsequential access to rows in a table. Follows relationships to navigate from rows in one table to corresponding rows in another table.
Disconnected access	Provided by the Recordset object, but generally supports connected access represented by the Connection object. Communicates with a database with calls to an OLE DB provider.	Communicates to a database with standardized calls to a DataSetCommand object.
Programmability	Uses the Connection object to transmit commands.	Uses the strictly typed programming characteristic of XML. Data is self-describing. Underlying data constructs such as tables and rows do not appear in the XML, making code easier to read and to write.
Sharing disconnected data between tiers or components	Uses COM marshaling to transmit a disconnected recordset. Supports only those data types defined by the COM standard.	Transmits a DataSet with an XML file. The XML format places no restrictions on data types and requires no type conversions.
Transmitting data through firewalls	Problematic because firewalls are typically configured to prevent system-level requests such as COM marshaling of binary objects.	No problem because the ADO.NET DataSet object uses text-based XML, which can pass through firewalls.
Scalability	Database locks and active database connections for long durations contend for limited database resources.	Disconnected access to database data limits contention for limited database resources.

Another significant difference between the Connection object in ADO and ADO.NET is that the ADO.NET Connection object does not have a CursorLocation property. The absence of this property is not a bug but a database design issue. ADO.NET has no explicit implementation of cursors, but of course they are there. The implementation of cursors in ADO.NET is unlike classic ADO, in which you had to set cursors on either the server or the client. While the more sophisticated DataSet object looks like a static cursor, the DataReader object is the equivalent of the ADO read-only cursor. In fact, ADO.NET does not have explicit support for server-side cursors. One exception, however, is the Data Reader object, which simply performs a record-by-record read. If you need to perform data manipulation on the server, classic ADO is still the way to go.

An application can establish its result sets either within the application process (client side) or within the data source (server side). Client-side cursors are supported in ADO.NET by the DataSet object. These cursors permit the management of data, such as scrolling and updating. Client-side cursors are generally a good choice for any type of impromptu user interaction with the data because a round trip to the server is not necessary for each action.

Server-side cursors are supported in ADO.NET by the DataReader object. Server-side cursors should be used cautiously, however, because nonsequential scrolling and the updating of results through a server-side cursor might hold locks and cause resource contention that greatly limits the scalability of an application. Instead of using a scrollable, updateable server-side cursor, an application can usually benefit from using stored procedures for procedural processing of results on the server.

You should also consider using a scrollable, updateable server-side cursor in either classic ADO or OLE DB in either of the following two situations.

• Your data source does not support stored procedures.

• The result set is too large for a client-side cursor and you expect relatively few concurrent users for a particular set of data.