插入全部 &SQLite.Net-PCL 中 sqlite-net-pcl 中的 UpdateAll VS InsertOrReplaceAll [英] InsertAll & UpdateAll in sqlite-net-pcl VS InsertOrReplaceAll in SQLite.Net-PCL

问题描述

我想删除 SQLite.Net-PCL 包并且想要使用 sqlite-net-pcl 因为我后来发现 SQLite.Net-PCL 未正式维护.

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.

我有将 GUID 存储为 Xamarin 项目中字符串类型主键的表.我有来自服务器的记录列表，目前使用 InserOrReplaceAll 方法插入新记录并更新现有记录，所有这些都基于我的 GUID 主键.

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 InserOrReplaceAll method to insert new records and update existing one, all at once based on my GUID primary key.

现在，sqlite-net-pcl 没有 InsertOrReplaceAll 方法，而是只有 InsertAll &UpdateAll 方法.Microsoft msdn Link 说检查主键是否具有可用值，并且基于此决定是否必须插入或更新记录.

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.

但是，在插入或更新对象之前，我总是在 List 中预先设置主键值，并且不想进行循环以检查记录是否存在或不超过 500 条记录.

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

推荐答案

我实现了一个扩展方法，类似于他们的实现.您可以将它与 SQLite.Net-PCL 项目.

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

这篇关于插入全部 &SQLite.Net-PCL 中 sqlite-net-pcl 中的 UpdateAll VS InsertOrReplaceAll的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！