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;
    }
}