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

Question

我想删除SQLite.Net-PCL包，并想使用SQLite-Net-PCL，因为我后来发现SQLite.Net-PCL没有得到正式维护。

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

现在，sqlite-net-pcl没有InsertOrReplaceAll方法，而只有InsertAll&CCD_ 8方法。Microsoft msdn Link表示，检查主键是否有可用值，并根据该值决定是否必须插入或更新记录。

但是，我有一种情况，在插入或更新对象之前，主键值总是预先设置在List中，并且不想循环检查500多个记录是否存在记录。

在这种情况下，如何一次插入或替换我的所有记录？

考虑以下示例来理解这种情况：

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

我实现了一个与它们的实现类似的扩展方法。您可以将其与SQLite.Net-PCL项目中的原始版本进行比较。

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

在这种情况下，如何同时插入或替换我的所有记录？

有两种方法可以解决问题L:

1. 遍历记录列表，并对列表中的每个对象使用conn.InsertOrReplace(object)：

   for(Enteremployee employees){连接插入或替换(emp)；}

2. 使用Sql命令插入或替换记录(需要构造命令字符串)：

   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

刚刚基准了4种不同的方式来追加许多项目：

上下文：更新包含5000个项目的产品(每个对象中有7个属性)数据库。

1. 在foreach循环中使用exists(通过PK上的countasync)、insertasync和updateasync：大约30秒
2. 使用foreach循环和InsertOrReplaceAsync：大约2分钟
3. 使用@Genfood提出的扩展：约92ms
4. 使用@Elvis Xia-MSFT提出的手动查询：约81ms

我刚刚对扩展进行了一些修改，以管理异步方法的使用

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