Ufdjrw

I have a winforms applications that has an ms sql server backend. In my database i have lookup tables for like status, and other tables where the data rarely changes. In my application, several forms might use the same lookup tables (Some have a lot of data in them). Instead of loading/filling the data each time the form is open, is there a way to cache the data from the database that can be accessed from multiple forms. I did some searching, but couldnt find the best solution. There is caching, dictionaries, etc. What is the best solution and can you point me to the documentation that discusses it and may even have an example.

Edit:
In my original post I failed to mention that I have a strongly typed dataset and use tableadapters. I want to preload my lookup tables when my application starts, and then have these dataset tables be used throughout the application, on multiple forms without having to fill them on every form.

I have tried creating a class:

Public Class dsglobal

 Public Shared EML_StaffingDataSet As EML_StaffingDataSet

 Public Shared Sub populateDS()
 EML_StaffingDataSet = New EML_StaffingDataSet
 End Sub

 Public Shared Sub loadskills()
 Dim ta As New EML_StaffingDataSetTableAdapters.TSTAFFSKILLTableAdapter
 ta.Fill(EML_StaffingDataSet.TSTAFFSKILL)
 End Sub

End Class 

I run this on a background worker when my application is starting up. So it loads the dataset table. On fill, I can see the datatable has data in it. When I open a form, i want to use the dataset table, but it seems to clear the data out. Not sure if my approach is correct or where my error is.

Edit2:
I have also tried this per comments, but not sure I am doing it correctly. If I am doing it correctly, then how do I use that as a datasource at design time, can i only do that programmatically?

Public Module lookupdata
 Private EML_StaffingDataSet As EML_StaffingDataSet

 Private skillvalues As List(Of skill)

 Public ReadOnly Property skill As List(Of skill)
 Get
 If skillvalues Is Nothing Then
 getskillvalues()
 End If
 Return skillvalues
 End Get
 End Property

 Private Sub getskillvalues()
 skillvalues = New List(Of skill)
 EML_StaffingDataSet = New EML_StaffingDataSet
 Dim ta As New EML_StaffingDataSetTableAdapters.TSTAFFSKILLTableAdapter
 ta.Fill(EML_StaffingDataSet.TSTAFFSKILL)

 For Each row As DataRow In EML_StaffingDataSet.TSTAFFSKILL
 Dim skill As New skill
 skill.skill_id = row("skill_id")
 skill.skill_desc = row("skill_desc")
 skill.skill_open_ind = row("skill_open_ind")
 skillvalues.Add(skill)
 Next
 End Sub

End Module

Public Class skill
 Public Property skill_id As Integer
 Public Property skill_desc As String
 Public Property skill_open_ind As Boolean
End Class

You might want to consider a lazy loading pattern, like this:

Public Module LookupData
 Private statusValues As List(Of LookupValue)

 Public Readonly Property Statuses As List(Of LookupValue)
 Get
 If statusValues Is Nothing Then
 GetStatusValues()
 End If

 Return statusValues
 End Get
 End Property

 Private Sub GetStatusValues()
 statusValues = New List(Of LookupValue)

 Dim sql = "select key, value from StatusTable"

 'TODO: Read the items from the database here, adding them to the list.

 End Sub

End Module

Public Class LookupValue
 Public Property Key As String
 Public Property Value As String
End Class

The idea is that you've got a single instance of LookupData (a Module in VB, there can be only one). Lookup data has a series of Properties, each of which returns a list of values from the database. If the data has already been loaded, it just returns what it has cached. If the data has not been loaded, then the first time it is referenced it retrieves it from the database.

You can consume it elsewhere in your code as follows:

Dim myStatuses = LookupData.Statuses