lundi 4 avril 2016

SQLite C#-bulk update or insert

We are using SQLite in our store app to store data locally. The issue is with sthe bulk insert or update of data and I want to know if there is a better way to do this. The list of object returned via service call contains other objects which are inserted (or updated) in local db. After this the main db is updated. This is the code:-

                 foreach (Worker wo in list)
                    {
                        await Task.WhenAll(
                             ProcessTasks(wo),
                            ProcessHolidays(wo),
                            ProcessWages(wo),
                            ProcessWorkComments(wo));
                     }

Below is one of the 'Process' method. There is no uniqueid for nested objects so need delete all rows first and then insert again.

public async Task ProcessWorkComments(Worker wo)
        {
            var db = new SQLiteAsyncConnection(Constants.LocalMainDbPath);
            var comments_tobedeleted = await GetCommentsAsync(wo.Number);

             //no deleteall method exist, delete one by one
            foreach (var dc in comments_tobedeleted)
            {
                await db.DeleteAsync(dc);
            }

              // insert all
            await db.InsertAllAsync(comments);
        }

After processing the nested objects the code processes the main objects which do have a primary key, the code just updates (if item exist) or inserts the object (if does not exist). This is done by iterating all the list object because there is no InsertorUpdateAllmethod.

 foreach (Worker item in list)
                    {
               var existingwo = await db.FindAsync<Worker>(w => w.Number == item.Number);

                        if (existingwo != null)
                        {
                            var ctr = await db.UpdateAsync(item);
                        }
                        else 
                            await db.InsertAsync(item);
            }

All the process takes a lot of time in case of 1000+ objects. Is there any better way to do this?

Thanks!

Aucun commentaire:

Enregistrer un commentaire