A Pagable DataGrid Utilizing a Cached DataSet

This demo illustrates how to build a pagable DataGrid that uses a cached DataSet. By utilizing a cached DataSet the DataGrid saves on making a database hit every time the user pages through the data.


The current time is 11/24/2017 7:05:10 AM

Caching Information
DataGrid was populated from the database...


199 found
Page 1 of 20
FAQIDDescription
2How do you convert a string to all uppercase or all lowercase?
7How do I dynamically resize an array?
8How do you create a database table through a SQL statement (via an ASP page, perhaps)?
10How can I tell what version of ADO I am using?
11How can I have my ASP page send HTML-formatted email?
12How can I display line breaks in an email message?
13How do you send email attachments through an ASP page?
14Can I send emails without using CDONTS?
15How can I determine the length of a string (how many characters are in a string)?
16Using VBScript, how can I determine if a variable is a valid date or not?
1 2 3 4 5 6 7 8 9 10 ...


Source Code

<%@ Page Language="VB" Debug="False" Explicit="True" Strict="True" 
     EnableSessionState="False" Buffer="True" SmartNavigation="True"%>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>

<script runat="server">
'Hold variable for my Record Count
Public RcdCount As Integer

Sub Page_Load(Sender As Object, E As EventArgs)

  'If page is posted for the first time
  If Not Page.IsPostBack Then
    'Reset my Datagrid to page 1
    MyDataGrid.SelectedIndex = 0

    'Call my datagrid method
    BindMyDataGrid()
  End If
End Sub


Sub MyDataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)
  MyDataGrid.CurrentPageIndex = e.NewPageIndex
  BindMyDataGrid()
End Sub


Sub BindMyDataGrid()
  'Programmatic Caching Setup
  Dim DataGridCache As DataSet = CType(Cache.Get("DataGridCache"),DataSet)

  'Check if Cache item is equal to Nothing
  If DataGridCache is Nothing Then
    Const CommandText As String = "SELECT FAQID, Description FROM tblFAQ ORDER BY FAQID"

    'The connection to our database
    Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

    Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
    Dim DS As New DataSet()

    myCommand.Fill(DS)
    MyDataGrid.DataSource = DS

    'Insert dataset into cache
    Cache.Insert ("DataGridCache", DS, Nothing, DateTime.Now.AddSeconds(360), TimeSpan.Zero)
    lblCacheInfo.text = "DataGrid was populated from the database..."

    'Specify what time the cache was updated
    Application("TimeCachedDataSetAdded") = DateTime.Now
	
    'Determine how many total records we have
    RcdCount = CInt(DS.Tables(0).Rows.Count.ToString())
  Else
    lblCacheInfo.text = "DataGrid was used from cache.  The cache was populated at " & _
               Application("TimeCachedDataSetAdded").ToString()

    'Populate datagrid from cache.
    MyDataGrid.DataSource = DataGridCache
    RcdCount = CInt(DataGridCache.Tables(0).Rows.Count.ToString())
  End If

  'Bind the datagrid from either source
  MyDataGrid.DataBind()

  ShowStats()
End Sub


Sub ShowStats()
  If RcdCount > 1 Then
    lblPageCount.Text = "<font size=2><b>" & RcdCount & " found <BR>Page " & _
       MyDataGrid.CurrentPageIndex + 1 & " of " & MyDataGrid.PageCount & "</b></font>"
  Else
    lblPageCount.Text = vbNullString
  End If
End Sub
</script>

    <i>The current time is <%=DateTime.Now%></i>
    <p>
    <b>Caching Information</b><br />    
    <asp:Label id="lblCacheInfo" runat="server" />
    <hr size="1" />
    <asp:Label id="lblPageCount" runat="server" Font-Bold="True" Font-Size="XX-Small"></asp:Label>

    <form runat="server">
       <asp:datagrid id="MyDataGrid" runat="server" Font-Bold="True" 
             Font-Size="Small" Font-Names="Verdana" 
             BorderColor="#DEBA84" AutoGenerateColumns="True" 
             BorderWidth="1px" BorderStyle="None" 
             AllowPaging="True" PageSize="10" 
             PagerStyle-HorizontalAlign="Right" 
             PagerStyle-Mode="NumericPages" 
             OnPageIndexChanged="MyDataGrid_Page" 
             HeaderStyle-Font-Bold="True" HeaderStyle-HorizontalAlign="Center" 
             HeaderStyle-ForeColor="Black" 
             HeaderStyle-BackColor="#FFF7BD" 
             BackColor="#DEBA84" CellPadding="3" 
             CellSpacing="2" width="85%" /> 
    </form>


[Return to the article.]