Page 1 of 1
Copy data from a DataTable to SQLite table
Posted: Fri 04 Dec 2015 15:33
by 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.
Re: Copy data from a DataTable to SQLite table
Posted: Mon 07 Dec 2015 15:40
by 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.
Re: Copy data from a DataTable to SQLite table
Posted: Mon 07 Dec 2015 20:03
by 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;
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();
}
Re: Copy data from a DataTable to SQLite table
Posted: Tue 08 Dec 2015 09:55
by Pinturiccio
We mean the second approach when the BeginTransaction and Commit methods are called on the connection object in ADO.NET.