SQL connection selecting row to delete

Question

I've got a local db in Visual Studio and in my project I want to delete a row which is selected in the WPF.

This is what I've got so far:

   private void Delete(object sender, RoutedEventArgs e)
    {
        DataRowView o = (DataRowView)g2.SelectedItem;
        int ID = Convert.ToInt32(o.Row.ItemArray[0]);
        
        SqlConnection con = new SqlConnection();
        con.ConnectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=MYFOLDER";    
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd = new SqlCommand("DELETE FROM [STOCK] WHERE o = @ID", con);
         
        cmd.Connection = con;
        cmd.ExecuteNonQuery();
        {
            MessageBox.Show("Row deleted");  
        }

In this statement I can select a row:

DataRowView o = (DataRowView)g2.SelectedItem;
int ID = Convert.ToInt32(o.Row.ItemArray[0]);

But then I have to delete it from my local db and I don't know how to complete this. I know this statement is wrong...but what is the right one:

cmd = new SqlCommand("DELETE FROM [STOCK] WHERE o = @ID", con);

Hope anybody can help me. I'm using Visual Studio 2019 - it's a WPF project with C#.

What is the primary key column in the STOCK table? What will be the value of `ID`? — Chetan, Dec 03 '21 at 08:55

score 2 · Accepted Answer · answered Dec 03 '21 at 10:17

You would need to refer to the ID column in your DELETE

Note also the following

AttachDbFilename is a bad idea, instead attach the database normally, using FOR ATTACH
Don't hard-code the connection string, put it in a settings file
You need to add the parameter to the command
You should always dispose the connection and command etc objects with using
Do not block the thread with a message box while the connection is open
Handle errors with a try catch

private void Delete(object sender, RoutedEventArgs e)
{
    DataRowView o = (DataRowView)g2.SelectedItem;
    int ID = Convert.ToInt32(o.Row.ItemArray[0]);

    try
    {        
        const string query = @"
DELETE FROM [STOCK] WHERE [STOCK].Id = @ID;
";
        using (SqlConnection con = new SqlConnection(Properties.ConnectionString))
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
            con.Open();
            cmd.ExecuteNonQuery();
        }
        MessageBox.Show("Row deleted");
    }
    catch(Exception ex)
    {
        MessageBox.Show("Error occurred:\r\n" + ex.Message);
    }
}

Thanks! It works. I'll put the connection string in a setting file. It also will be more easy to read i guess. Again: super thanks! — Hartjezomer, Dec 03 '21 at 11:03

SQL connection selecting row to delete

1 Answers1