Microsoft.Data.Sqlite SqliteTransaction question

Question

Hi,

I'm experimenting with converting some Sqlite code to
Microsoft.Data.Sqlite, including this:

        using (SqliteTransaction transaction = con.BeginTransaction())
        {
            //for (int j = 0; j < _totalItems; j++)
            //{

            using (SqliteCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = @"INSERT INTO ProductData (ProductId, ProductName, OrderDate, OrderNumber, Unused1, Unused2, DimReady)
                                         VALUES (@productId, @productName, @orderDate, @orderNumber, @unused1, @unused2, @dimReady)";

                for (int j = 0; j < _totalItems; j++)
                {
                    cmd.Parameters.Add(new SqliteParameter("@productId", _dataFields[j][0]));
                    cmd.Parameters.Add(new SqliteParameter("@productName", _dataFields[j][1]));
                    cmd.Parameters.Add(new SqliteParameter("@orderDate", _dataFields[j][2]));
                    cmd.Parameters.Add(new SqliteParameter("@orderNumber", _dataFields[j][3]));
                    cmd.Parameters.Add(new SqliteParameter("@unused1", _dataFields[j][4]));
                    cmd.Parameters.Add(new SqliteParameter("@unused2", _dataFields[j][5]));
                    cmd.Parameters.Add(new SqliteParameter("@dimReady", _dataFields[j][6]));

                    cmd.ExecuteNonQuery();
                }
            }

            transaction.Commit();
            con.Close();
        }

I get an error message if I use it like it is though. If however I
move the "for" iterator up as shown (commented out), it works, for
whatever reason. I'm not sure though if that's the best way to do it

• I'm thinking about performance here, and if there may be a better
  solution? It's reasonably fast though, 18.000 rows are inserted in a
  few seconds.

Thanks,
Rich

Answer 1

You keep adding the same parameters to the cmd. I would clear them after you execute the query. Try changing your code to this

         using (SqliteCommand cmd = con.CreateCommand())
         {
             cmd.CommandText = @"INSERT INTO ProductData (ProductId, ProductName, OrderDate, OrderNumber, Unused1, Unused2, DimReady)
                                      VALUES (@productId, @productName, @orderDate, @orderNumber, @unused1, @unused2, @dimReady)";
             for (int j = 0; j < _totalItems; j++)
             {
                 cmd.Parameters.Add(new SqliteParameter("@productId", _dataFields[j][0]));
                 cmd.Parameters.Add(new SqliteParameter("@productName", _dataFields[j][1]));
                 cmd.Parameters.Add(new SqliteParameter("@orderDate", _dataFields[j][2]));
                 cmd.Parameters.Add(new SqliteParameter("@orderNumber", _dataFields[j][3]));
                 cmd.Parameters.Add(new SqliteParameter("@unused1", _dataFields[j][4]));
                 cmd.Parameters.Add(new SqliteParameter("@unused2", _dataFields[j][5]));
                 cmd.Parameters.Add(new SqliteParameter("@dimReady", _dataFields[j][6]));
                 cmd.ExecuteNonQuery();
                 cmd.Parameters.Clear();
             }
         }