AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=476&pId=-1
Building a DataGrid Helper Control for ASP.NET 1.x: Part 2
page
by Li Chen
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 25648/ 55

Introduction

[Download Code]

In Part 1 of this series, we built a DataGrid helper control that would drastically simplify the programming of a read-only data access page. In Part 2, we continue our quest to enhance the control and use it to simplify the programming of a data access page with update and delete capability. We will push the idea to the extreme and show that we can also develop a data access page with sorting, paging, deleting and updating capabilities without writing a single line of code in ASP.NET 1.x, just like ASP.NET 2.0 has promised.

Again, we will start by examining a typical data access page with update and delete capability and identify repetitive code that can be moved into our control. We will then enhance the DataGridHelper control to handle update and delete. We will show how to use the control and conclude the article.

Now, let us take a look of a typical data access page first.

A Typical Data Access Page with Update and Delete Capability

[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.

Enhance the DataGridHelper Control with Update and Delete Capabilities

[Download Code]

We intend to achieve the following goals with our enhancements:

  1. Eliminate repetitive coding for the EditCommand, CancelCommand, and DeleteCommand.
  2. Simplify the coding for UpdateCommand by extracting user inputs from bound columns.
  3. Make use of data adapters to achieve code-free programming. The several data adapters and their configuration wizards is the closest thing to code-free programming offered by Microsoft in ASP.NET 1.x. Although data adapters were designed to synchronize data between DataSet and data sources, the data adapters contain the necessary commands as well as the mapping information we can use to retrieve data and update the data sources from our ASP.NET page.
  4. Use with any data source. In fact, the control does not use the System.Data.SqlClient namespace.

The DataGridHelper.cs file in the WebDataControls project contains the code for the enhanced DataGridHelper control.

The most important new property of the enhanced DataGridHelper control is the DataAdapter property. If the DataAdapter property is assigned, the DataGridHelper control will attempt to use the data adapter automatically.

In the following, we will show the events exposed by the DataGridHelper control and how they behave depending on whether the DataAdapter property is set.

Event Description
LoadData If the DataAdapter property is set, the DataGridHelper control will load the data automatically and the event will not fire. If the DataAdapter property is not set, it is necessary to handle the LoadData event and set the DataSource property of the event argument.
BeforeUpdateData This event will fire when the UpdateCommand event of the DataGrid is fired and before the data is updated. The event argument has a few properties. The Values property contains the name/value pair extracted from the DataGrid. We can validate and modify the values. We can also extract additional values using the Item property if the DataGrid contains template columns. Set the Cancel property if we want to cancel the update.
UpdateData If the DataAdapter property is not set, we need to handle this event to update the data. The event argument has a Values property that contains the values as name/value pairs. If the DataAdapter property is set and there is an update command, the DataGridHelper control will update the data automatically. The event will then fire after the update and we can use it to handle any error occurred.
BeforeDeleteData If event will fire when the DeleteCommand event of the DataGrid is fired and before the data is deleted. I include this event mostly for completeness. This event could be used to check external conditions and cancel the delete if the conditions are not met.
DeleteData If the DataAdapter property is not set, we need to handle this event to delete the data. If the DataAdapter property is set and there is a delete command, the DataGridHelper control will delete the data automatically. The event will fire after the delete and we can use it to handle any error occurred.

The event arguments for all the events except the first event have an Item property and a DataKey property. The Item property points to the current DataGridItem of the DataGrid control. The DataKey property is of the type of object array. In this version, the array only has one element but we create it as an array so that we can support multiple-column keys in the future.

The following table contains the properties of the DataGridHelper:

Property Purpose
AlternateSortDirection Alternate the sort direction on subsequent clicks of the same column header.
AutoSortDataView If this property is true and the data source is DataSet, DataTable or DataView, the DataGridHelper control will sort the data automatically.
DataAdapter If the DataAdapter property is assigned, the DataGridHelper control will attempt to use it to load, update and delete data. If not assigned, we have to handle the LoadData, UpdateData and DeleteData events. We can assign any object inherited from DbAdapter class to this property.
DataGrid The DataGrid control to bind to. Without binding to a DataGrid control. The DataGridHelper control is useless.
MessageLabel If assigned, the DataGridHelper will automatically display the error message occurred during update or delete. It is possible to modify the message in the UpdateData and DeleteData event.
SortExpression The current sort expression. This property is useful when the user of the control wants to soft the data themselves in the LoadData event.
ResetPageIndexOnSorting If this property is True, the control will reset the page index whenever the sort expression is changed.

The DataGridHelper control also exposes a single method.

Method Description
GridLoad Refresh the data if we have post-back from another control.

We also add a new component, DataGridHelperConnector, to the WebDataControl.dll. This component is used to connect a DataGridHelper control to a DataGrid control, a data adapter, and a Label control at design time. It was created entirely for the sake code-free programming.

In the next section, we will show how to use the enhanced DataGridHelper control.
 

Using the DataGridHelper Control

[Download Code]

The DataGridHelper.aspx page demonstrates the use of DataGridHelper control. A live demo can be accessed at http://www.dotneteer.com/projects/DataGridHelper/v2/DataGridHelperTest.aspx. This page is functionally identical to the TypicalDataAccessPage.aspx. The DataGrid control in the page has the same configuration. The page also contains a DataGridHelper control and a DataGridHelperConnector component. The page does not contain a single line of code!

If we do not use the DataGridHelperConnector component, all we need is to add three lines of code in the Page_Init event:

private void DataGridHelperTest_Init(object sender, System.EventArgs e)
{
      dataGridHelper.DataGrid = dataGridProducts;
      dataGridHelper.DataAdapter = sqlDataAdapterProducts;
      dataGridHelper.MessageLabel = lblMsg;
}

Apart from the code-free programming, the DataGridHelperConnector control does not save much coding.

In the next section, we will discuss more about the limitations of DataGridHelper control and see how it compares with ASP.NET 2.0 and conclude this article.

Final Notes

[Download Code]

We have successfully realized the promise of code-free programming a complete data access page in ASP.NET 1.x. Now let us examine the limitation of our implementation and compare it to the proposed offering in ASP.NET 2.0.

  1. The current version of the DataGridHelper control uses the functionality of the DataGrid control to retrieve data key. Since the DataGrid control only supports single column primary key, the DataGridHelper control inherits the same limitation. The data source controls in ASP.NET 2.0 supports a multiple-column key. In order to support a multiple-column key, we have to build our own key storage and retrieval mechanism in the DataGridHelper control.
  2. Although the DataGridHelper can extract user input from bound columns, it still cannot extract user input from template columns; the users of the control need to extract the input with code in the BeforeUpdateData event. The ASP.NET 2.0 has a mechanism called two-way binding that makes it possible to update data with controls embedded in a template.
  3. Although we realized code-free programming by using the xxxDataAdapters and their wizards in ASP.NET 1.x, ASP.NET 2.0 also offers data source controls that can bind to object data sources. However, ASP.NET 2.0 requires a rigid mapping between the data columns and method parameters of the object. In contrast, the DataGridHelper control simply uses name/value pair to pass data.  We believe that our approach is very easy to understand and flexible.

In conclusion, we have developed a DataGridHelper control that can drastically simplify DataGrid programming. When used with data adapters, it is possible to develop a data access page with sorting, paging, updating and deleting capabilities without writing a single line of code.

For updates, please check http://www.dotneteer.com/home/datagridhelper. For comments, questions and suggestions, please email lichen@linkline.com.


Product Spotlight
Product Spotlight 

©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 10:27:45 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search