Bulk insertion into SQLite.Net database

With SQLite you can do this by opening a transaction, looping through all your data and executing the relevant SQL INSERT commands, and than commit the transaction. This is the fastest way to perform bulk inserts in SQLite.

I'm using the SQLite.Net.Async-PCL nuget package for working with sqlite in my UWP app and I found that the InsertAllAsync method is really fast. It was able to insert almost 33,000 rows into a table in less than a second where it took almost 10 minutes when I used a loop and just called InsertAsync.

I checked the source and it is using a transaction for the InsertAll method, so it's nice that's all taken care of already and you can just pass in your list of items and not have to jump through any hoops.

    private void prepareConnectionForBulkInsert(SQLiteConnection cn)    {        SQLiteCommand stmt;        stmt = new SQLiteCommand("PRAGMA synchronous=OFF", cn);        stmt.ExecuteNonQuery();        stmt = new SQLiteCommand("PRAGMA count_changes=OFF", cn);        stmt.ExecuteNonQuery();        stmt = new SQLiteCommand("PRAGMA journal_mode=MEMORY", cn);        stmt.ExecuteNonQuery();        stmt = new SQLiteCommand("PRAGMA temp_store=MEMORY", cn);        stmt.ExecuteNonQuery();    }