InsertAll & UpdateAll in sqlite-net-pcl VS InsertOrReplaceAll in SQLite.Net-PCL

Question

I want to remove SQLite.Net-PCL package and want to use sqlite-net-pcl because I later found that SQLite.Net-PCL is not officially being maintained.

I have tables which stores GUID as primary key of string type in my Xamarin project. I have List of records coming from server and currently using InsertOrReplaceAll method to insert new records and update existing one, all at once based on my GUID primary key.

Now, sqlite-net-pcl does not have InsertOrReplaceAll method but instead it has only InsertAll & UpdateAll methods. Microsoft msdn Link says checking if primary key has value available or not and based on that decide if Records have to be inserted or updated.

But, I have a case where primary key value is always pre-set in the List, before insert or update the object and don't want to make a loop to check if record exists or not for more than 500 records.

How to Insert Or Replace all my records at once in this case?

Consider following Example to understand this scenario:

using (var conn = new DBConnectionService().GetConnection())
{       
    List<ENTEmployee> employees = new List<ENTEmployee>()
    {
        new ENTEmployee(){ Id = "b977ec04-3bd7-4691-b4eb-ef47ed6796fd", FullName = "AAA BBB", Salary = 15000 },
        new ENTEmployee(){ Id = "c670a3e2-b13f-42b3-849c-fd792ebfd103", FullName = "BBB BBB", Salary = 16000 },
        new ENTEmployee(){ Id = "d961c33c-0244-48dc-8e10-f4f012386eb6", FullName = "CCC BBB", Salary = 17000 },
        new ENTEmployee(){ Id = "35be4508-ff93-4be8-983f-d4908bcc592d", FullName = "DDD BBB", Salary = 18000 },
        new ENTEmployee(){ Id = "0875549c-d06c-4983-b89a-edf81b6aa70d", FullName = "EEE BBB", Salary = 19000 },
    };

    var insertResult = conn.InsertAll(employees);

    //Updated Record
    employees[0].FullName = "AAA Updated";
    employees[0].Salary = 12300;


    //New Records
    employees.Add(new ENTEmployee() { Id = "87f48ecf-715c-4327-9ef3-11712ba4a120", FullName = "FFF BBB", Salary = 20000 });
    employees.Add(new ENTEmployee() { Id = "85f53888-b1e9-460c-8d79-88010f143bcf", FullName = "GGG BBB", Salary = 21000 });

    //Now here, 
    //How to decide which records to be inserted and which records to be updated for List employees?
}

Answer 1

I implemented an extension method which is similar to their implementation. You could compere it with the original one in the SQLite.Net-PCL project.

static public class SQLiteConnectionExtensions
{
    /// <summary>
    ///     Inserts all specified objects.
    ///     For each insertion, if a UNIQUE
    ///     constraint violation occurs with
    ///     some pre-existing object, this function
    ///     deletes the old object.
    /// </summary>
    /// <param name="objects">
    ///     An <see cref="IEnumerable" /> of the objects to insert or replace.
    /// </param>
    /// <returns>
    ///     The total number of rows modified.
    /// </returns>
    static public int InsertOrReplaceAll(this SQLiteConnection connection, IEnumerable objects, bool runInTransaction = true)
    {
        var c = 0;
        if (objects == null)
            return c;

        if (runInTransaction)
        {
            connection.RunInTransaction(() =>
            {
                foreach (var r in objects)
                {
                    c += connection.Insert(r, "OR REPLACE", Orm.GetType(r));
                }
            });
        }
        else
        {
            foreach (var r in objects)
            {
                c += connection.Insert(r, "OR REPLACE", Orm.GetType(r));
            }
        }

        return c;
    }
}

Answer 2

How to Insert Or Replace all my records at once in this case?

There are two ways to resolve the problemL:

1. Traverse your records list and use conn.InsertOrReplace(object) for every object in your list:

   for(ENTEmployee emp in employees) { conn.InsertOrReplace(emp); }

2. Use Sql command to insert or replace the records(need your to construct the command string):

   string cmd = @"insert or replace into employ(id,FullName,salary) 
                 values('c670a3e2-b13f-42b3-849c-fd792ebfd103', 'fullname1',32),
                       ('d961c33c-0244-48dc-8e10-f4f012386eb6', 'fullname2',23);";
   conn.Execute(cmd);
   

Answer 3

Just benchmarked 4 different ways to upsert many items :

Context : updating a product (with 7 properties in each object) database with 5000 items.

1. using exists (through a countasync on PK), insertasync and updateasync in a foreach loop : about 30 seconds
2. using a foreach loop and InsertOrReplaceAsync : about 2 minutes
3. using the extension proposed by @Genfood : about 92ms
4. using the manual query proposed by @Elvis Xia - MSFT : about 81ms

I just made some modifications to the extension to manage the use of async methods

public static class SqliteExtensions
{
    static async public Task<int> InsertOrReplaceAll(this SQLiteAsyncConnection connection, IEnumerable objects, bool runInTransaction = true)
    {
        var c = 0;
        if (objects == null)
            return c;

        if (runInTransaction)
        {
            await connection.RunInTransactionAsync(nonAsyncConnection =>
            {
                foreach (var r in objects)
                {
                    c += nonAsyncConnection.Insert(r, "OR REPLACE", Orm.GetType(r));
                }
            });
        }
        else
        {
            foreach (var r in objects)
            {
                c += await connection.InsertAsync(r, "OR REPLACE", Orm.GetType(r));
            }
        }

        return c;
    }
}