How to delete multiple or all rows in DataGridView?

Question

I want to delete multiple rows or all rows in Database using DataGridView. For example. If I have 10 rows in the DataGridView, then all the 10 rows should be selected and deleted. Here is my code for deleting single row in database using DataGridView.

private void btnDeleteProduct_Click(object sender, EventArgs e)
    {
        ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["MyDBConnectionString"];
        string connectionString = conSettings.ConnectionString;

            if (ProductServicesDataGrid.CurrentRow.Selected)
            {
                string selectedCode = ProductServicesDataGrid.CurrentRow.Cells[0].Value.ToString();

                conn = new SqlConnection(connectionString);

                try
                {
                    conn.Open();
                    cmd = new SqlCommand("DELETE FROM ProductServices where ProductCode='" + selectedCode + "' ", conn);
                    sdr = cmd.ExecuteReader();
                    loadProductServicesTable();

                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
            else
            {
                MessageBox.Show("Row is not Selected");
            }
    }

Can someone please help me and modify my code?

Nino · Accepted Answer · 2017-08-09T12:27:42.347

1

first, you shouldn't use CurrentRow property because it returns only one row. Instead. Use SelectedRows collection and iterate through it.

Second, NEVER concatenate parameters in command text. Instead, use parametrized query.

//connection.Open is moved out of the loop, to avoid unnecessary open/close
conn = new SqlConnection(connectionString);
conn.Open();

try
{
    foreach (var row in ProductServicesDataGrid.SelectedRows)
    {
        string selectedCode = row.Cells[0].Value.ToString();

        try
        {
            cmd = new SqlCommand("DELETE FROM ProductServices where ProductCode=@productCode", conn);
            cmd.Parameters.Add(.Parameters.Add("productCode", SqlDbType.VarChar).Value = selectedCode;
            sdr = cmd.ExecuteReader();
            //this probably shouldn't be here, but outside the foreach loop.
            //that way table will be loaded after deletion of those n rows.
            //loadProductServicesTable();


    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    conn.Close();
}

//refresh products after deletion
loadProductServicesTable();

edited Aug 09 '17 at 12:27

answered Aug 09 '17 at 12:16

Nino

6,931
2
27
42

I guess it would be adviseable to open and close the connection outside of the loop, otherwise you open and close it unnecessarily – Mong Zhu Aug 09 '17 at 12:19
1

@MongZhu, that is good advice. But, the idea of deleting all rows one by one is also pretty bad... – Nino Aug 09 '17 at 12:25
I agree, but I forgot how it works with building a list and using the keyword `IN`.. :D otherwise I would write an answer :) – Mong Zhu Aug 09 '17 at 12:26
@MongZhu Yeah, i thought of that, but I'm too lazy to write it now... :) – Nino Aug 09 '17 at 12:28
He could try the duplicate that I just found. – Mong Zhu Aug 09 '17 at 12:29
@Nino, This worked for me though. Just want to ask why parametrized query and not concatenate parameters? Does this conflict or something else? – Hammad Ahmed Aug 09 '17 at 19:50
1

@Hamms read about [sql injection](https://en.wikipedia.org/wiki/SQL_injection)... also, obligatory [xkcd comic](https://xkcd.com/327/) – Nino Aug 09 '17 at 20:19

How to delete multiple or all rows in DataGridView?

1 Answers1