C#, Inserting Multiple Rows Into An SQL Database

Question

I have an Access database. I'm using C# and Visual Studio I'm attempting to insert several rows within a transaction. However, instead of inserting each row, I end up with the first element inserted multiple times.

I can insert the data into the "Invoices" table one row at a time, so the table is in working order. I included the code below. The rows do have varied data. The debugger shows that.

private void insertInvoiceItemsIntoDatabase(int tableNumber)
{
    string category, food;
    double price
    string connectionString = _databaseConnectionString; 
    
    using (OleDbConnection connection =
               new OleDbConnection(connectionString))
    {
        OleDbCommand command = new OleDbCommand();
        OleDbTransaction transaction = null;

        // Set the Connection to the new OleDbConnection.
        command.Connection = connection;

        // Open the connection and execute the transaction.
        try
        {
            connection.Open();

            // Start a local transaction with ReadCommitted isolation level.
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

            command.Connection = connection;
            command.Transaction = transaction;

            command.CommandText = "Insert into Invoices (TableNumber, Category, Food, Price) VALUES (?,?,?,?)";

            command.CommandType = CommandType.Text;

            Order order;
            order = tables[tableNumber - 1].getOrder();
            List<MenuItem> items = order.deepCopyMenuItems();
            for (int i = 0; i < items.Count(); i++)
            {
                FoodType ft =  items[i].getFoodType();
                category = "Main Course";
                if(ft == FoodType.maincourse)
                {
                    category = "Main Course";
                }else if(ft == FoodType.drink)
                {
                    category = "Drink";
                }else if(ft == FoodType.dessert)
                {
                    category = "Dessert";
                }

                food = items[i].getItemName();
                price = items[i].getItemPrice();

                command.Parameters.AddWithValue("TableNumber", tableNumber);
                command.Parameters.AddWithValue("Category", category);
                command.Parameters.AddWithValue("Food", food);
                command.Parameters.AddWithValue("Price", price);
                command.ExecuteNonQuery();
            }
           
            transaction.Commit();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            try
            {
                // Attempt to roll back the transaction.
                transaction.Rollback();
            }
            catch
            {
                // Do nothing here; transaction is not active.
            }
        }
    }
}

You're using a single command and adding the same parameters over and over to it. Just create a new `OleDbCommand` in each iteration of the `for` loop. — Alejandro, Jun 07 '21 at 17:31
Stop using AddWithValue() https://www.dbdelta.com/addwithvalue-is-evil/ — Serg, Jun 07 '21 at 17:37
[SqlCommand Parameters Add vs. AddWithValue](https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue) — , Jun 07 '21 at 17:58
@OlivierRogier Not in this case. Alejandro's solution worked. — John Alway, Jun 07 '21 at 21:06

score 2 · Answer 1 · answered Jun 07 '21 at 17:44

Create params with a proper type before the loop. Set values in loop.

        ...
        command.CommandText = "Insert into Invoices (TableNumber, Category, Food, Price) VALUES (?,?,?,?)";

        command.CommandType = CommandType.Text;
        command.Parameters.Add("TableNumber", /*param type*/);
        command.Parameters.Add("Category", /*param type*/);
        command.Parameters.Add("Food", /*param type*/);
        command.Parameters.Add("Price", /*param type*/);

        Order order;
        order = tables[tableNumber - 1].getOrder();
        List<MenuItem> items = order.deepCopyMenuItems();
        for (int i = 0; i < items.Count(); i++)
        {
            FoodType ft =  items[i].getFoodType();
            category = "Main Course";
            if(ft == FoodType.maincourse)
            {
                category = "Main Course";
            }else if(ft == FoodType.drink)
            {
                category = "Drink";
            }else if(ft == FoodType.dessert)
            {
                category = "Dessert";
            }

            food = items[i].getItemName();
            price = items[i].getItemPrice();

            command.Parameters["TableNumber"].Value = tableNumber;
            command.Parameters["Category"].Value =  category;
            command.Parameters["Food"].Value =  food;
            command.Parameters["Price"].Value =  price;
            command.ExecuteNonQuery();
        }
        ...

Since Alejandro's solution worked for me, I'm using that for now, but I am going to go over these "Add()"s. Thanks for the insight. — John Alway, Jun 07 '21 at 21:13

C#, Inserting Multiple Rows Into An SQL Database

1 Answers1