Copy data from a DataTable to SQLite table

[sun21170]

I have a DataTable with about 10K records that is populated from SQL Server.
I would like to populate a SQLite table that has the exact same structure as the DataTable.

Is that possible with dotConnect SQLite and if yes, then what components/features would be helpful?
I would prefer the insert into SQLite table to happen in a batch i.e. many records inserted in same database trip to SQLite rather than one record inserted per database trip.

[Pinturiccio]

SQLite API does not provide special functions for bulk insert. You can specify several insert statements (for example 500), separated by semicolon, in SQLiteCommand.Text, and they will be executed faster than when you execute them one-by-one.

Besides, a transaction is autocommitted each time SQliteCommand is executed. Each commit writes to the database file, which slows down performance. You can avoid this by opening a transaction explicitely before executing INSERT statements, execute all of them, and then commit the transaction. In this case there is only one write operation to the database file, which can increase performance substantially.
We also advise you to check the version of your sqlite3.dll library. Its performance substantially improved since the 3.9 version. If you have an older sqlite3.dll version, you can install the latest dotConnect for SQLite version, which installs the latest sqlite3.dll library. Also, we can send you the required sqlite3.dll library.
This can help you to improve application performance with your task.

[sun21170]

You mean just add begin and commit transaction before and after the INSERT statements like below, or you mean to call BeginTransaction and Commit methods on Connection object in ADO.Net?

Approach 1

Code: Select all

BEGIN TRANSACTION;
INSERT into table1 (FullName,Age, City) values ('abc', 40, 'ukraine');
INSERT into table1 (FullName,Age,City) values ('xyz', 25, 'kiev');
INSERT into table1 (FullName, Age, City) values ('amn', 30, 'dallas');
COMMIT TRANSACTION;

Approach 2

Code: Select all

 // Start a local transaction 
  myTrans = sqConnection.BeginTransaction(); 
  // Assign transaction object for a pending local transaction 
  sqCommand.Transaction = myTrans; 
  try 
  { 
    sqCommand.CommandText = "INSERT into table1 (FullName,Age, City) values ('abc', 40, 'ukraine');INSERT into table1 (FullName,Age,City) values ('xyz', 25, 'kiev'); INSERT into table1 (FullName, Age, City) values ('amn', 30, 'dallas');"; 
    sqCommand.ExecuteNonQuery(); 
    myTrans.Commit(); 
   } 
  catch(Exception e) 
  { 
    myTrans.Rollback(); 
  } 
  finally 
  { 
    sqConnection.Close(); 
  } 
 

[Pinturiccio]

We mean the second approach when the BeginTransaction and Commit methods are called on the connection object in ADO.NET.