Winforms Data from database across multiple forms

Question

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

Answer 1

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