Visual Basic and Visual C# Concepts  

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 Introduction to Datasets.

An alternative strategy is to perform operations directly against the database. In this model, you use a data command object that includes an SQL statement or a reference to a stored procedure. You can then open a connection, execute the command to perform the operation, and then close the connection. If the command returns a result set — that is, if the command performs a Select statement — you can fetch the data using a data reader, which acts like a highly efficient read-only cursor. The data reader can then act as a source for data binding. For more information, see Introduction to DataCommand Objects in Visual Studio.

Each strategy has specific advantages, as detailed in the sections below. You should choose a strategy based on what your data-access requirements are.

Note   When you deploy an application that includes Visual Studio data-access components, you must make sure that the user installing the application has version 2.7 or later of the Microsoft Data Access Components (MDAC). For more information, see Adding a Launch Condition for Microsoft Data Access Components.

Storing Data in Datasets

A common model for data access in Visual Studio .NET applications is to store data in datasets and use a data adapter to read and write data in the database. (A .NET application is one that uses the .NET Framework: the common language runtime and the managed classes.) The advantages of the dataset model are:

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. If the command returns a result set — that is, if the command performs a Select statement — you can fetch the data using a data reader, which acts like a highly efficient read-only cursor.

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:

Because of the stateless nature of Web applications and the corresponding issues associated with storing datasets, it is sometimes more practical in Web applications to work directly against the database. For more information, see Introduction to Data Access in Web Forms Pages.

Recommendations for Accessing Data

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

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:

For more information, see Web Data Access Strategy Recommendations.

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:

Use a data command (and if appropriate, a data reader) under the following circumstances:

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.

Specifically, use dataset under the following circumstances:

Use a data command (and if appropriate, a data reader) under the following circumstances:

See Also

Introduction to DataCommand Objects in Visual Studio | Introduction to Datasets | Working with Data Commands: High-Level Process | Executing a Command | Introduction to Dataset Updates