Recommended ADO.NET Data Access Strategies

Introduction

ADO.NET is a model intended primarily for disconnected data access. ADO.NET provides two strategies for you to work with this model.

1. Store data in a dataset, which is an in-memory cache of records you can work with while disconnected from the data source. To use a dataset, you use a data adapter to fill it from the data source. You then work with the data in the dataset.

2. Perform operations directly against the database. In this strategy, you use a data command object. You can then open a connection, execute the command to perform the operation, and then close the connection.

Each strategy has its own advantages. So you should choose a strategy based on what your data-access requirements are.

Advantages of Storing Data in Datasets

The advantages of the dataset model are:

• Data exchange with other applications - A dataset provides a powerful way to exchange with other components of your application and with other applications.
• Moving data between tiers in a distributed application - By keeping data in a dataset, you can easily move it between the presentation tier, business tier, and data tier of your applications.
• Working with multiple tables - A dataset can contain multiple tables. You can work with the tables individually or navigate between them as parent-child tables.
• Maintaining records for reuse - A dataset allows you to work with the same records repeatedly without re-querying the database.
• Manipulating data from multiple sources - The tables in a dataset can represent data from many different sources. Once the data is in the dataset, you can manipulate it and relate it as if it had come from a single source.
• Data binding - If you are working with forms, it is usually easier to bind controls to data in a dataset than it is to programmatically load data values into the control after executing a command.
• Ease of programming - When you work with a dataset, you can generate a class file that represents its structure as objects. This makes it easier, clearer, and less error-prone to program with, and is supported by Visual Studio tools such as IntelliSense, the Data Adapter Configuration wizard, and so on.

Advantage of performing Database Operations Directly

The advantages of performing database operations directly:

• More control over execution - By using commands, you get more direct control over how and when an SQL statement or stored procedure is executed and what becomes of the results or return values.
• Less overhead - By reading and writing directly in the database, you can bypass storing data in a dataset. Because the dataset requires memory, you can reduce some overhead in your application. This is especially true in situations where you intend to use the data only once. In that case, creating and filling a dataset might be an unnecessary step in displaying the data.
• Extra functionality - There are some operations, such as executing DDL commands, that you can run only by executing data commands.
• Less programming in some instances - In a few instances, particularly Web applications, there is some extra programming required to save the state of a dataset. If you use a data reader to read directly from the database, you avoid the extra steps required to manage the dataset.

Recommendations for Accessing Data

The following sections provide recommendations for which data-access strategy to use with specific types of applications.

Web Forms

Use data commands in general; use a data reader to fetch data. Because Web Forms pages and their controls and components are recreated each time the page makes a round trip, it often is not efficient to create and fill a dataset each time, unless you also intend to cache it between round trips.

Use datasets under the following circumstances:

• You need to perform extensive processing with each record you get from the database.
• You want to work with multiple separate tables or tables from different data sources.
• If your data processing involves interdependent records.
• If you want to perform XML operations such as XSLT transformations on the data.
• You are exchanging data with another application or a component such as an XML Web service.
• If you prefer the ease of programming provided by datasets.

XML Web Services

XML Web services are ASP.NET Web applications, and therefore use the same model as Web Forms pages: the XML Web service is created and discarded each time a call is made to it. This suggests that the data-access model for an XML Web service is largely the same as it is for Web Forms. However, XML Web services are often middle-tier objects, and an important part of their purpose is often to exchange data with other applications across the Web.

Use a dataset if:

• Your XML Web service sends and receives data.
• For any of the reasons listed above for Web Forms.

Use a data command if:

• The XML Web service is performing a non-query operation, such as a DDL command.
• The XML Web service is retrieving a scalar value.
• The XML Web service is calling a stored procedure to execute logic within the database.

Windows Forms

In general, in a Windows Form, use a dataset. Windows Forms are typically used on rich clients where the form is not created and discarded with each user operation, as with Web Forms. Windows Forms applications also traditionally offer data-access scenarios that benefit from maintaining a cache of records, such as displaying records one by one in the form.

Use a dataset if:

• You are using the Windows Forms data-binding architecture, which is specifically designed to work with datasets.
• You are working with the same records repeatedly, such as allowing a user to navigate between records.
• For any of the other reasons listed under Web Forms above.

Use a data command if:

• You are performing a non-query operation, such as a DDL command.
• You are getting a scalar value from the database
• You are getting read-only data to display in a form — for example, creating a report. Stated differently, if there is no need to keep the data available after accessing it, use a data command.