c# delete multiple data from database at once

Question

I'm trying to create loop that will delete data from my database at the same times.

here's my code.

using (MySqlConnection mysqlCon = new MySqlConnection(connectionString))
{
    foreach (DataGridViewRow dr in bunifuCustomDataGrid1.Rows)
    {
        int inventid = Convert.ToInt32(bunifuCustomDataGrid1.Rows[bunifuCustomDataGrid1.CurrentRow.Index].Cells[0].Value);

        mysqlCon.Open();
        MySqlCommand cmd = new MySqlCommand("DELETE FROM bookdb.book WHERE (BookID = @uid)", mysqlCon);
        cmd.Parameters.AddWithValue("uid", inventid);

        cmd.ExecuteNonQuery();

        mysqlCon.Close();
        GridFill();
    }
}

MessageBox.Show("DOne");

I can delete multiple record, but its out if order and chaos. I hope someone can help me.

////////////////////////////////////////////////////////

I've tried some answers below. this is the code that almost work for me.

        using (MySqlConnection mysqlCon = new MySqlConnection(connectionString))
        {
            mysqlCon.Open();
            var tr = mysqlCon.BeginTransaction();

            foreach (DataGridViewRow dr in bunifuCustomDataGrid1.Rows)
            {

                var cmd = new MySqlCommand("DELETE FROM bookdb.book WHERE (BookID = @uid)");
                cmd.Connection = mysqlCon;
                cmd.Transaction = tr;
                cmd.Parameters.AddWithValue("@uid", dr.Cells["BookID"].Value ?? DBNull.Value);
                cmd.ExecuteNonQuery();
            }

            tr.Commit();
            mysqlCon.Close();

            //////////////////////////
        }

        MessageBox.Show("DOne");
        GridFill();

        ////
        ///

the problem is it deleted all rows.

A more typical approach is to use data binding. If your data was under the control of a MySqlDataAdapter you would just call it's Update() method. If you are manipulating UI elements its a red flag. — Crowcoder
– Crowcoder, Commented Dec 12, 2019 at 21:47
Why are you opening and closing your connection each time you iterate over bunifuCustomDataGrid1.Rows? — Babak Naffas
– Babak Naffas, Commented Dec 12, 2019 at 22:08
Why are you ignoring the "dr" variable in your loop? If your GridFill(); does what I think it does, it probably shouldn't be inside that loop. — LarsTech
– LarsTech, Commented Dec 12, 2019 at 22:13

Charles · Accepted Answer · 2019-12-12 21:59:41Z

2

use a transaction to send many ExecuteNonQuery in one batch

mysqlCon.Open();
var tr = mysqlCon.BeginTransaction();

foreach (...)
{
    var cmd = new MySqlCommand();
    cmd.Connection = mysqlCon ;
    cmd.Transaction = tr;

     ...
    cmd.ExecuteNonQuery();
}

tr.Commit();
mysqlCon.Close();

answered Dec 12, 2019 at 21:59

Charles

3,1112 gold badges11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gino Over a year ago

i think i use your code in the wrong way.. it deleted all the row at once..

Charles Over a year ago

inside your foreach loop put Debug.WriteLine($"deleting bookId {dr.Cells["BookID"].Value}") and comment //cmd.ExecuteNonQuery() to first check if it will delete all the correct bookIDs

antonye · Accepted Answer · 2019-12-12 22:12:12Z

1

You could structure your query differently to delete all at once:

DELETE FROM bookdb.book WHERE BookID IN (bookid1, bookid2, bookid3, ...)

Although this can sometimes lead to a massive SQL query being passed.

As per previous answers, the best solution is to use data binding and call the update method once all items are marked for deletion; this is the equivalent of deleting everything in a transaction.

answered Dec 12, 2019 at 22:12

antonye

415 bronze badges

Comments

sspaniel · Accepted Answer · 2019-12-13 13:59:17Z

1

Based on your current code, seems like an easy way to do that is to iterate the cells and grab all of the ids from the gridview at once, then send them to the DB to be deleted.

        using (MySqlConnection mysqlCon = new MySqlConnection(connectionString))
        {
            IEnumerable<int> ids = bunifuCustomDataGrid1
                .Rows[bunifuCustomDataGrid1.CurrentRow.Index]
                .Cells
                .Select(x => Convert.ToInt32(x.Value));

            string sql = $"DELETE FROM bookdb.book WHERE BookID IN ({string.Join(", ", ids)})";

            mysqlCon.Open();
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);

            cmd.ExecuteNonQuery();

            mysqlCon.Close();
            GridFill();
        }

        MessageBox.Show("DOne");

edited Dec 13, 2019 at 13:59

answered Dec 12, 2019 at 22:11

sspaniel

6874 silver badges10 bronze badges

3 Comments

Charles Over a year ago

just be careful with sql injection here if you don't use Parameters, shouldn't be a problem with int tho

Gino Over a year ago

I've tried this code. but i got red line error in .Select... i wonder why.

sspaniel Over a year ago

you might need some additional code to validate each cell's value to make sure it can be converted to a integer

Collectives™ on Stack Overflow

c# delete multiple data from database at once

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related