Removing Rows from Database in C# with MYSQL

Question

this is my first time here so bear with me if I miss rules on posting or whatever. I'm looking for the proper way to delete a row from a database using C#. I already wrote code to delete it from the datagridview but what would I add on to completley remove it from the database?

Here is the code so far:

foreach (DataGridViewRow row in dataGridView1.SelectedRows)
{
   if (!row.IsNewRow)
   {
      dataGridView1.Rows.Remove(row);
   }

   MessageBox.Show("Selected rows Deleted");
}

This is what I tried out at first, thinking it would via a search:

OpenConnection();
string productType = txtDeleteProduct.Text;
MainForm frm = new MainForm();
mySqlDataAdapter = new MySqlDataAdapter("DELETE * from products        WHERE ProductType= '@productType';", connection);
DataSet DS = new DataSet();
mySqlDataAdapter.Fill(DS);
dataGridView1.DataSource = DS.Tables[0];
CloseConnection();

AFAIK DataGridView.Rows.Remove just remove the item from dataset, not in DB. You need to include SQL command to delete selected row(s) and use ExecuteNonQuery method to run it. — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented Dec 8, 2016 at 2:02
You need to use MySQLCommandBuilder. This is an SO question about MySQLCommandBuilder. Maybe it can help you. — Hermanto
– Hermanto, Commented Dec 8, 2016 at 2:31

Joel Coehoorn · Accepted Answer · 2016-12-08 03:37:07Z

This is an adaption of your second sample:

using (var cn = new MySqlDbConnection("your connection string here"))
using (var cmd = new MySqlDbCommand("DELETE * from products WHERE ProductType= @productType;")) // Note that I removed the single quotes (')
{
    //Use the actual column type and length here.
    // Ignore examples elsewhere online that use AddWithValue(). This is better!
    cmd.Parameters.Add("@productType", MySqlDbType.VarString, 25).Value = txtDeleteProduct.Text;
    cn.Open();
    cmd.ExecuteNonQuery();
}

There's not enough information in your question for me to completely finish this. You need something like it, but with your actual table names and key fields in the SQL statement. Here is something a little closer:

using (var cn = new MySqlDbConnection("your connection string here"))
using (var cmd = new MySqlDbCommand("DELETE * from `MYTABLE` WHERE MyIDColumn = @rowKey;")) // Note that I removed the single quotes (')
{
    cmd.Parameters.Add("@rowKey", MySqlDbType.Int32);
    cn.Open();

    foreach (DataGridViewRow row in dataGridView1.SelectedRows)
    {
        if (!row.IsNewRow)
        {
            //not sure which cell(s) in the grid make up your primary key
            cmd.Parameters["@rowKey"].Value = Convert.ToInt32(row.Cells[0]);
            cmd.ExecuteNonQuery();
            dataGridView1.Rows.Remove(row);
        }        
    }    
}
MessageBox.Show("Selected rows Deleted");

Finally... the normal procedure here is actually to send the delete commands, and then re-bind the grid... reload everything from scratch. You do this because you already have code to load the grid, so it saves you a little coding work, and because it gives you a chance to update things if something else changed in the table in the meantime.

Collectives™ on Stack Overflow

Removing Rows from Database in C# with MYSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related