Bulk Operations in SQLite and C# with Transaction

When populating a SQLite database for your mobile (or other) app, individual inserts can consume hours. Cut it down to seconds using the SQLiteTransaction class.

Here is an example:

using System.Data.SQLite;
using (var connection = new SQLiteConnection("db.sqlite;Version=3"))
{
  connection.Open();
  using (var command = new SQLiteCommand(connection))
  {
    using (var transaction = connection.BeginTransaction())
    {
      for (int x=0; x<10000000; x++)
      {
        command.CommandText = String.Format(
          "INSERT INTO MyTable (MyStringField, MyIntField) VALUES ('{0}', {1})",
          x.ToString(), x);
        command.ExecuteNonQuery();
      }
      transaction.Commit();
    }
  }
  connection.Close();
}

Below is the same functionality without transactions, and therefore much slower:

using System.Data.SQLite;
using (var connection = new SQLiteConnection("db.sqlite;Version=3"))
{
  connection.Open();
  using (var command = new SQLiteCommand(connection))
  {
    for (int x=0; x<10000000; x++)
    {
      command.CommandText = String.Format(
        "INSERT INTO MyTable (MyStringField, MyIntField) VALUES ('{0}', {1})",
        x.ToString(), x);
      command.ExecuteNonQuery();
    }
  }
  connection.Close();
}
Tagged on: ,

5 thoughts on “Bulk Operations in SQLite and C# with Transaction

  1. Jun Du

    Thanks for sharing.

    Somehow only the first commit takes effect. I see the data committed first time, but all consequent commits have no effect. I need to run the code many times, controlled by a timer.

    Any suggestions?

  2. CTCCoco

    You don’t need to close the connection at the end, the Dispose method called from the “using” statement will close it automatically for you.

  3. Henry T

    Thanks for clearing up my frustration. – I spent quite a time trying to figure this out – and then found this. – Makes a lot of sense really when you think about it.

Leave a Reply

Your email address will not be published. Required fields are marked *