Save datagrid changes in the database

Once you want edit a recore, add new or deleted a recored in your data grid you may want to save it in the database.

How do we do it?

1. Create UpdateCommand handler for the data grid
2. Identify what DataGrid row was updated by getting the ItemIndex property of the row (Item object) passed in the event object. Then use the index value to get the corresponding value out of the grid's DataKeys collection:
   • string key = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
3. Get the changed values out of the DataGrid row. How?
   • From the item passed in the event object, get the appropriate cell (zero-based) out of the Cells collection from the item passed in the event object. For example, the left-most column in the grid is Cells(0).
   • For each cell, get its Controls collection, which contains all the elements that appear in the cell.
   • Get the first (and only) control out of the collection — for this example, a TextBox control. To get the TextBox, declare a local variable of type TextBox and cast the object in the Controls collection to it.
   • Get the TextBox control's value (its Text property).
4. Find the corresponding row in the data table. The dataset contains a special FindBy method — that locates a row by its primary key and returns a reference to it.
5. Update the row by changing values in the row you located in Step 4.
6. Send changes from the dataset to the database by calling the data adapter's Update method.
7. Switch the current row in the grid out of editing mode.
8. Data-bind the DataGrid control.

Code example:

private void myDataGrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
string productName, productDescription;
// Gets the value of the key field of the row being updated
string key = myDataGrid.DataKeys[e.Item.ItemIndex].ToString();
// Gets get the value of the controls (textboxes) that the user
// updated.
// Each cell has a collection of controls. In this case, a TextBox control.
// The first column -- Cells(0) -- contains the Update and Cancel buttons.
TextBox tb;
// Gets the value the TextBox control in the third column
tb = (TextBox)(e.Item.Cells[2].Controls[0]);
productName = tb.Text;
// Gets the value the TextBox control in the fourth column
tb = (TextBox)(e.Item.Cells[3].Controls[0]);
productDescription = tb.Text;
// Finds the row in the dataset table that matches the
// one the user updated in the grid. This example uses a
// special Find method defined for the typed dataset, which
// returns a reference to the row.
dsProducts.CategoriesRow r;
r = dsProducts.Categories.FindByProductID(int.Parse(key));
// Updates the dataset table.
r.ProductName = productName;
r.ProductDescription = productDescription;
// Calls a SQL statement to update the database from the dataset
sqlDataAdapter1.Update(dsProducts);
// Takes the DataGrid row out of editing mode
myDataGrid.EditItemIndex = -1;
// Refreshes the grid
myDataGrid.DataBind();
}

For more information about DataSet updates, navigate to: http://msdn2.microsoft.com/en-US/library/y2ad8t9c(VS.71).aspx