Data Access Technologies
Most applications require some form of data access. If you are creating a new application, you have three excellent data access choices: ADO.NET, ADO, and OLE DB. If you need to modify the data access for an existing application, you might continue using the application's current data access technology for maintenance convenience. However, if you expect the application to have a long lifecycle, you should consider reengineering to use either ADO.NET for managed applications or ADO for native applications. In the long run, the newer data access technologies typically reduce development time, simplify code, and provide excellent performance.
ADO.NET is the strategic application-level interface for providing data access services in the Microsoft .NET Platform. You can use ADO.NET to access data sources using the new .NET Framework data providers. These data providers include:
- .NET Framework Data Provider for SQL Server.
- .NET Framework Data Provider for OLE DB.
- .NET Framework Data Provider for ODBC.
- .NET Framework Data Provider for Oracle.
These data providers support a variety of development needs, including middle-tier business objects using live connections to data in relational databases and other stores.
ADO.NET is designed specifically for message-based Web applications while still providing preferable functionality for other application architectures. By supporting loosely coupled access to data, ADO.NET maximizes data sharing by reducing the number of active connections to the database — reducing the possibility of multiple users contending for limited resources on the database server.
ADO.NET provides several ways to access data. If your Web application or XML Web service requires data access from multiple sources, needs to interoperate with other applications (both local and remote), or can benefit from persisting and transmitting cached results, the dataset is an excellent choice. As an alternative, ADO.NET provides data commands and data readers to communicate directly with the data source. Direct database operations using data commands and data readers include running queries and stored procedures, creating database objects, and performing direct updates and deletes using DDL commands.
ADO.NET maximizes data sharing by supporting an XML-based persistence and transmission format for the fundamental object of distributed ADO.NET applications: the dataset. A dataset is a relational data structure that can be read from, written to, or serialized using XML. ADO.NET datasets make it easy to build applications that require loosely coupled data interchange between application tiers and multiple Web sites.
Because datasets are remoted as XML, any two components can share data and use XML schemas to define the relational structure of the dataset. And, because the dataset's serialization format is XML, DataSet objects can easily pass through firewalls without restrictions. In addition to loading data from XML, datasets can be populated with, and hold changes to, data from SQL Server as well as data sources exposed via OLE DB.
A primary characteristic of a dataset is that you can access and manipulate the data inside a local dataset two ways:
- As tables in a relational database A dataset can contain one table or a collection of tables. An important aspect of the dataset is that it keeps track of the relationships between the tables it contains — as if it is an in-memory relational data store.
- As XML (eXtended Markup Language) structures Data within a dataset can also be accessed as XML data. Methods are provided for reading and writing data as XML, and reading and writing the structure of the dataset as XML schema. In addition, an XmlDataDocument can be associated with a dataset in order to allow simultaneous viewing, querying, and modifying the data as XML.
For detailed information on when to use ADO.NET data access technology, see the Decision Chart recommendations and the following individual topics: Introduction to Distributed Applications and Data Integration, Web Data Access Strategy Recommendations, Overview of ADO.NET, Accessing Data with ADO.NET, ADO.NET Datasets, Performing Database Operations Directly, Creating and Using Datasets.
For applications written in native code, ADO provides a COM-based application-level interface for OLE DB data providers. Similar to ADO.NET, ADO supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects using live connections to data in relational databases and other stores. And, like ADO.NET, ADO can construct client-side recordsets, use loosely coupled recordsets, and handle OLE DB's data shaping rowsets.
ADO also supports some behaviors not exposed through ADO.NET, such as scrollable, server-side cursors. However, because server-side cursors require holding database resources, their use might have a significant negative impact on the performance and scalability of your application. In order to transmit ADO recordsets through firewalls you need to configure the firewall to enable the COM marshaling request, taking into account the associated security ramifications. COM marshaling also limits data types to those defined by the COM standard. Optionally, you can persist an ADO recordset in an XML format and transmit the XML text instead.
OLE DB is the strategic system-level programming interface for accessing data, and is the underlying technology for ADO as well as a source of data for ADO.NET. OLE DB is an open standard for accessing all kinds of data — both relational and non-relational data including: mainframe ISAM/VSAM and hierarchical databases; e-mail and file system stores; text, graphical, and geographical data; and custom business objects.
OLE DB provides consistent, high-performance access to data and supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects using live connections to data in relational databases and other stores.
For more information on OLE DB, see the Microsoft OLE DB Web site (http://www.microsoft.com/data/oledb).
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.
Sequential, read-only server cursors are supported in ADO.NET by data readers (such as the SqlDataReader or OleDbDataReader objects), and in ADO by a forward only/read-only Recordset object. Sequential, read-only cursors provide the fastest means of reading data out of a database.
Scrollable, updatable server-side cursors are supported in ADO by a scrollable, updatable Recordset object. Server-side cursors should be used cautiously. Non-sequential scrolling and updating of results through a server-side cursor holds locks and causes resource contention that greatly limits scalability of the application. In place of using a scrollable, updateable server-side cursor, an application can generally benefit from the use of stored procedures for procedural processing of results on the server.
- Data access connection Both ADO.NET and ADO support explicit connections to the database. In ADO.NET, the developer might use a data reader, holding locks based on the current position and requiring a continuous connection to the database until the data is read. Alternatively, the data could be put into a dataset. When using a dataset, the developer can choose whether to keep the connection and transaction open while the data in the dataset is being modified, or open the connection and use transactions only when needed to populate data into the dataset and update changes back to the database. Closing the connection frees resources and locks for other users while the data in the dataset is being transmitted, viewed, and modified. In ADO, Recordsets can use either an open connection and hold locks as the user reads through the data in the database, or a client cursor Recordset can work with data without holding a database connection.
- Data scrolling Both ADO.NET and ADO can navigate data sequentially and non-sequentially. With the ADO.NET dataset you can also conveniently navigate from a row in one data table to the related rows in another table. Both the ADO recordset and the ADO.NET data readers support very fast, forward-only, read-only server-side cursors. The ADO recordset uniquely supports scrollable, updateable server-side cursors, although such cursors consume server resources and in most cases can be better implemented as logic within a stored procedure or as a loosely coupled client-side cursor.
- Ease of use The ADO.NET dataset provides self-describing data and removes the need to handle underlying data constructs such as tables, columns, constraints, and rows. Instead, with the dataset you have type-safe access to data using objects. This makes programs easier to read, write, and modify. Because application tiers can exchange data through XML-formatted datasets, new and extended communication is easily implemented over the lifecycle of the application. With ADO.NET, it does not matter which language you use for data access: they are all syntactically similar and use the same common language runtime services.
While both ADO.NET and ADO support loosely coupled data access, there is a difference. With ADO.NET you can control how the dataset changes are transmitted to the database by modifying the statements used by the DataAdapter object, or by inserting custom code that responds to row updating events. With this feature you can optimize performance, modify validation checks, or add any other extra processing — all without changing your application. For more information, see Issues in .NET Application Architecture.
Choosing OLE DB
The choice to write to OLE DB directly must be evaluated against using an easier data access method such as ADO.NET or ADO. The factors affecting your decision to use OLE DB's COM-level data access include:
- Performance Both ADO.NET and ADO are very fast, but they do insert an extra layer of abstraction between your application and the data provider when working with OLE DB data sources. If Microsoft SQL Server is your back-end database, and you are writing in managed code, the .NET Framework Data Provider for SQL Server will yield the best performance since it bypasses the overhead imposed by ADO and OLE DB and communicates with the server directly via the network driver. If performance is an issue against a non-SQL Server database, you might decide to code in Visual C++ using OLE DB. You might have additional options through other 3rd-party data providers.
- Functionality OLE DB defines native interfaces designed to be extensive and extensible enough to expose all of a database's underlying behavior, semantics, and functionality. ADO exposes a subset of the common functionality defined by the OLE DB interfaces, and ADO.NET provides common objects for working with data that hides differences between the behavior, functionality, and type systems of the different sources. If you need to access the native behavior, functionality, and data types of a particular database, OLE DB provides the most comprehensive native interface to the data source.
- Maintenance Using the OLE DB data access technology affects the cost of long-term maintenance for your application. Using OLE DB is more expensive than either ADO.NET or ADO because maintaining and enhancing complex code is more difficult.
- Developer skill Native coding in the COM environment requires considerable programming skill. Writing to the OLE DB interface is a very complex and difficult task. If your staff has OLE DB and COM expertise, you might choose OLE DB and capitalize on that experience.
- Language If you choose OLE DB for your data access technology, you must program using Visual C++. You can make the programming a bit easier by using the consumer and provider templates in the OLE DB Templates library. For more information, see OLE DB Templates.
If your application requires the high performance and raw manipulative power of OLE DB, you have the skill to write to a system-level programming interface, and are willing to pay for the more expensive long-term maintenance burden, then OLE DB is a good choice.
For more information on using OLE DB for data access, see OLE DB Programming.