How to delete a selected row from datagridview and database

Question

The idea is that the row that is selected when deleted gets removed from datagridview, database and then datagridview gets refreshed. I assume it has to be done with SQL but how would you link that sqlcommand of type text with a delete code with that particular row? The database consists of one single table and the datagrid is bound to it.

Delete button:

private void btnBookRecord_Click(object sender, EventArgs e)
{
    if (this.BooksGrid.SelectedRows.Count > 0)
    {
        foreach (DataGridViewRow dgvrCurrent in BooksGrid.SelectedRows)
        {
            if (dgvrCurrent == BooksGrid.CurrentRow)
            {
                BooksGrid.CurrentCell = null;
            }

            // Delete row code here
        }
    }
}

Solution 1

For some reason the datagridview won't update, even though I copied the refresh code from add button which works. But it does delete the record from database.

private void deleteRecord()
{
    if (BooksGrid.SelectedRows.Count > 0)
    {
        int selectedIndex = BooksGrid.SelectedRows[0].Index;

        int rowID = int.Parse(BooksGrid[0, selectedIndex].Value.ToString());
        string sql = "DELETE FROM Table1 WHERE RowID = @RowID";

        SqlCommand deleteRecord = new SqlCommand();
        deleteRecord.Connection = Booksconnection;
        deleteRecord.CommandType = CommandType.Text;
        deleteRecord.CommandText = sql;

        SqlParameter RowParameter = new SqlParameter();
        RowParameter.ParameterName = "@RowID";
        RowParameter.SqlDbType = SqlDbType.Int;
        RowParameter.IsNullable = false;
        RowParameter.Value = rowID;

        deleteRecord.Parameters.Add(RowParameter);

        deleteRecord.Connection.Open();

        deleteRecord.ExecuteNonQuery();

        deleteRecord.Connection.Close();

        booksDataset1.GetChanges();

        sqlDataAdapter1.Fill(booksDataset1.Videos);
    }
}

Solution 2

If only allowing one selection in the DataGridView, you could do this.

Say the first column in the DataGridView is the identity seed of the row in the database.

if (BooksGrid.SelectedRows.Count > 0)
{
     int selectedIndex = BooksGrid.SelectedRows[0].Index;

     // gets the RowID from the first column in the grid
     int rowID = int.Parse(BooksGrid[0, selectedIndex].Value.ToString());

     string sql = "DELETE FROM Table1 WHERE RowID = @RowID";

     // your code for deleting it from the database

     // then your code for refreshing the DataGridView
}

Comments

[WhySoSerious]

if it's one single table I suggest that you use the Update method of your DataAdapter and a SQLCommandBuilder, so that changes made on the DataGridView (example for deleting rows using RemoveAt) can be saved directly to the database with a simple line of code sqlDataAdapter1.Update(booksDataset1.Videos); See msdn.microsoft.com/en-us/library/… msdn.microsoft.com/en-us/library/xzb1zw3x.aspx msdn.microsoft.com/en-us/library/33y2221y(v=vs.110).aspx