While working in a project around a year back, I was facing trouble while downloading data from a SQL server to sqlite database. The SQL server DB contained millions of records (CAd/CAM refernece data) in some table that I needed to download to the SQLite DB.In fact the records were inserted to the SQL server from some other SQLite DB (using SQLBulkCopy, which was amazingly fast).
But the sad point was that the SQlite provider didn’t offer any functionalities to bulk copy large amount of data into SQlite DB. I tweaked the code many ways, but everything was in vain . Copying all data from SQL server to Sqlite tables was taking around 9-10 minutes.
Finally, I adopted some simple mechanisms and I was in the 5th gear . Copying same number of records took just 1-2 minutes .
Here is the code snippet for your reference :
(though it uses SQLite, you can also try the same for anyother db)
[Each time I was retrieving a fixed amount of record from SQL server and using this code]
using (SQLiteTransaction dbTransaction = dbConnection.BeginTransaction())
using (System.Data.SQLite.SQLiteDataAdapter dbAdapter = new SQLiteDataAdapter())
using (SQLiteCommand dbCommand = dbConnection.CreateCommand())
dbCommand.Transaction = dbTransaction;
dbCommand.CommandText = "Select * from " + sqliteTableName + " Where 1 = 2";
dbAdapter.SelectCommand = dbCommand;
using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder())
//set the data adapter property
cb.DataAdapter = dbAdapter;
using (dbAdapter.InsertCommand = (SQLiteCommand)((ICloneable)cb.GetInsertCommand()).Clone())
// unregister the
cb.DataAdapter = null;
foreach (DataRow dbRow in dbTable.Rows)
if (dbConnection.State != ConnectionState.Closed)
The code snippet may not be that much of interset to you, but here are the key points to rememeber :
1. Always use USING clause during db operation
2. Use TRANSACTION object while you make large amount of data transfer
3. Try to save the insert / update command of command builder and reuse it (as using CommandBuilder may hinder the performance)