Filtering Data after its been cached
I have a web app that contains 5 listboxes with data that is loaded on page_load. In trying to develop a web app that has increased performance by keeping frequently accessed or expensive data in memory rather than relying on repeated database calls, I've created dataSet caches for each listbox. An example of one of the caches is given below:
Public Sub LoadDistrictData(ByVal e As String)
If Cache("Dist") Is Nothing Then
Dim dsDist As New DataSet
dsDist = getDistrict()
Cache.Insert("Dist", dsDist, Nothing, DateTime.Now.AddMinutes(10), Nothing)
End If
Dim dvDistrict As New DataView(Cache("Dist").Tables(0))
lstDistrict.DataSource = dvDistrict
lstDistrict.DataValueField = "Distrc"
lstDistrict.DataTextField = "cDistrict" '"DIDSNM"
lstDistrict.DataBind()
End Sub
Function getDistrict() As DataSet
Dim dsDist As New DataSet
If Cache("Dist") Is Nothing Then
Dim daDistrict As New SqlDataAdapter("sp_SelectDistrict2", sqlConnWhere)
daDistrict.SelectCommand.CommandType = CommandType.StoredProcedure
Dim workParam2 As SqlParameter = Nothing
workParam2 = New SqlParameter("@regID", System.Data.SqlDbType.VarChar)
workParam2.Direction = ParameterDirection.Input
workParam2.Value = ViewState("Region")
daDistrict.SelectCommand.Parameters.Add(workParam2 )
Try
daDistrict.Fill(dsDist)
Catch ex As Exception
lblInfo.Text = "Database error: " & ex.Message
Exit Function
End Try
Cache.Insert("Dist", dsDist, Nothing, DateTime.Now.AddMinutes(10), Nothing)
End If
Return Cache("Dist")
End Function
Private Sub lstDistrict_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstDistrict.SelectedIndexChanged
Dim x As Integer
Dim distsel As Integer = 0
Dim dstval As String
For x = 0 To lstDistrict.Items.Count - 1
If lstDistrict.Items(x).Selected = True Then
dstval = dstval + Convert.ToString(lstDistrict.Items(x).Value)
distsel = distsel + 1
End If
Next
ViewState("distsel") = distsel
ViewState("Territory") = dstval
LoadTerritoryData(ViewState("Territory"))
......other subs
End Sub
Both the data and cache load correctly. However, a selection in one listbox (the first listbox) is suppose to filters data in the remaining 4 listboxes. But since my cache is not equal to NOTHING then the updated ViewState("Region") (created on SelectedIndexChange of the first listbox) is not used as the parameter which will filter the remaining listboxes. Instead the previous Cache is used. I could set the cache equal to NOTHING or Cache.Remove("Dist") on selectedindexchange, but that would require a hit to the server again (which is what I'm trying to avoid). Is there a way to FILTER the EXISTING dataSet Cache? I guess I'm going about this the wrong way. Any help would be greatly appreciated.
Thanks
|