SQLite not inserting more than one row when using parameters and transaction-CodePudding

I have a strange phenomena with SQlite in a Windows UWP application.

I'm trying to do a batch insert with transactions but I only gets one row inserted in the database, or an error message saying "Must add values for the following parameters:" but no parameters are shown in the error message and all my parameters are containing valid values.

Here's the code

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

CodePudding user response：

You have a bug in your code, each time you are adding a new paramer to the existing ones. Try this

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

CodePudding user response：

Execute an INSERT command per item in the loop and then commit:

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