Use transaction to batch updates to SQLite

Question

I get an error using Microsoft.Data.Sqlite to add products to a SQLite database in a loop. The error:

Must add values for the following parameters: @ItemCode, @ItemCost, @ItemDescription, @LocationId, @ReorderStockLevel, @ReservedStockLevel, @StockLevel, @StockIt, @SupplierId

In my code I try to follow the examples at Bulk Insert in Microsoft.Data.Sqlite and Bulk insert

using (SqliteConnection con = new(DataService.SqliteConnectionString))
{
    con.Open();
    using (SqliteTransaction transaction = con.BeginTransaction())
    {
        using (SqliteCommand cmd = con.CreateCommand())
        {
            cmd.CommandText =
                    $"INSERT INTO Products 
                        (
                        ItemCode, 
                        ItemCost, 
                        ItemDescription, 
                        LocationId, 
                        ReorderStockLevel, 
                        ReservedStockLevel, 
                        StockLevel, 
                        StockIt, 
                        SupplierId
                        ) 
                    VALUES 
                        (
                        @ItemCode, 
                        @ItemCost, 
                        @ItemDescription, 
                        @LocationId, 
                        @ReorderStockLevel, 
                        @ReservedStockLevel, 
                        @StockLevel, 
                        @StockIt, 
                        @SupplierId
                        )";

            SqliteParameter itemCodeParam = cmd.CreateParameter();
            itemCodeParam.ParameterName = "$ItemCode";
            cmd.Parameters.Add(itemCodeParam);

            SqliteParameter itemCostParam = cmd.CreateParameter();
            itemCostParam.ParameterName = "$ItemCost";
            cmd.Parameters.Add(itemCostParam);

            SqliteParameter itemDescriptionParam = cmd.CreateParameter();
            itemDescriptionParam.ParameterName = "$ItemDescription";
            cmd.Parameters.Add(itemDescriptionParam);

            SqliteParameter locationIdParam = cmd.CreateParameter();
            locationIdParam.ParameterName = "$LocationId";
            cmd.Parameters.Add(locationIdParam);

            SqliteParameter reorderStockLevelParam = cmd.CreateParameter();
            reorderStockLevelParam.ParameterName = "$ReorderStockLevel";
            cmd.Parameters.Add(reorderStockLevelParam);

            SqliteParameter reservedStockLevelParam = cmd.CreateParameter();
            reservedStockLevelParam.ParameterName = "$ReservedStockLevel";
            cmd.Parameters.Add(reservedStockLevelParam);

            SqliteParameter stockLevelParam = cmd.CreateParameter();
            stockLevelParam.ParameterName = "$StockLevel";
            cmd.Parameters.Add(stockLevelParam);

            SqliteParameter stockItParam = cmd.CreateParameter();
            stockItParam.ParameterName = "$StockIt";
            cmd.Parameters.Add(stockItParam);

            SqliteParameter supplierIdParam = cmd.CreateParameter();
            supplierIdParam.ParameterName = "$SupplierId";
            cmd.Parameters.Add(supplierIdParam);

            foreach (Product newItem in newItems)
            {
                itemCodeParam.Value = newItem.ItemCode;
                itemCostParam.Value = newItem.ItemCost;
                itemDescriptionParam.Value = newItem.ItemDescription;
                locationIdParam.Value = newItem.LocationId;
                reorderStockLevelParam.Value = newItem.ReorderStockLevel;
                reservedStockLevelParam.Value = newItem.ReservedStockLevel;
                stockItParam.Value = newItem.StockIt;
                stockLevelParam.Value = newItem.StockLevel;
                supplierIdParam.Value = newItem.SupplierId;
                cmd.ExecuteNonQuery();
            }
            cmd.Prepare();
            transaction.Commit();            
        }
    }
}

The first call to cmd.ExecuteNonQuery() fires. The exception throws as foreach (Product newItem in newItems) is entered for the second iteraton .The same exception throws with and without cmd.Prepare()

No products are entered into the database. The database connection configuration is correct as products can be entered in the database row at a time.

How to I update a list of items in a transaction?

The insert `sql` ends with two close parenthesis! `INSERT INTO .... ))`. Check that out — dr.null, Jan 04 '21 at 00:36
@dr.null thank you for bringing the double parenthesis to my attention. That was a typo when writing the question. I've edited the sql to hopefully make it more legible. — Vague, Jan 04 '21 at 01:43
Not sure if relevant, but cmd.Prepare() should be called once, outside the loop. — David Browne - Microsoft, Jan 04 '21 at 01:49
@DavidBrowne-Microsoft Thank for the advice. I've moved `cmd.Prepare` outside the 'foreach' loop. The error is the same — Vague, Jan 04 '21 at 02:09
@DavidBrowne-Microsoft please propose replacing `@` with `$` so I can accept. — Vague, Jan 04 '21 at 06:26

Use transaction to batch updates to SQLite

0 Answers0