C# - Windows Form - Delete Button - Won't Properly Execute SQL Command

Question

I am attempting to create a simple Windows Form application that can interact with a database to manage some data. I've run into an issue in my attempts to make a delete button, I've made similar applications several times but cannot figure out the problem here.

The purpose of the task is to create a Delete button that will delete the selected row from the database. As I'm limited to only using ADO.NET without DataBinding, and a DataGridView, I've done the following:

private void buttonViewPersonDelete_Click(object sender, EventArgs e)
        {
            string sSqlConnection = GetDBConnectionString("dbConnectionString");

            DialogResult result = MessageBox.Show("Are you sure you want to delete selected data?", "Delete", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
            if (result.Equals(DialogResult.OK))
            {
                using (SqlConnection sqlConn = new SqlConnection(sSqlConnection))
                {
                    sqlConn.Open();
                    SqlTransaction sqlTrans;
                    sqlTrans = sqlConn.BeginTransaction();
                    try
                    {
                        using (SqlCommand sqlCmd = new SqlCommand("", sqlConn, sqlTrans))
                        {
                            DataGridViewRow selectedRow = dataGridViewPerson.CurrentCell.OwningRow;
                            string sPersonID = selectedRow.Cells[0].Value.ToString();

                            //int iRowIndex = dataGridViewPerson.CurrentCell.RowIndex;
                            //string sPersonID = dataGridViewPerson.Rows[iRowIndex].Cells[0].Value.ToString();

                            string sQuery = "DELETE FROM dbo.Person WHERE iPersonID = @PersonID";
                            sqlCmd.Parameters.Add("@PersonID", SqlDbType.Int).Value = sPersonID;
                            sqlCmd.CommandText = sQuery;

                            sqlTrans.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, ex.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        try
                        {
                            sqlTrans.Rollback();
                        }
                        catch (Exception ex2)
                        {
                            MessageBox.Show(ex2.Message, ex2.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                    finally
                    {
                        UpdateDataGridViewStatus();
                        sqlConn.Close();
                    }
                }
            }
            else
            {
                return;
            }
        }

I've checked that the query works in SSMS, I've also made sure that sPersonID does receive the correct ID from the selected row, the issue appears to be with executing the query itself, I've tried doing it without the SqlTransaction and restructuring the code in a few different ways (reason why it looks so messed up), rebuilding the solution, etc.

The expected result is that the event would grab the ID out of the DataGridView, pass it as a parameter to the SQL Query, that would then execute and delete the desired row from the database.

Answer 1

You never executed your DELETE statement. You opened a transaction, prepared a command, then immediately committed the transaction. Add this right before the commit:

sqlCmd.ExecuteNonQuery();