Using DataSets with ASP.NET Pages


Using DataSets with ASP.NET Pages

When used properly, DataSets can dramatically improve the performance of your Web site. When used improperly, however, DataSets can waste precious server resources and slow down the performance of your Web site.

In the following sections, you will examine some good uses of DataSets . You will learn how a DataSet can be cached in memory and used over multiple requests to the same or different pages.

Caching DataSets

When you place an item in your application's cache, the item is automatically preserved between page requests in your server's memory. You can cache a DataSet , DataTable , or DataView by using the Cache object.

To cache a DataSet , for example, you would use the following statement:

 
 Cache( "CachedDataSet" ) = dstDataSet 

This statement adds a new item to the cache, called CachedDataSet , and assigns the DataSet named dstDataSet to this item.

It's important to be aware that if system resources become low or the application is restarted, the items in the cache are removed. In other words, you should never assume that an item stays in the cache after you put it there. Whenever you retrieve an item from the Cache object, you should use logic like this:

 
 dstDataSet = Cache( "CachedDataSet" ) If myDataSet Is Nothing Then   ' Recreate the DataSet   Cache( "CachedDataSet" ) = dstDataSet End If 

Always check whether an item retrieved from the cache is equal to Nothing . If it is, you need to re-create it and add the item to the cache once again.

NOTE

You must use Is rather than = when comparing the contents of the cache to Nothing . You must use this operator because you are comparing objects (reference types), not values.


You can perform sophisticated operations with the Cache object. For example, you can add items to the cache with sliding or absolute expirations or file and key dependencies. For example, you can automatically update an item stored in the cache every hour .

NOTE

Caching is discussed in more detail in Chapter 17, "Caching ASP.NET Applications."


Displaying Cached Data

Imagine that you are building an online store and that you want to display a list of products from the Products table in a DataGrid . Furthermore, imagine that you want to enable your customers to sort the products in order of different columns . In this situation, it would not make sense to retrieve the list of products from the database each and every time the customer sorts the products table. The data hasn't changed, only the order of the data has changed.

The page in Listing 12.21 illustrates how you can cache a DataView that represents a list of products from the Products table and sort the cached data (see Figure 12.10).

Listing 12.21 CacheProducts.aspx
 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load   If Not IsPostBack Then     ' Bind to datagrid     dgrdProducts.DataSource = GetProducts()     dgrdProducts.DataBind()   End If End Sub Function GetProducts() As DataView   Dim dstProducts As DataSet   Dim conNorthwind As SqlConnection   Dim dadProducts As SqlDataAdapter   Dim dvwProducts As DataView   dvwProducts = Cache( "Products" )   If dvwProducts Is Nothing Then     dstProducts = New DataSet()    conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )     dadProducts = New SqlDataAdapter( "Select * from Products", conNorthwind )     dadProducts.Fill( dstProducts, "Products" )     dvwProducts = dstProducts.Tables( "Products" ).DefaultView()     Cache( "Products" ) = dvwProducts   End If   Return dvwProducts End Function Sub dgrdProducts_SortCommand( s As Object, e As DataGridSortCommandEventArgs )   Dim dvwProducts As DataView   ' Sort DataView   dvwProducts = GetProducts()   dvwProducts.Sort = e.SortExpression   ' Rebind to DataGrid   dgrdProducts.DataSource = dvwProducts   dgrdProducts.DataBind() End Sub </Script> <html> <head><title>CacheProducts.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid   ID="dgrdProducts"   AllowSorting="True"   OnSortCommand="dgrdProducts_SortCommand"   Runat="Server" /> </form> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 12.10. Sorting cached products.

graphics/12fig10.jpg

The DataGrid that displays the list of products uses the DataView returned by the GetProducts() function as its data source. The GetProducts() function attempts to retrieve a DataView containing the products from the Cache object. If the Products DataView is not present in the cache, it is created.

The AllowSorting property of the DataGrid is assigned the value True . You can sort a column by clicking the link that appears at the top of each column. When the rows in the DataGrid are sorted, the cached list of products are sorted and rebound to the DataGrid .

Caching the products in the Cache object dramatically improves the performance of this ASP.NET page. By storing the products in memory, the database does not need to be queried for the list of products each time the page is requested and each time the products are resorted.

If you need to alter the list of products, you can remove the Products item from the cache by using the following line of code:

 
 Cache.Remove( "Products" ) 

You might want to include this statement, for example, within a form that adds or updates the products in the Products table. After this statement is executed, the page in Listing 12.21 automatically retrieves the new list of products from the database the next time the page is requested.

Filtering Cached Data

In the preceding section, you learned how to cache the products displayed by an online store. If your server has sufficient memory, caching the complete list of products sold through the Web site might be worthwhile.

You can enable users to select a category and display a list of products from the selected category. Both the Categories and Products tables can be placed in the cache. When a category is selected, you can retrieve the list of products from the cache and filter the products for the selected category.

The page in Listing 12.22 demonstrates how to do so (see Figure 12.11).

Listing 12.22 CacheStore.aspx
[View full width]
 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Dim dstStore As DataSet Sub Page_Load   Dim conNorthwind As SqlConnection   Dim dadNorthwind As SqlDataAdapter   dstStore = Cache( "Store" )   If dstStore Is Nothing Then     dstStore = New DataSet()     conNorthwind = New SqlConnection( graphics/ccc.gif "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )     dadNorthwind = New SqlDataAdapter( "Select CategoryID, CategoryName From Categories", graphics/ccc.gif conNorthwind )     conNorthwind.Open()     dadNorthwind.Fill( dstStore, "Categories" )     dadNorthwind.SelectCommand = New SqlCommand( "Select * From Products", conNorthwind )     dadNorthwind.Fill( dstStore, "Products" )     conNorthwind.Close()     Cache( "Store" ) = dstStore   End If   If Not IsPostBack Then     dgrdCategories.DataSource = dstStore     dgrdCategories.DataMember = "Categories"     dgrdCategories.DataBind()   End If End Sub Sub dgrdCategories_ItemCommand( s As Object, e As DataListCommandEventArgs )   Dim dvwProducts As DataView   Dim intCategoryID   ' Select the Chosen Category   dgrdCategories.SelectedIndex = e.Item.ItemIndex   ' Get CategoryID from DataKeys Collection   intCategoryID = dgrdCategories.DataKeys( e.Item.ItemIndex )   ' Filter the Products   dvwProducts = dstStore.Tables( "Products" ).DefaultView()   dvwProducts.RowFilter = "CategoryID=" & intCategoryID   dgrdProducts.DataSource = dvwProducts   dgrdProducts.DataBind() End Sub </Script> <html> <head><title>CacheStore.aspx</title></head> <body> <form Runat="Server"> <table width="100%"> <tr><td valign="top" width="200"> <asp:DataList   ID="dgrdCategories"   OnItemCommand="dgrdCategories_ItemCommand"   SelectedItemStyle-BackColor="Yellow"   DataKeyField="CategoryID"   Runat="Server"> <ItemTemplate>   <asp:LinkButton     Text='<%# Container.DataItem( "CategoryName" ) %>'     Runat="Server" /> </ItemTemplate> </asp:DataList> </td><td valign="top"> <asp:DataGrid   ID="dgrdProducts"   Runat="Server" /> </td></tr> </table> </form> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 12.11. Filtering a cached DataSet .

graphics/12fig11.jpg

Both the Categories and Products tables are cached in a DataSet within the Page_Load subroutine. If the DataSet is missing from the cache, it is created and added to the cache.

When you select a product category, the dgrdCategories_ItemCommand subroutine executes, displaying the selected category with a yellow background. It also creates a DataView for the Products database table. The DataView is filtered to display only the products for the selected category. Finally, the DataView is bound to the DataGrid control named dgrdProducts , and the proper products are displayed.

The list of categories and products is retrieved from the Categories and Products database tables only the first time the page is requested. When the page is requested (by any user ) in the future, the cached DataSet is used.

Finding a Row in a Cached DataSet

Imagine that your company wants to build a private intranet Web site that contains a directory of employees. Suppose, furthermore, that your company wants to create a form that enables a user to search through the list of employees to display information on a particular employee (the employee name , phone number, and so on).

You can cache all the information about the employees in a DataView . To display detailed information for a particular employee, you can use the Find method with the cached DataView .

The page in Listing 12.23 illustrates how to do so (see Figure 12.12).

Listing 12.23 CacheEmployees.aspx
[View full width]
 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Button_Click( s As Object, e As EventArgs )   Dim dstEmployees As DataSet   Dim conNorthwind As SqlConnection   Dim dadEmployees As SqlDataAdapter   Dim dvwEmployees As DataView   Dim arrValues( 1 ) As Object   Dim intEmployeeIndex As Integer   ' Get cached DataView   dvwEmployees = Cache( "Employees" )   If dvwEmployees Is Nothing Then     dstEmployees = New DataSet()     conNorthwind = New SqlConnection( graphics/ccc.gif "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )     dadEmployees = New SqlDataAdapter( "Select * From Employees", conNorthwind )     dadEmployees.Fill( dstEmployees, "Employees" )     dvwEmployees = dstEmployees.Tables( "Employees" ).DefaultView()     dvwEmployees.Sort = "LastName, FirstName"     Cache( "Employees" ) = dvwEmployees   End If   ' Find the employee   arrValues( 0 ) = txtLastName.Text   arrValues( 1 ) = txtFirstName.Text   intEmployeeIndex = dvwEmployees.Find( arrValues )   If intEmployeeIndex <> -1 Then     lblName.Text = txtLastName.Text & ", " & txtFirstName.Text     lblPhone.Text = dvwEmployees( intEmployeeIndex ).Row( "HomePhone" )     lblNotes.Text = dvwEmployees( intEmployeeIndex ).Row( "Notes" )   Else     lblError.Text = "Employee Not Found!"    End If End Sub </Script> <html> <head><title>CacheEmployees.aspx</title></head> <body> <h2>Employee Directory</h2> <form Runat="Server"> <b>First Name:</b> <asp:TextBox   ID="txtFirstName"   Runat="Server" /> <p> <b>Last Name:</b> <asp:TextBox   ID="txtLastName"   Runat="Server" /> <asp:Button   Text="Find!"   OnClick="Button_Click"   Runat="Server" /> <hr> <asp:Label   ID="lblError"   ForeColor="Red"   EnableViewState="False"   Runat="Server" /> <asp:Label   ID="lblName"   EnableViewState="False"   Runat="Server" /> <p> <asp:Label   ID="lblPhone"   EnableViewState="False"   Runat="Server" /> <p> <asp:Label   ID="lblNotes"   EnableViewState="False"   Runat="Server" /> </form> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 12.12. Finding a row in a cached DataView .

graphics/12fig12.jpg

The form in Listing 12.23 contains two TextBox controls labeled First Name and Last Name . When you enter a first and last name and click Find, the Button_Click subroutine is executed.

The Button_Click subroutine retrieves a DataView representing the Employees database table from the Cache object. If the DataView does not exist in the cache, it is created and added to the cache.

After the Employees DataView is retrieved, the Find method finds an employee record that matches the first and last name entered into the form. If the employee is found, the employee's name, phone number, and notes are displayed in Label controls. Otherwise, the message "Employee Not Found!" is displayed.

An alternative approach to creating the page in Listing 12.23 is to use the DataView 's RowFilter property rather than the DataView 's Find method to display the correct employee. (The RowFilter property was discussed in the preceding section.) One advantage of using the RowFilter property is that it enables you to display multiple matching employees instead of just one.



ASP.NET Unleashed
ASP.NET 4 Unleashed
ISBN: 0672331128
EAN: 2147483647
Year: 2003
Pages: 263

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net