Caching dropdown lists

Friday, September 5, 2003

.NET Scoilnet

I like to play with Cache clas, it's a really powerful beast, but it has some limits, which I hope will be improve in .Net 2.0.

I take for example the control I did for the website Scoilnet. I call it the Resources Finder.

The idea is simple. Through 3 dropdown lists, I search in a database some resources for the teachers or students.

The first dropdown is Courses, the second Subjects and the 3rd one Material.

The complexity here is that I need to create a cascade effect, applying the relationships I defined in SQL.

It means that if I ask for Junior Cycle in courses, I show only the subjects for this course, and after if I pick up a subject, I show only the materials available.

I think it's agood practice, because the user is not presented with an empty results page if I have nothing to display.

The difficulty here is that we talk about web where refresh and postback are the keys.

This is where the Cache can be use but unfortunatly I was not able to fully implement it. I have no idea what a user would choose in the dropdown, and because I have something like 6000 records in the databse, no doubts that I can chae the whole database !

So I compromise with some partial Cache, limited in time. So if the user stays on the same Course and the same subjects it's efficient.

If he choose another course I reset the Cache.

There is some extract of the code and you can also check on Scoilnet the application :

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If Not IsPostBack Then
           'on the first load, I reset the Cache before binding because the dropdown content can change from one page to another

            Cache.Remove("courses")
            Cache.Remove("subjects")
            Cache.Remove("materials")
            res1.Items.Clear()
            res2.Items.Clear()
            res3.Items.Clear()

'I do the same basic stuff every time I need to bind the data to one of the dropdown (res1, res2 or res3)
' SQL contains the Query

Dim RSTable As DataTable = GetCourses(Sql)
                If RSTable Is Nothing Then
res1.DataSource = ""

                Else
                    res1.Items.Clear()
                    res1.DataSource = RSTable

                    res1.DataTextField = "Course"
                    res1.DataValueField = "ID_Course"
                End If

                res1.DataBind()
end if
end sub

So I have three properties based on the same model. This is the GetCourses() one:

Private Property GetCourses(ByVal query As String)
        Get
            Dim Conn As SqlConnection

            Conn = New SqlConnection(ConfigurationSettings.AppSettings("Scoilnet"))

            Dim ds As DataSet
            If Cache("courses") Is Nothing Then
                ds = SqlHelper.ExecuteDataset(Conn, CommandType.Text, query)

                If ds.Tables(0).Rows.Count > 0 Then
                    Cache.Insert("courses", ds.Tables(0), Nothing, DateTime.Now.AddSeconds(60), TimeSpan.Zero)
                    Return Cache("courses")
                Else
                    Return Nothing
                End If
            Else
                Return Cache("courses")
            End If
        End Get
        Set(ByVal Value)

        End Set
    End Property

Note that I setup a short expiration time. I tried different values but I don't see the point to cache more than one minute something like dynamic data which can change so quickly.

In conclusion I can say the Cache is a great stuff, but be aware about the limits.

The next thing I am working on is to let know the user when I have to populate a dropdown.
On some slow connections, refreshing 3 dropdowns list is a painful experience.

Thankfully I was able on this to implement Smartnavigations, but this trick is not working well in the case you have some other complex controls on the page like Tabs or Tree view.

No Comments