In ADO, the in-memory representation of data is the recordset. In ADO.NET, it is the dataset. There are important differences between them.
Data Navigation and Cursors
In ADO you scan sequentially through the rows of the recordset using the ADO
MoveNext method. In ADO.NET, rows are represented as collections, so you can loop through a table as you would through any collection, or access particular rows via ordinal or primary key index. DataRelation objects maintain information about master and detail records and provide a method that allows you to get records related to the one you are working with. For example, starting from the row of the Investor table for "Nate Sun,"
you can navigate to the set of rows of the Purchase table describing his purchases.
A cursor is a database element that controls record navigation, the ability to update data, and the visibility of changes made to the database by other users. ADO.NET does not have an inherent cursor object, but instead includes data classes that provide the functionality of a traditional cursor. For example, the functionality of a forward-only, read-only cursor is available in the ADO.NET DataReader object. For more information about cursor functionality, see Data Access Technologies.
Choosing ADO.NET or ADO
Both ADO.NET and ADO are easy to program, language-independent, implemented with a small footprint, use minimal network traffic, and require few layers between the
application's front-end and the data source. Both methods provide high-performance data access.
Choosing either of these data access technologies affects an application's design, extensibility, interoperability, ease of maintenance, and many other factors. These include:
• Managed code If your application is written in managed code and built upon the common language runtime, you should use ADO.NET. If you are writing
unmanaged code in C++ (and especially if you are maintaining an existing ADO application), ADO is still a good choice.
• Data structure The ADO.NET dataset can contain one or more tables, and provides both a table-based relational view and an XML-based view. The dataset uses standard common language runtime types, which simplifies programming.
The ADO recordset is a single table, accessible only as a recordset, and does not contain relationships. An ADO recordset can be the result of a multiple table JOIN query, but it is still only a single result table. If you want multiple tables with ADO, you must have multiple Recordset objects. The ADO.NET dataset provides better functionality due to its integrated relational structure.
• Data sharing ADO.NET provides the basis for data interchange between components and across tiers: datasets can be passed over the Internet and through firewalls as XML. You can view the same set of data as relational tables within your application and as an XML data structure in some other application. The dataset provides convenient two-way transformation: from dataset tables to an XML document, and from an XML document into dataset tables.
If you use COM marshaling to transmit an ADO recordset, the target application must be programmed to use the recordset data structure. This requires more difficult programming than simply reading XML data. Alternatively, you can persist the ADO recordset as XML and more easily share the data with other applications and services.
• Scalability ADO.NET is the most scalable solution. ADO.NET is designed from the ground up to be the best data access architecture for building scalable Web applications with a low total cost of ownership. If you do not need the scalability, and are not writing in managed code, you can continue to use ADO.
• Cursor location An application can establish result sets in either of two places:
within the application process (client-side cursor) or within the data store process (server-side cursor). Client-side cursors are generally a good choice for any type of impromptu user interaction with the data. Client-side cursors are supported in ADO.NET by the DataSet object and in ADO by the ClientCursor Recordset object.
Introduction to Data Access with ADO.NET
As you develop applications using ADO.NET, you will have different requirements for working with data. In some cases, you might simply want to display data on a form. In other cases, you might need to devise a way to share information with another company.
No matter what you do with data, there are certain fundamental concepts that you should understand about the data approach in ADO.NET. You might never need to know some of the details of data handling — for example, you might never need to directly edit an XML file containing data — but it is very useful to understand the data architecture in ADO.NET, what the major data components are, and how the pieces fit together.
This introduction presents a high-level overview of these most important concepts. The topic deliberately skips over many details — for example, there is much more to datasets than what is mentioned here — in favor of simply introducing you to ideas behind data integration in ADO.NET.
ADO.NET Does Not Depend On Continuously Live Connections
In traditional client/server applications, components establish a connection to a database and keep it open while the application is running. For a variety of reasons, this approach is impractical in many applications:
• Open database connections take up valuable system resources. In most cases, databases can maintain only a small number of concurrent connections. The overhead of maintaining these connections detracts from overall application performance.
• Similarly, applications that require an open database connection are extremely difficult to scale up. An application that does not scale up well might perform acceptably with four users but will likely not do so with hundreds. ASP.NET Web applications in particular need to be easily scalable, because traffic to a Web site can go up by orders of magnitude in a very short period.
• In ASP.NET Web applications, the components are inherently disconnected from each other. The browser requests a page from the server; when the server has finished processing and sending the page, it has no further connection with the browser until the next request. Under these circumstances, maintaining open connections to a database is not viable, because there is no way to know whether the data consumer (the client) requires further data access.
• A model based on always-connected data can make it difficult and impractical to exchange data across application and organizational boundaries using a connected architecture. If two components need to share the same data, both have to be connected, or a way must be devised for the components to pass data back and forth.
Database Interactions Are Performed Using Data Commands
To perform operations in a database, you execute SQL statements or stored procedures (which include SQL statements). You use SQL statements or stored procedures to read and write rows and perform aggregate functions, such as adding or averaging. You also use SQL statements or stored procedures to create or modify tables or columns, to perform transactions, and so on.
In ADO.NET you use data commands to package a SQL statement or stored procedure.
For example, if you want to read a set of rows from the database, you create a data
command and configure it with the text of a SQL Select statement or the name of a stored procedure that fetches records.
When you want to get the rows, you do the following:
1. Open a connection.
2. Call an execute method of the command, which in turn:
a. Executes the SQL statement or stored procedure referenced by the command.
b. Then closes the connection.
The connection stays open only long enough to execute the statement or stored procedure.
When you call a command's execute method, it returns a value. Commands that update the database return the number of rows affected; other types of commands return an error code. If the command queries the database with a SELECT statement, the command can return a set of rows. You can fetch these rows using a data reader, which acts as a very fast read-only, forward-only cursor. For more information, see Retrieving Data Using the DataReader.
Components of ADO.NET
The following illustration shows the major components of an ADO.NET application.
ADO.NET Data Components
The following table summarizes the ADO.NET data components illustrated above and provides links to more information.
Component or object Dataset
Data adapter Data connection Windows Form Web Forms page BizTalk