SQLite Used to Populate a ListView in WPF C#

Question

I have an SQLite database with over 100,000 records. I need the data to fill a ListView in my WPF project. I'm using System.Data.SQLite. What's the (best) way to get this setup so it works using virtualization?

Also, what's the best method to filter the list view based on key word searches? I'm aiming for maximum speed.

Answer 1

It's a bad approach to populate with 100k items.

No one will ever scroll that list.

Instead display last 100 used, and have a filter. They will definitely use the filter to lookup a value and not scroll the list.

Have an index on the filter columns, and for string data allow filtering only from the beginning of the word so the indexes should work, as they won't work if you do a LIKE search, so it will be slower.

Answer 2

Here's how I did it:

SQLiteConnection sql_con = new SQLiteConnection("data source=YOUR DATA SOURCE");
sql_con.Open();
SQLiteCommand sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = "SELECT * FROM table_name";
SQLiteDataAdapter DB = new SQLiteDataAdapter(sql_cmd.CommandText, sql_con);
DataSet DS = new DataSet();
DB.Fill(DS);
YourListView.DataContext = DS.Tables[0].DefaultView;

Gotta make sure your ListView XAML is setup with the appropriate data binding as well, otherwise the ListView won't populate.