Recommendations for Data Access Strategies
ADO.NET assumes a model for data access in which you open a connection, get data or perform an operation, and then close the connection. ADO.NET provides two basic strategies for how you work with this model. One model is 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 create an instance of it and then use a data adapter to fill it from the data source. You then work with the data in the dataset — for example, by binding controls to dataset members. For more information, see Datasets in Visual Studio Overview.
An alternative strategy is to perform operations directly against the database. In this model, you use a TableAdapter query or data command that includes an SQL statement or a reference to a stored procedure. You can then execute the query or command to perform the operation. For more information, see Fetching Data into Your Application.
Storing Data in Datasets
A common model for data access in Visual Studio applications is to store data in datasets and use TableAdapters or data adapters to read and write data in the database. The advantages of the dataset model are:
Working with multiple tables A dataset can contain multiple tables of results, which it maintains as discrete objects. You can work with the tables individually or navigate between them as parent-child tables.
**Manipulating data from multiple sources ** The tables in a dataset can represent data from many different sources (for example, from different databases, from XML files, spreadsheets, and so on, all in the same dataset). Once the data is in the dataset, you can manipulate it and relate it in a homogeneous format as if it had come from a single source.
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.
Data exchange with other applications A dataset provides a powerful way to exchange with other components of your application and with other applications. Datasets include extensive support for features such as serializing data as XML and reading and writing XML Schemas.
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.
Maintaining records for reuse A dataset allows you to work with the same records repeatedly without requerying the database. Using dataset facilities, you can filter and sort records, and you can use the dataset as a source of data if you are paging.
Ease of programming When you work with a dataset, you can generate a class file that represents its structure as objects (for example, a Customers table in the dataset can be accessed as the dataset.Customers object). 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.
Performing Database Operations Directly
Alternatively, you can interact with the database directly. In this model, you use a data command object that includes an SQL statement or a reference to a stored procedure. You can then execute the command to perform the operation. For more information, see Commands and Parameters (ADO.NET).
.gif "Security note") Security Note: |
|---|
When using data commands with a CommandType property set to Text, carefully check information that is sent from a client before passing it to your database. Malicious users might try to send (inject) modified or additional SQL statements in an effort to gain unauthorized access or damage the database. Before you transfer user input to a database, you should always verify that the information is valid. A best practice is to always use parameterized queries or stored procedures when possible. |
Performing database operations directly has specific advantages, which include:
Extra functionality As noted, there are some operations, such as executing DDL commands, that you can only accomplish by executing data commands.
More control over execution By using commands (and a data reader, if you are reading data), 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 to 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, such as displaying search results in a Web page. In that case, creating and filling a dataset might be an unnecessary step in displaying the data.
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. For example, in Web Forms pages, the page is recreated with each round trip; unless you add programming to save and restore a dataset, it, too, is discarded and recreated with each round trip. 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.
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 (along with its data) 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. For more information see, Creating Client Data Applications.
Specifically, use dataset under the following circumstances:
If you are working with the same records repeatedly, such as allowing a user to navigate between records.
If you are using the Windows Forms data-binding architecture, which is specifically designed to work with datasets.
For any of the other reasons listed under Web Forms above.
Use a TableAdapter query or data command under the following circumstances:
If you are getting a scalar value from the database
If you are performing a non-query operation, such as a DDL command.
If 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.
Web Forms
In general, use data commands; to fetch data, use a data reader. 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 dataset under the following circumstances:
You want to work with multiple separate tables or tables from different data sources.
You are exchanging data with another application or a component such as an XML Web service.
You need to perform extensive processing with each record you get from the database. If you use a data command and data reader, processing each record as you read it can result in the connection being held open for a long period, which in turn can affect the performance and scalability of your application.
If your data processing involves interdependent records (for example, looking up information in related records).
If you want to perform XML operations such as XSLT transformations on the data.
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 example, sending it as the return value of a method and receiving it as a method argument. This is a fundamental choice in XML Web services; even if there are other reasons you might consider using a data command, data exchange with other components almost always means that you should use a dataset.
For any of the reasons listed above for Web Forms.
Use a data command (and if appropriate, a data reader) under the following circumstances:
The XML Web service is retrieving a scalar value.
The XML Web service is performing a non-query operation, such as a DDL command.
The XML Web service is calling a stored procedure to execute logic within the database.
See Also
Concepts
Other Resources
Getting Started with Data Access
Connecting to Data in Visual Studio
Preparing Your Application to Receive Data
Fetching Data into Your Application
Displaying Data on Forms in Windows Applications