CODEWITHSUNDEEP
.netvisual-studiosqlite
dban10
dban10

Reputation:

Bulk insertion into SQLite.Net database

I need to perform bulk insetion into a sqlite database in visual studio. Does anybody know how to do this?

Upvotes: 4

Views: 6133

Answers (6)

Brent Keller
Brent Keller

Reputation: 1435

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.

Upvotes: 4

Liron Levi
Liron Levi

Reputation: 1169

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.

Upvotes: 7

محمد الاشرف أنور
محمد الاشرف أنور

Reputation: 300

    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();

    }

Upvotes: 1

JP Richardson
JP Richardson

Reputation: 39395

I wrote a class to help facilitate bulk inserts in SQLite. Hopefully it's helpful:

http://procbits.com/2009/09/08/sqlite-bulk-insert/

-JP

Upvotes: 1

Emmanuel
Emmanuel

Reputation: 1531

I needed to do the same thing, I found this answer on the Sqlite.Net forum, might be of interest for someone.

http://sqlite.phxsoftware.com/forums/t/134.aspx

Upvotes: 0

Mike Trpcic
Mike Trpcic

Reputation: 25659

You can just write a regular insert statement and loop through your dataset, inserting the relevant data into the query string before running it against the database.

Upvotes: 1

Related Questions