Inserting listview items individually into database as new entries in c#

Question

I want each individual item inserted into database as a new entry. Right now, I am only able to insert all items into the database as a single entry. I am trying to add 3 book names in the database with the same borrowerId. can anyone help me? because im a beginner.

here is my code:

foreach (ListViewItem item in this.lb_bookList.Items)
           {
                for (int i = 0; i < item.SubItems.Count; i++)
                {


                    int bookId = i;

                    //Inserting into a database
                    String requestBook = "INSERT INTO tbl_borrowedbooks(BorrowerID, BookID, DateRequested, Status) Values('" + getUser + "','" + bookId + "','" + date + "'," + "' Requested')";
                    MySqlCommand datacommand = new MySqlCommand(requestBook, databaseConnection);
                    
                        databaseConnection.Open();
                        datacommand.ExecuteReader();

                    
                }
            }

Insert with `ExecuteNonQuery()`. Also of interest: [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/q/7505808) — 001, Jan 03 '22 at 16:40
I don't think you need to call `databaseConnection.Open();` everytime. — 001, Jan 03 '22 at 16:43

Arturo Martinez · Answer 1 · 2022-01-03T16:59:31.627

Add them recursively to your command

foreach (ListViewItem item in this.lb_bookList.Items)
{
     var builder = new StringBuilder();

     builder.Append("INSERT INTO tbl_borrowedbooks(BorrowerID, BookID, DateRequested, Status) VALUES ");

     for (int i = 0; i < item.SubItems.Count; i++)
     {
         if (i > 0) builder.Append(",");
         var bookId = i;
         builder.Append("('" + getUser + "','" + bookId + "','" + date + "'," + "' Requested')");
                
     }

     MySqlCommand datacommand = new MySqlCommand(builder.ToString(), databaseConnection);
                    
     databaseConnection.Open();
     datacommand.ExecuteReader();

}

This generates a command like:

INSERT INTO tbl_borrowedbooks(BorrowerID, BookID, DateRequested, Status) VALUES 
('User1', '0', '2022-01-03', 'Requested'),
('User1', '1', '2022-01-03', 'Requested'),
('User1', '2', '2022-01-03', 'Requested')

Inserting listview items individually into database as new entries in c#

1 Answers1