Fastest Way to extract from SQL Lite in C#

Question

We have a single table (25 columns int, text and datetime) with about a million of recrds, and we need to extract them from a SQL Lite database and then render to a wpf xamDataGrid, What is the fastest way of doing it in C#?

PLUS, what would be the most efficient way of doing it?

Options we have thought about:

• Getting a DataSet

• List of Entities

• DataReader

• Array of CSVs

Thanks in advance.

Answer 1

DataReader is usually the fastest method to connect to a sql database. you can read them in and parse them manually into your own custom object collection.

Entity Framework will give you the best development experience on the application side and the best level of modeling abstraction, but at a performance hit. Entities bind very nicely and easily to WPF UI elements.

A DataSet is easy to set up but will force the relational database abstraction into your application code in a very ugly way. It will force you to reference columns as strings in a non type safe way that will break only at run time, and is generally flimsy and not recommended anymore for any significant projects. (before DataSet fans jump down my throat, this is official guidance from MSFT ecn.channel9.msdn.com/o9/te/NorthAmerica/2010/pptx/DEV324.pptx)

Array of CSVs could be fast but much harder to work with and implement.

I would say if you need top performance go with DataReader, but if you need developer productivity go with Entity Framework.

Answer 2

You can load the data using a background thread and populate the ObservableCollection using the Main thread's Dispatcher, the priority being ContextIdle.

Dispatcher UIDispatcher = Dispatcher.CurrentDispatcher;
BackgroundWorker bw = new BackgroundWorker();
bw.DoWork += (sender,e) =>
{
    // Use a linq query to yield an IQueryable/IEnumerable List of data from DB
    foreach(Data data in DataList)   // Enumerated here
    {
        UIDispatcher.Invoke(DispatcherPriority.ContextIdle, new Action(() => 
        { 
            myOC.Add(data);
        }));
    }    
};