1

I have data in a SQLite database table that I want to show in a datagridview. I'm able to use a BindingSource and load the data in a DataTable and fill my datagridview (and binding navigator) without any problem on tables which are sufficiently small (i.e. it fits in the available memory).

But I also have a copy of the database where the prerequisite (it fits in memory) no longer is true. And I still want to use my datagrieview bindingsource etc.

The DataGridView does have a virtual mode for loading only the records that are displayed dan Microsoft gives a nice example for cashing records in https://msdn.microsoft.com/en-us/library/ms171624(v=vs.100).aspx Got that working but it disconnects the datagridview from the bindingsource and I no longer can use the binding navigator or other niceties that can be connected to the binding source.

A DataTable, as far as I understand, a connectionless copy of the data. And because it is connectionless it needs all the data. This is where my problem is. All the data does not fit in memory. I had a look at using the ideas for cashing and just-in-time data loading from the microsoft example but don't see an easy way of implementing this for DataTables.

Question: Does an object (or method) exist to create a cashed or buffered DataTable that can be used with a BindingSource?

Does anybody have suggestions/directions how to tacle this in a clean and easy way?

Kind regard.

PapaAtHome
  • 575
  • 8
  • 25

1 Answers1

0

Here is what I did:

I created a Cache class that implements the IList interface. The cache is realised using the ideas from the microsoft example to read the database. And the cache is accessable as a List, wich is what is needed for the BindingSource DataSource attibute.

According to the microsoft documentation

The DataSource property can be set to a number of data sources, including types, objects, and lists of types. The resulting data source will be exposed as a list.

Does that mean that a private copy is helt for each record in the original list? That needs testing. If so, my idea will fail.

My hope is that the Binding source is using some smart lookup algorithm and does not copy all content. I think that the copy scenario is unlikely, for instance, it will maken insert and delete management a hell.

First tests look promissing.

But if someone has a better idea for this...

Kind regards.

PapaAtHome
  • 575
  • 8
  • 25
  • Anybody who will give this approach a try should also have a look at http://stackoverflow.com/questions/3325515/sqlite-limit-offset-query – PapaAtHome Feb 19 '16 at 08:37