Why SQLCommandBuider not DELETING?

Question

In the following code UPDATE is working but DELETE is not working. When I DELETE row it delete it and GridView1 does not show it but database still has it. customers has PrimaryKey.

using (SqlConnection connection = new SqlConnection(conString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand("select * from customers", connection);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            connection.Open();
            DataTable customers = new DataTable();
            adapter.Fill(customers);
            // code to modify data in DataTable here
        customers.Rows.RemoveAt(rowIndex);
            GridView1.EditIndex = -1;
            GridView1.DataSource = customers;
            GridView1.DataBind();
            adapter.Update(customers);

Answer 1

You don't want to remove the row from the DataTable but delete it.

The DataRow's DataRowState must be modified to deleted, then the DataAdapter knows that it must delete it in the datasource.

Following will delete it:

customers.Rows(rowIndex).delete();//now DataRow's DataRowState is Deleted
adapter.Update(customers);        // now it's actually deleted

Futher informations: MSDN How to: Delete Rows in a DataTable

By the way, following would prevent the DataRow to be deleted, if you've changed your mind:

customers.Rows(rowIndex).RejectChanges();

The RowState will change back to Unchanged. This works either for the DataRow or for the whole DataTable.