dataGridView in virtual mode

[Sakyamuni]

Hi Everyone!
Im developing an application which uses dataGridView to (only) show the contents of tables from SQLite databases. Tables can grow to 1M+ records, so it is quite painful to wait for the long load times. Recently I read an article suggesting the use of virtual mode in dataGridView and cache the selects from the database. I can't seem to get it working, because I need to set the height of dataGridView vertical scrollbar, which is not permitted once the datasource is defined. I am thinking of using the sql LIMIT statement to select only partial data from tables and only show, what the user needs to see. Any advice on the issue ? Here is the code :

string sql = "SELECT rowid, length,sec,usec,sip,dip,protocol,sport,dport,packet_id FROM "+tableName;
using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + PathToDatabase))
{
using (SQLiteDataAdapter da = new SQLiteDataAdapter(sql, conn))
{
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
}

Thanks for any advice!

[nelo]

What you could do is to retrieve the data in batches in a background thread and update the data grid as data comes in. On another note do the users need to see all 1M+ records at the same time? I understand that it could be useful to have that data in memory but how will a user process 1M+ records in a single user interface? Anyway... you could implement some paging mechanism that would show different subsets of the data.

[Sakyamuni]

I was thinking of handling the scrollbar event and everytime the user scrolls down for more data, a chunk of data from the table would be read and put in the dataGridView. The problem is I can't set the vertical scrollbar height to allow user to scroll down, because the scrollbar automatically adjusts to the number of rows read from the table (dataGridView1.RowCount). Is there any way to bypass this and handle the event ?