winforms save datatable locally

Question

In my application, I load a small datatable from a database and use the datatable to bind a column to a combobox dropdownlist.

Everytime the combobox is shown, ibelieve it pulls data from the database which seems unnecessary as the data hardly every changes.

Is there a way to load the datatable once in memory when the application opens and then use it over and over in a form?

Please explain the basic mechanics of how this would work.

Answer 1

You can use shared variable/objects for holding the data in winforms.

Answer 2

Make your datatable as a property at your main form like that :

private DataTable _dataSource;
private DataTable DataSource
{
  get
  {
    if (_dataSource == null)
    {
      // Fill your _dataSource
    }

    return _dataSource;
  }
}

And use this property to bind your dropdownlist. _dataSource will be a global variable that stands with the main form instance.

Answer 3

I'm using static class for this case:

public static class OraProvider
    {
        private static Dictionary<string, DataTable> _cachedDateTable; 
        ...
        public static DataTable GetTable(string Table, bool cache)
        {
            if (cache && _cachedDateTable.ContainsKey(Table))
            {
                return _cachedDateTable[Table];
            }
            if (Connection.State == ConnectionState.Open)
            {
                DataTable dt = new DataTable(Table);

                commonCommand.CommandText = "SELECT * FROM " + Table;
                commonCommand.Connection = Connection;
                commonDataAdapter.Fill(dt);
                if (cache)
                {
                    _cachedDateTable.Add(Table, dt);
                }
                else
                {
                    if (_cachedDateTable.ContainsKey(Table))
                    {
                        _cachedDateTable.Remove(Table);
                        _cachedDateTable.Add(Table, dt);
                    }
                }
                return dt;
            }
            else
            {
                return null;
            }
        }
...
}

Answer 4

There are two (if not more) possible ways:

One is to add a typed dataset to your project and manually create a table with the respective columns. Then, create code to fill that table and add it as a datasource to your column using a BindingSource.

You can also use an object data source

Declare a class (e.g. MyDataClass) that contains public members (must be a read/write property IIRC. If that doesn't work, create public member variables) for ID and value. Declare a generic list in your form like

private List<MyDataClass> m_dataSource = new List<MyDataClass>();

This list will be used as the data source for the column. Fill the list with one instance of MyDataClass for each value you want to be displayed in the combo box.

Then, create a new object data source in your project for the MyDataClass class. I'm using a German VS2008, so I don't know the exact names of the menu items. It should be "Data > Show DataSources" and in the datasource tool-window: Add new datasource. In the following dialog, select "Object" and select the class MyDataClass.

You will then get a new data source that contains members for the properties declared above. Add a BindingSource to your form and one instance of the object data source. Assign that to the BindingSource (this is so that you can use the designer for selecting fields, etc.). Assign the BindingSource as DataSource for your column and select the ValueMember and DataMember properties accordingly.

Then, in code, after loading the data into the generic list, assign m_dataSource to the BindingSource's DataSource property:

bindingSource.DataSource = m_dataSource;

Please remember that I'm writing this from my memory - there may be more steps involved, but it should work similar to what I've described.