Caching listboxes and dropdownlists? 1

[song2siren]

Hello

I use the following function in my code behind when the page loads to populate a listbox from values in a SQL table:

Public Function GetSubjects() As DataView
cmdSql = New SqlCommand("Pubs_getSubjects", myConnection)
cmdSql.CommandType = CommandType.StoredProcedure
myConnection.Open()
frmSubjects.DataSource = cmdSql.ExecuteReader()
frmSubjects.DataBind()
'insert blank choice on top of the subjects listbox.
frmSubjects.Items.Insert(0, New ListItem("Select Subject...", [String].Empty))
myConnection.Close()
End Function

However, I just need some guidance on how I would cache things like this rather than open a connection to the database each time the page is opened. Any suggestions would be very much appreciated.

Thanks

 

[BG12424]

You would cache (session or cache object, depending on your usage) the data that comes from the db rather than caching the entire control. With this said, load your data into a datatable rather than using an reader object (it's forward only and good for one use).

Function GetSubjects() as DataTable

Dim dt as DataTable
Dim cacheData as DataTable = Session("MyKey")

If cacheData Is Nothing Then
  dt = .... << get your data into table (DataAdapter.Fill would work >>
  Session.Add("MyKey",dt)
  Return dt
Else
  Return cache
End If

End Function

....Now you can use the GetSubjects() as a data source to whatever you need.

....Keep in mind that you want to open and close a connection to the database each time you need it; let garbage collection handle the disposing of the connection objects after closing; also this will prevent too many connections opening simultaneously, besides, it's good programming practice.

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

 

[song2siren]

Many thanks bgaines72.

This seems to point me in the right direction.