Custom cached sorting using datatables, viewstate and other bits n bobs
Like most people I have found the need to create a custom datasource that doesn't easily bind to a datagrid without some serious manipulation. I did some looking to see if I could find an article that did what I wanted and couldn't so ****this may not be the right way***. It is however _my_way_ which worked for me in my particular situation.
My requirements were that the user could request a quite large and complicated SQL query that takes about 20-25 seconds to work out and display. (Yes, its indexed and optimised...)
They could then sort the results on any of the column headers without having to rerun the query anytime throughout the day.
Some of the columns are computed columns that cannot be easily worked out in SQL (due to the nature of the query and the data sources) but the data only changes once an hour on the database so relevency wasn't an issue - neither (thankfully) was paging.
I've kind of recreated the scenario here using the PUBS database.
The code is set out below and I've commented it inline. Copy the HTML, Copy the CODE and then step through it, I explain whats happening inline as you go.
Basically, you grab a datareader
Dim oSQL As New SqlClient.SqlCommand
Dim oDR As SqlClient.SqlDataReader
Dim oConn As New SqlClient.SqlConnection
With oConn<BR> ' TODO: Change the CONN String
.ConnectionString = "Persist Security Info=False;Data Source=YOUR_DATABASE_SERVER;Initial Catalog=pubs;User ID=sa;Password=;"
.Open()
End With
With oSQL
.CommandText = "SELECT au_lname, au_fname, city, LEFT(phone, 3) AS tel_code FROM authors WHERE state = 'CA'"
.Connection = oConn
.CommandTimeout = 60
.CommandType = CommandType.Text
End With
' Fill the datareader
oDR = oSQL.ExecuteReader
and populate a datatable.
While oDR.Read
' Build the datatable
oRow = oTable.NewRow
oRow("Name") = oDR(1) & " " & oDR(0) & ""
oRow("City") = oDR(2)
oRow("Surname") = oDR(0)
oRow("Forename") = oDR(1)
Dim iCode% = oDR.Item(3)
oRow("code_for_sorting") = iCode
Select Case iCode
Case 408
' some simple manipulation of one of the datatable columns
oRow("code") = "<span class=""Code408"">" & iCode.ToString & "</span>"
Case Else
oRow("code") = "<span style=""background:background-color: #CC00FF"">" & iCode.ToString & "</span>"
End Select
oTable.Rows.Add(oRow)
End While
THis datatable is then added to the statebag
viewstate.add("oTable", oTable)
The sorting is done by manipulation of the column headers, which in my instance are linkButton controls, by changing the CommandArgument and dealing with the logic within the buttonClick event.
Property SortBy() As String
Get
SortBy = m_sSortBy
If m_sSortBy Is Nothing Then
SortBy = "Forename asc, Surname asc"
End If
End Get
Set(ByVal Value As String)
m_sSortBy = Value
End Set
End Property
...
Private Sub lnkName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkName.Click
Dim sSortDir$ = lnkName.CommandArgument
If lnkName.CommandArgument = "DESC" Then
'Set the sort by for the current requirements
SortBy = "Forename DESC, Surname desc"
'flip the command Argument
lnkName.CommandArgument = "ASC"
Else
' its the other way round
SortBy = "Forename ASC, Surname ASC"
lnkName.CommandArgument = "DESC"
End If
Sort_Table()
End Sub
The custom caching is again dealt with in the statebag by storing a property and handling the logic in the postback events.
Public Property LastRefresh() As Date
Get
If LastRefresh = Nothing Then
LastRefresh = viewstate.Item("LastRefresh")
Else
LastRefresh = m_dLastRefresh
End If
End Get
Set(ByVal Value As Date)
End Set
End Property
As I said, cut n paste the code into Visual Studio and it should all become crystal clear :)
<![CDATA[<%@ Page Language="vb" AutoEventWireup="false" Codebehind="Index.aspx.vb" Inherits="Intranet.article"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>oTitle</title>
<style type="text/css">
<!--
.Code408 {
background-color: #CC99FF;
}
-->
</style>
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<P>State <asp:TextBox id=txtState runat="server" ReadOnly="True" Width="48px">CA</asp:TextBox><asp:Button id=Button1 runat="server" Text="Button"></asp:Button><BR></P>
<P>
<!-- start of table with repeater content -->
<table cellSpacing=1 cellPadding=2 bgColor=#7babdf border=0>
<tr bgColor=#caddf4>
<td><asp:linkbutton id=lnkName runat="server" CssClass="Link2" CommandArgument="DESC">Authors Name</asp:linkbutton></td>
<td><asp:LinkButton id=lnkCity runat="server">City</asp:LinkButton></td>
<td><asp:LinkButton id=lnkCode runat="server">Code</asp:LinkButton></td>
</tr>
<!-- we don't need the viewstate for this. Its the datatable we are interested in -->
<asp:Repeater id=rptAuthors runat="server" EnableViewState="False">
<ItemTemplate>
<tr valign="middle">
<td width="225" height="15" align="left" bgcolor="#ffffff"><%# DataBinder.Eval(Container.DataItem,"Name") %></td>
<td width="92" align="left" bgcolor="#ffffff"><%# DataBinder.Eval(Container.DataItem,"City") %></td>
<td width="89" align="left" bgcolor="#ffffff"><%# DataBinder.Eval(Container.DataItem,"Code") %></td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
<!-- End of table with repeater content -->
</P>
</form>
</body>
</HTML>
]]>
CODEBEHIND:
<![CDATA[Public Class article
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Protected WithEvents txtState As System.Web.UI.WebControls.TextBox
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents rptAuthors As System.Web.UI.WebControls.Repeater
Protected WithEvents lnkName As System.Web.UI.WebControls.LinkButton
Protected WithEvents lnkCity As System.Web.UI.WebControls.LinkButton
Protected WithEvents lnkCode As System.Web.UI.WebControls.LinkButton
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private m_sSortBy$
Private m_dLastRefresh As Date
' Create some helper properties for handling the LastRefresh and sortBy events
Public Property LastRefresh() As Date
Get
If LastRefresh = Nothing Then
LastRefresh = viewstate.Item("LastRefresh")
Else
LastRefresh = m_dLastRefresh
End If
End Get
Set(ByVal Value As Date)
End Set
End Property
Property SortBy() As String
Get
SortBy = m_sSortBy
If m_sSortBy Is Nothing Then
SortBy = "Forename asc, Surname asc"
End If
End Get
Set(ByVal Value As String)
m_sSortBy = Value
End Set
End Property
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Page.IsPostBack = False Then
' initialise the LastRefresh property
viewstate.Add("LastRefresh", Date.Now)
End If
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' This is a fresh search because it comes from the search button,
' so the SortBy needs refreshing. Easiest way to do this is to Set it to nothing and let the logic
' within the property kick in.
SortBy = Nothing
' Create a new DataTable, get fresh data
Dim oTable As System.Data.DataTable
oTable = getData(SortBy)
rptAuthors.DataSource = oTable
rptAuthors.DataBind()
oTable = Nothing
End Sub
Function getData(ByVal SortBy As String) As System.data.DataTable
Dim oSQL As New SqlClient.SqlCommand
Dim oDR As SqlClient.SqlDataReader
Dim oConn As New SqlClient.SqlConnection
With oConn<BR> ' TODO: Change the CONN String
.ConnectionString = "Persist Security Info=False;Data Source=YOUR_DATABASE_SERVER;Initial Catalog=pubs;User ID=sa;Password=;"
.Open()
End With
With oSQL
.CommandText = "SELECT au_lname, au_fname, city, LEFT(phone, 3) AS tel_code FROM authors WHERE state = 'CA'"
.Connection = oConn
.CommandTimeout = 60
.CommandType = CommandType.Text
End With
' Fill the datareader
oDR = oSQL.ExecuteReader
' Create a new datatable and row object
Dim oTable As System.Data.DataTable
oTable = makeTable()
Dim oRow As System.Data.DataRow
While oDR.Read
' Build the datatable
oRow = oTable.NewRow
' I use the ordinal. Thats because I'm lazy and its quicker.
' You can start a flame war over this if you like but I will not change :)
oRow("Name") = oDR(1) & " " & oDR(0) & ""
oRow("City") = oDR(2)
oRow("Surname") = oDR(0)
oRow("Forename") = oDR(1)
' I'm going to change the color of the area code based on the location.
' To accomplish this, I need two fields on the datatable:
' "CODE" which is the column which I shall sort by and
' "CODE_FOR_SORTING" which is the column to display.
' If you tried sorting on "CODE" - a string column - you'd get strange results, hence the 'hidden' column.
Dim iCode% = oDR.Item(3)
oRow("code_for_sorting") = iCode
Select Case iCode
Case 408
' some simple manipulation of one of the datatable columns
oRow("code") = "<span class=""Code408"">" & iCode.ToString & "</span>"
Case Else
oRow("code") = "<span style=""background:background-color: #CC00FF"">" & iCode.ToString & "</span>"
End Select
oTable.Rows.Add(oRow)
End While
'Tidy up
oConn.Close() : oConn = Nothing
oSQL = Nothing : oDR = Nothing
viewstate.Item("LastRefresh") = Date.Now
' By utilising the defaultView.Sort method of the datatable, we can sort by any of our custom
' fields that we've created within oTable.
oTable.DefaultView.Sort = SortBy
' Add the datatable into the state bag.
ViewState.Add("oTable", oTable)
Return oTable
End Function
Function makeTable() As System.Data.DataTable
' Makes the in-memory table to play with.
Dim oTable As DataTable
oTable = New System.Data.DataTable("Authors")
With oTable
oTable.Columns.Add("Name", GetType(String))
oTable.Columns.Add("City", GetType(String))
oTable.Columns.Add("Forename", GetType(String))
oTable.Columns.Add("Surname", GetType(String))
oTable.Columns.Add("Code", GetType(String))
oTable.Columns.Add("Code_For_Sorting", GetType(Integer))
End With
Return oTable
End Function
Public Sub Sort_Table()
Dim oTable As DataTable
' Custom caching. The last refresh time is held in the state bag,
' meaning we can look it up (via the property LastRefresh) and determine
' whether or not we need new data. If we do, call getData, if not, just use the
' oTable within the statebag.
'
' This is why the viewstate of the actual repeater is set to null.
If LastRefresh < Date.Now.AddMinutes(-3) Then
oTable = getData(SortBy)
Else
oTable = ViewState.Item("oTable")
oTable.DefaultView.Sort = SortBy
End If
rptAuthors.DataSource = oTable
rptAuthors.DataBind()
End Sub
#Region "Sortby Routines"
' These could be extended by using the onBubble event handler and identifying the correct handler in a select CASE of the sending control
Private Sub lnkName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkName.Click
Dim sSortDir$ = lnkName.CommandArgument
If lnkName.CommandArgument = "DESC" Then
'Set the sort by for the current requirements
SortBy = "Forename DESC, Surname desc"
'flip the command Argument
lnkName.CommandArgument = "ASC"
Else
' its the other way round
SortBy = "Forename ASC, Surname ASC"
lnkName.CommandArgument = "DESC"
End If
Sort_Table()
End Sub
Private Sub lnkCity_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkCity.Click
Dim sSortDir$ = lnkCity.CommandArgument
If lnkCity.CommandArgument = "DESC" Then
SortBy = "City DESC"
lnkCity.CommandArgument = "ASC"
Else
SortBy = "City ASC"
lnkCity.CommandArgument = "DESC"
End If
Sort_Table()
End Sub
Private Sub lnkCode_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkCode.Click
Dim sSortDir$ = lnkCode.CommandArgument
If lnkCode.CommandArgument = "DESC" Then
SortBy = "code_for_sorting DESC"
lnkCode.CommandArgument = "ASC"
Else
SortBy = "code_for_sorting ASC"
lnkCode.CommandArgument = "DESC"
End If
Sort_Table()
End Sub
#End Region
End Class
]]>