This documentation is archived and is not being maintained.

Web Data Access Strategy Recommendations

Visual Studio .NET 2003

When designing data access in your Web application, you have a variety of choices to make about how you communicate with a data source, whether you store data between round trips of the page, and if you do store data, where. The choices you make can determine how efficiently your application runs and how well it scales. There is no one data access strategy that is suitable for all situations. In fact, each choice has its own advantages and disadvantages, which you will need to understand.

The following sections detail the basic design choices you must make for Web Forms data access. The choices are presented here in a sequence; each choice is based on a choice you made previously.

Security Note   Accessing a database from a Web application introduces security issues that you should be aware of. For details, see Access Permissions for Web Applications. For details about accessing SQL Server, see Accessing SQL Server from a Web Application.

Dataset or Direct Access and Data Reader?

A primary choice is whether you want to cache records in a dataset or whether you want to access the database directly and read records using a data reader. For some database operations, such as creating and editing database structures, you cannot use a dataset. For example, if you want to create a new database table from within your application, you cannot do so using a dataset; instead, you would execute a data command. For general data-access scenarios, however, you can often choose between storing records in a disconnected dataset and working directly with the database using data commands.

Note   For details about datasets, see Introduction to Datasets. For details about accessing a database directly, see Introduction to DataCommand Objects in Visual Studio.

Each strategy has inherent advantages that apply to any data access scenario (not just Web Forms pages). For example, datasets make it easier to work with related tables and to work with data from disparate sources. Conversely, using a data reader often results in slightly better performance and memory usage, it eliminates the extra step (and memory requirement) of filling a dataset, and it allows you more direct control over the statements or stored procedures you use. For details about the comparative advantages of each of these approaches in general, see Recommendations for Data Access Strategies.

Datasets and Data Commands in Web Forms Pages

When you are working with Web Forms pages, additional factors come into play when deciding whether to use a dataset or a data reader. A significant factor is the page life cycle — Web Forms pages are initialized, processed, and then discarded with each round trip. If you simply want to display data on the page, then creating a dataset, filling it, and then binding controls to it can represent unnecessary overhead, since the dataset will be discarded immediately. In many cases, it is more efficient to use data reader to fetch the data and then to bind controls to it at run time.

Tip   No matter how you decide to get data from the data source, you should always attempt to minimize the amount of data you use in a page. The more data a query or stored procedure brings back, the more database and server resources you use.

In general, therefore, you might want to assume that in Web Forms pages it is usually better to use data commands to execute SQL statement or stored procedures, using a data reader to fetch data. For example, to display information in a DataList command, you can execute a SQL statement and then bind the control to a data reader. Exceptions to this general rule might be the following:

  • Working with related tables   Datasets allow you to maintain multiple related tables and include support for relations and referential integrity. Working with related records — for example, reading parent and corresponding child records — can be substantially simpler in a dataset than fetching records independently using when executing commands against a database.
  • Exchanging data with other processes   If your Web Forms page is getting its data from another component, such as an XML Web service, you will almost always use a dataset to hold the local copy of the data. Datasets automatically read and write the XML stream used to communicate between components in the .NET Framework.
  • Working with a static set of records   If you need to use the same set of records repeatedly — for example, when users are paging in a grid — it can be efficient to fill those records into a dataset rather than return to the data source with each round trip. This is particularly true if you want to set aside a particular set of records to work with from a database that is changing frequently.

A more general advantage of using a dataset is that it is easier to program than working directly with data commands. However, you must weigh this advantage carefully against other application requirements.

For details about using data commands, see Introduction to DataCommand Objects in Visual Studio.

Save Dataset or Recreate Each Time?

If you do use a dataset, your next choice is whether you want to recreate it with each round trip. You have two options:

  • Each time the page is processed, create an instance of the dataset and fill it. When page processing is finished and the page is sent to the browser, the dataset is discarded.
  • Create and fill the dataset once (typically, the first time the page runs). Then save the dataset in a way that you can retrieve it with each subsequent round trip.

Creating the dataset each time means that with each round trip — effectively, each time the user clicks a button on your page — you execute a query or stored procedure against the data source. For example, you might have a Web Forms page where the user wants to page through data. If you create the dataset each time, the Web Forms page executes a query against the data source to get the next set of records to display.

Tip   Remember to always minimize data transfer. Whenever practical, use selection criteria to reduce the number of records you need on a page.

If you save and restore the dataset, on the other hand, you do not need to go back to the source just to get a few more records. However, saving the dataset has a number of drawbacks. An important one is that the dataset consumes memory between round trips. If the dataset is very large, for example, it can take considerable server memory to store it. If multiple users create large datasets, you can quickly consume available server memory. (An option is to store data in the page; for details, see the next section.)

Another potential drawback is that the dataset can get out of sync with the data source, since you are not refreshing the dataset each time the user clicks a button. If you are working with very volatile data (inventory data, for instance), you might find it better for your application to recreate the dataset with each round trip.

Cache on the Server or on the Client?

If you decide to save a dataset between round trips, you must decide where to keep it. This issue is the standard one of state maintenance in Web Forms pages — where do you store information you want to preserve between round trips? For information about saving values, see Web Forms State Management.

You have two options:

  • On the server, save the dataset in Session state, Application state, or using a cache.
  • On the client — that is, in the page — save the dataset using view state or by putting the data into your own hidden field. (View state is also implemented using a hidden field.)

Storing the dataset on the server uses server resources. If you store too much data (a large dataset, or many users storing small datasets), it can affect server performance and scalability. Using a cache can partly offset this problem, because the cache manager will discard the dataset if the server needs memory or if cached data expires. But because the dataset is not guaranteed to be in the cache, you must add logic to your page to check that the dataset is available in the cache; if not, you must recreate it and put a copy back in the cache.

Storing data in the page means that you do not need server resources to save the data. However, the data becomes part of the HTML stream of the page. If the dataset is large, it can substantially affect the time it takes for the page to load into the user's browser and to post the page back to the server. For details about saving data in view state, see Saving Web Forms Page Values using View State.

Tip   Always try to keep the size of a dataset to a minimum by filling it with only the records you need.

No matter where you decide to store the dataset, you must add logic to your page to save it and restore it at the appropriate time. The following example shows a typical way to store and restore a dataset in Session state. The dataset dsCustomers1 is an instance of the dataset class dsCustomers. Note that the dataset is stored in Session state as type Object. When restoring the dataset from Session state, you must cast it from Object back to a dataset class.

'Visual Basic
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
   If Page.IsPostBack Then
      dsCustomers1 = CType(Session("myDsCustomers"), dsCustomers)
      If Session("myDsCustomers") Is Nothing Then
         Session("myDsCustomers") = dsCustomers1
      End If
   End If
End Sub

private void Page_Load(object sender, System.EventArgs e)
   // Put user code to initialize the page here
    if (Page.IsPostBack) 
        dsCustomers1 = (dsCustomers) Session["myDsCustomers"];
        if (Session["myDsCustomers"] == null) 
            Session["myDsCustomers"] = dsCustomers1;

See Also

Web Forms State Management | Caching Application Data | Introduction to Distributed Applications and Data Integration | Introduction to Data Access in Web Forms Pages | ASP.NET Web Application Security