[Download Code]

We will first look at what is involved in creating a typical data access page with update and delete functionality by examining an example. A live demo can be found at http://www.dotneteer.com/projects/DataGridHelper/v2/TypicalDataAccessPage.aspx. The page is an enhancement over the same page demonstrated in the first part of this article. I made the following enhancements:

1. Add a button column for the Edit/Update/Cancel commands. Add another button column for the Delete command.
2. Set the AutoGenerateColumns property to false. Instead of letting the DataGrid control to generate the columns automatically, I added some bound columns to the DataGrid control at design time. This allows us to have finer control over the behavior of the DataGrid, such as setting user-friendly headers, the format for the data displayed, as well as the sort expression. More importantly, we can now make some columns read-only--we do not want users to edit an identity column.
3. Set the DataKeyField property to the name of the primary key column of the table we are going to edit. The DataGrid control will then provide the value of the primary key through the DataKeys property at run-time, so we can use it to update and delete a record.
4. Add code to handle the EditCommand, CancelCommand, UpdateCommand and DeleteCommand events of the DataGrid.

The SqlDataAdapter member in the page was configured using the "Data Adapter Configuration Wizard."  Using "advanced options," we turned off the "Use optimistic concurrency" and "Refresh dataset" options because we do not use them.

The code that handles the EditCommand and CancelCommand events is essentially repetitive, as shown below:

private void dataGridProducts_EditCommand(object source, 
 System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
 dataGridProducts.EditItemIndex = e.Item.ItemIndex;
 bindGrid();
} 

private void dataGridProducts_CancelCommand(object source, 
 System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
 dataGridProducts.EditItemIndex = -1;
 bindGrid();
}

We can easily move this code to the DataGridHelper control using the same technique used in Part 1 of this series.

Now let us take a look of the code that handles the DeleteCommand event:

private void dataGridProducts_DeleteCommand(object source, 
 System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
 SqlCommand cmd = sqlDataAdapterProducts.DeleteCommand;
 Object key = dataGridProducts.DataKeys[e.Item.ItemIndex];
 try
 {
  cmd.Parameters["@ProductId"].Value = key;
  sqlConnection.Open();
  cmd.ExecuteNonQuery();
  lblMsg.Text = "Product " + key.ToString() 
   + " successfully deleted.";
  bindGrid();
 }
 catch (Exception ex)
 {
lblMsg.Text = "Failed to delete Product " + key.ToString() 
 + "due to following error:<BR>" + ex.ToString();
 }
}

This code basically retrieves the value of primary key column from the DataKeys property of the DataGrid control and sets it to the parameters of the delete command and then executes the command.

At last, let us take a look of the code that handles the UpdateCommand event:

private void dataGridProducts_UpdateCommand(object source, 
 System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
 SqlCommand cmd = sqlDataAdapterProducts.UpdateCommand;
 Object key = dataGridProducts.DataKeys[e.Item.ItemIndex];
 try
 {
  cmd.Parameters["@Original_ProductId"].Value = key;
    
  String[] cols = {"@Original_ProductId","@ProductName", "@SupplierID", 
   "@CategoryID", "@QuantityPerUnit", "@UnitPrice", "@UnitsInStock", 
   "@UnitsOnOrder", "@ReorderLevel", "@Discontinued"};
    
  int numCols = e.Item.Cells.Count;
  for (int i=3; i<numCols; i++) //skip first, second, third and last column
  {
   String colvalue =((System.Web.UI.WebControls.TextBox)
    e.Item.Cells[i].Controls[0]).Text;
                 
   cmd.Parameters[cols[i-2]].Value = colvalue;
  }   sqlConnection.Open();
  cmd.ExecuteNonQuery();
  lblMsg.Text = "Product " + key.ToString() + " successfully updated.";
  dataGridProducts.EditItemIndex = -1;
  bindGrid();
 }
 catch (Exception ex)
 {
  lblMsg.Text = "Failed to update Product " + key.ToString() 
   + "due to following error:<BR>" + ex.ToString();
 }
}

The code for the UpdateCommand event was borrowed from the ASP.NET QuickStart tutorial.  It is no doubt the ugliest part of DataGrid programming. The first part of the task is to locate the table cell that corresponds to the database table column of interest. Once the cell is located, we need to find the control in the cell that was used for data input. We can find the control by either looping through the Controls collection or using the FindControl method. We can then update the database using the retrieved values.