Gokul's Blog

Executing 100000 DB Inserts (Sqlite)

Leave a comment

Open the csv file via System.data.OleDb
Open the sqlite database via System.Data.SQLite
Begin a transaction on the sqlite connection
loop over the rows in the csv, inserting them into the sqlite tables
Commit the transaction

Which code below is faster

Code 1:

     using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
      {
        int n;

        for (n = 0; n < 100000; n ++)
        {
          mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1);
          mycommand.ExecuteNonQuery();
        }
      }

Code 2:

 using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
      {
        using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
        {
          SQLiteParameter myparam = new SQLiteParameter();
          int n;

          mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
          mycommand.Parameters.Add(myparam);

          for (n = 0; n < 100000; n ++)
          {
            myparam.Value = n + 1;
            mycommand.ExecuteNonQuery();
          }
        }
        mytransaction.Commit();
      }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s