Using DataSets with ASP.NET Pages

最新推荐文章于 2024-09-18 15:08:51 发布

llwen218

最新推荐文章于 2024-09-18 15:08:51 发布

阅读量653

点赞数

分类专栏： ASP.NET 文章标签： asp.net dataset datagrid server cache asp

ASP.NET 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

如果使用正确，DataSets能戏剧性地提高你Web站点的性能，然而，如果使用不正确，DataSets会浪费宝贵的服务器资源并降低Web Site的性能。为了更好地使用DataSets，DataSets可以被cache在存储器中，响应来自同样的或是不同的页面多个请求

Caching `DataSets`

你可以通过cache对象来缓存DataSet,DataTable或者DataView，为了缓存DataSet,你应该使用这样的语句，比如： Cache( "CachedDataSet" ) = dstDataSet ，这个语句增加了一个叫做 CachedDataSet 的项，然后将名为dstDataSet 的 DataSet 赋值给它

应该意识到，当系统资源变得很少或是应用程序重新启动，缓存中的项是会被移除的，换句话说，你没有考虑到当这个项不存在的情况。为了在任何时候都能从项获得cache对象，你应该这样写：
dstDataSet = Cache( "CachedDataSet" )
If myDataSet Is Nothing Then
' Recreate the DataSet
Cache( "CachedDataSet" ) = dstDataSet
End If

当要从cache中取数据时，总是检查这个项是否存在，如果不是，重新创建并把这个项重新添加到cache

注意

你必须使用“Is”而不是“=”来比较 cache 中的内容是否存在，你必须使用这个操作符是因为你要比较的是对象，而不是值。

Displaying Cached Data

假设，你正在构建网上商店，想要把 Products 表的一列商品显示在 DataGrid 上，或者是，你想要是你的顾客把商品分类显示在不同的列上。这种情况下，当顾客每次分类商品时，数据没有变化，只是数据的顺序改变了，就没有必要重新从数据库列出商品。如下面的例子。

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>

Figure 12.10. Sorting cached products.

graphics/12fig10.jpg

在cache中缓存商品戏剧性地改变ASP.NET页面的性能，通过存储在内存中的商品，数据库不必要每次页面请求就重新查询一遍，还能保证每次商品都是存在的。如果你要改变商品列表，你可以通过以下代码移除 Products 项

Cache.Remove( "Products" )

Filtering Cached Data

你可以允许用户去选择一个分类来显示相对应的商品类表，Categories 表和 Products 可以被同时存放在 cache 。当某个商品类型被选中了，你可以从 cache 中筛选出这个类型的商品。代码如下：

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(
 "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )
    dadNorthwind = New SqlDataAdapter( "Select CategoryID, CategoryName From Categories",
 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>

Figure 12.11. Filtering a cached `DataSet`.

graphics/12fig11.jpg

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).

你可以缓存所有的雇员信息在 DataView，为了显示某个特殊雇员的详细信息，你可以使用 cache 中 DataView 的 Find 函数。如：

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(
 "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>