How to store masses of data offline and then update it into an SQLite database in batch?

Question

Currently, I am trying to fill an SQLite database with tens of thousands of text data using this this method:

SQLiteConnection = new SQLiteConnection(cntnStr);
connection.Open();

foreach(Page p in pages)
{
     using (SQLiteCommand command = new SQLiteCommand(String.Format("Insert Into Pages (ID, Data, Name) Values ({0}, '{1}', '{2}')", id, p.Data, p.Name), connection))
         command.ExecuteNonQuery();
}

However, I suspect that doing this about 10 times per second is probably slowing the whole process down. Is there a way I can collate the data in memory and then add every 5000 records or so into the database in batch (so it is faster)?

EDIT: Super-important: Make sure you perform all your SQL commands within a DbTransaction - in this case an SQLiteTransaction:

SQLiteTransaction trans = connection.BeginTransaction();

// SQL centric code - repeated inserts/changes

trans.Commit(); // adds your changes

It improves performance by 1000x.

Answer 1

You will have to handle escaping the data yourself, but you can use a batch insert.

IE:

Insert Into Pages (ID, Data, Name) Values (...),(...),(...),(...)

Answer 2

Use a parameterized query, instead of building the query using string concatenation :

using (SQLiteConnection = new SQLiteConnection(cntnStr))
{
    connection.Open();

    string query = "Insert Into Pages (ID, Data, Name) Values (?, ?, ?)";
    using (SQLiteCommand command = new SQLiteCommand(query, connection)
    {
        command.Parameters.Add("id", DbType.Int32);
        command.Parameters.Add("data", DbType.String);
        command.Parameters.Add("name", DbType.String);
        foreach(Page p in pages)
        {
             command.Parameters["id"].Value = p.Id;
             command.Parameters["data"].Value = p.Data;
             command.Parameters["name"].Value = p.Name;
             command.ExecuteNonQuery();
        }
    }
}

This will be faster because the DbCommand is only created once, and the query is only parsed once. Also, you avoid the risks of SQL injection due to the string concatenation

BTW, have a look at this article by Robert Simpson (author of the SQLite .NET provider)

Answer 3

You could load the table from the SQLite DB into a DataTable-Object, then insert your records into the DataTable-Object and sync it back every 5000 records to the DB.