Bulk Insert Into SQLite in C#

In this tutorial I’ll show you How to make your SQLite bulk inserts in C#. This is the fastest way to insert millions of records in SQLite Database.

In SQLite if you insert one record at a time then they are wrapped in individual transactions. If you want to insert a lot of records, you should insert them (ex: 20K inserts) in single SQLite transaction.

For adding records, lets open Visual Studio and start coding.

Open Visual Studio and create a C# Console Application. File / New / Project -> C# / C# Console Application.

Right click on the “Soliton Name” in the Soliton Explorer. Soliton explorer is on the right handside on the Visual Studio. If you can’t see Soliton explorer , click on the View Menu and you will see Solution Explorer in the View Menu.

After right clicking the Solution Name, click Manage Nuget Package… as in the following

Find the SQLite into Nuget packages and click install button.

Here are the sample codes:


    class Program
    {
       
        static void Main(string[] args)
        {
            SQLiteConnection con;
            SQLiteCommand cmd;
            SQLiteDataReader dr;
            /*creating or openning database*/
            con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
            string sql = @"CREATE TABLE IF NOT EXISTS Student(
                               ID INTEGER PRIMARY KEY AUTOINCREMENT ,
                               FirstName           TEXT      NOT NULL,
                               LastName            TEXT       NOT NULL
                            );";
                
                con.Open();
                cmd = new SQLiteCommand(sql, con);
                cmd.ExecuteNonQuery();


            /* 1 Million Records Added into SQLite Database*/
            var stopwatch = new Stopwatch();
              stopwatch.Start();
              cmd = new SQLiteCommand();


              using (cmd = new SQLiteCommand(con))
              {
                  using (var transaction = con.BeginTransaction())
                  {
                      for (var i = 0; i < 1000000; i++)
                      {
                        cmd.CommandText = "insert into Student(FirstName,LastName) values ('John','Doe')";
                        cmd.ExecuteNonQuery();
                      }

                      transaction.Commit();
                  }
              }
           Console.WriteLine("****** 1 Million Records Added into SQLite Database *****");
           Console.WriteLine("{0} seconds with one transaction.", stopwatch.Elapsed.TotalSeconds);
              
            con.Close();

            Console.ReadLine();

        }
    }

class Program

{

static void Main(string[] args)

{

SQLiteConnection con;

SQLiteCommand cmd;

SQLiteDataReader dr;

/*creating or openning database*/

con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");

string sql = @"CREATE TABLE IF NOT EXISTS Student(

ID INTEGER PRIMARY KEY AUTOINCREMENT ,

FirstName TEXT NOT NULL,

LastName TEXT NOT NULL

);";

con.Open();

cmd = new SQLiteCommand(sql, con);

cmd.ExecuteNonQuery();

/* 1 Million Records Added into SQLite Database*/

var stopwatch = new Stopwatch();

stopwatch.Start();

cmd = new SQLiteCommand();

using (cmd = new SQLiteCommand(con))

{

using (var transaction = con.BeginTransaction())

{

for (var i = 0; i < 1000000; i++)

{

cmd.CommandText = "insert into Student(FirstName,LastName) values ('John','Doe')";

cmd.ExecuteNonQuery();

}

transaction.Commit();

}

Console.WriteLine("****** 1 Million Records Added into SQLite Database *****");

Console.WriteLine("{0} seconds with one transaction.", stopwatch.Elapsed.TotalSeconds);

con.Close();

Console.ReadLine();

}

Output:

Bulk Insert Into SQLite in C#

Like this:

Leave a Comment X

Like this:

You may also like

Leave a Comment X