当使用参数和事务时，SQLite不插入多于一行

Question

我在Windows UWP应用程序中使用SQlite有一个奇怪的现象。

我正在尝试使用事务进行批量插入，但我只在数据库中插入一行，或者出现"必须为以下参数添加值"的错误消息:"但是在错误消息中没有显示任何参数，并且我的所有参数都包含有效值。

下面是代码

{
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, _dbName);
using (SqliteConnection db = new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
using (var insertCommand = db.CreateCommand())
{
using (var insertTransaction = db.BeginTransaction())
{
insertCommand.Transaction = insertTransaction

insertCommand.CommandText = $"INSERT INTO {tableName} (Id) VALUES (@Id)";
foreach (var aktor in aktors)
{

insertCommand.Parameters.AddWithValue("@id", aktor.Id);
await insertCommand.ExecuteNonQueryAsync();
}

insertTransaction.Commit();
}
}
db.Close();
}
return true;
}

Answer 1

每次向现有参数添加新参数时，代码中都会出现错误。试试这个

insertCommand.Parameters.AddWithValue("@id", 0);
foreach (var aktor in aktors)
{
insertCommand.Parameters[0].Value = aktor.Id;
// or
insertCommand.Parameters["@id"].Value = actor.Id;
await insertCommand.ExecuteNonQueryAsync();
}

Answer 2

对循环中的每项执行INSERT命令，然后提交:

{
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, _dbName);
using (SqliteConnection db = new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
using (var insertTransaction = db.BeginTransaction())
{
foreach (var aktor in aktors)
{
using (var insertCommand = db.CreateCommand())
{
insertCommand.Transaction = insertTransaction;
insertCommand.CommandText = $"INSERT INTO {tableName} (Id) VALUES (@Id)";
insertCommand.Parameters.AddWithValue("@id", aktor.Id);
await insertCommand.ExecuteNonQueryAsync();
}
}
insertTransaction.Commit();
}
db.Close();
}
return true;
}