I am developing UWP application.
I have a database that should be initialized with about 20,000 records. The records, that are defined as follows:
private static readonly ObservableCollection<TickRecord> TickRecords = new ObservableCollection<TickRecord>();
I tried to insert the records one at a time like this:
private void CreateFakeTickRecords()
{
if ( Database.Database.CountTickRecords() > 0 )
{
return;
}
foreach ( var tickRecord in TickRecords )
{
Database.Database.AddOrUpdateTickRecord( tickRecord );
}
}
and
public static void AddOrUpdateTickRecord( TickRecord tickRecord )
{
// Create a new connection
using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) )
{
if ( tickRecord.Id == 0 )
{
// New
db.Insert( tickRecord );
}
else
{
// Update
db.Update( tickRecord );
}
}
}
This code works fine, however it is way too slow.
I would like to modify it so I can do a "Bulk Insert".
How do I do that?
Thx
Try the InsertAll and UpdateAll functions. Hopefully this opens up the database table just once and inserts/updates everything at once. You will need to figure out which objects to insert/update ahead of time, but this should still really speed things up.
List<TickRecords> updates = new List<TickRecords>();
List<TickRecords> inserts = new List<TickRecords>();
foreach ( var tickRecord in tickRecords )
{
if ( tickRecord.Id == 0 )
{
updates.Add(tickRecord);
}
else
{
inserts.Add(tickRecords);
}
}
using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) )
{
db.InsertAll(inserts);
db.UpdateAll(updates);
}
You should insert all 20K records in single sqllite transaction.
Something like this:
public static void AddOrUpdateTickRecords( ObservableCollection<TickRecord> tickRecords )
{
// Create a new connection
using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) )
{
db.BeginTransaction();
try
{
foreach ( var tickRecord in tickRecords )
{
if ( tickRecord.Id == 0 )
{
// New
db.Insert( tickRecord );
}
else
{
// Update
db.Update( tickRecord );
}
}
db.Commit();
}
catch ( Exception )
{
db.Rollback();
}
}
}
I modified Bibek answer a bit to match it to UWP:
public static void AddOrUpdateTickRecords( ObservableCollection<TickRecord> tickRecords )
{
// Create a new connection
using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) )
{
db.BeginTransaction();
try
{
foreach ( var tickRecord in tickRecords )
{
if ( tickRecord.Id == 0 )
{
// New
db.Insert( tickRecord );
}
else
{
// Update
db.Update( tickRecord );
}
}
db.Commit();
}
catch ( Exception ex )
{
db.Rollback();
}
}
}