Data Points: Building a Tiered Web App Using the DataSet and the ASP DataGrid

MSDN Magazine

Building a Tiered Web App Using the DataSet and the ASP DataGrid
Download the code for this article: Data0205.exe (48KB)

uch of the success of the ADO Recordset can be attributed to its lightweight structure and easy-to-use interface. Now with the introduction of the ADO.NET DataSet, you can represent more than one data structure, making the ADO Recordset even more flexible. As I discussed in the February 2002 Data Points column, the DataSet can represent multiple related data structures and their relationships. And because it's built on XML, the DataSet can also transform itself into an XML document. This gives developers the flexible and portable solution they have been looking for in one nice, neat package that can be transported between application tiers or even applications.
      At the core of the ADO.NET DataSet object is an XML schema that describes the structure of the data contained within the DataSet. The flexibility of the DataSet allows it to store complex data structures and relations. There are other benefits inherent in XML-based data stores. For example, a DataSet can be deconstructed into the XML that defines its structure and data. The XML can then be used to transport the data and its schema across a LAN or even across the Internet using HTTP. This makes the DataSet an effective tool for storing data along with its schema, as well as a vehicle for transporting the data between applications. This month's column will concentrate on how to use a DataSet and its intrinsic features to manage the data throughout the various tiers of an ASP.NET Web application.
      In the January 2002 and February 2002 installments of the Data Points column, I demonstrated how to write three-tiered Windows®-based applications using the DataSet. Over the past few weeks, I received requests from readers wanting to know how to create a tiered, DataSet-centric Web application. So this month, I'll walk through the development of a three-tiered Web application that retrieves data, allows the user to make changes, and sends the data back to the database using the DataSet object as the transport mechanism.

Figure 1 Sample n-tier Application
Figure 1 Sample n-tier Application

      Using Visual Basic® .NET in a Web app, the application retrieves customers from the Northwind database from a business services application written in C#. The data is transported between the different application tiers via a DataSet object that contains the schema of the customer data, the customer data itself, and any modifications to the customer data. Then the information is loaded into a DataGrid in the presentation tier using a Microsoft® .NET Web application. Along the way I'll demonstrate how to bind a DataSet to an ASP.NET DataGrid, how to deconstruct and reconstruct a DataSet to and from XML, and how to make use of the session state features of ASP.NET. I'll begin by explaining how the DataSet plays a central part in an n-tier application, such as the one in this column (see Figure 1).

The DataGrid

      There are many different ways to save data modifications to a database in a business service class. Included among the more common techniques is the invocation of a stored procedure and execution of a SQL statement. In this column I will show you how to use these conventional techniques in conjunction with the ADO.NET DataSet. The DataSet is a flexible vehicle that can be passed to a business service class and used to update the database. The DataSet provides a single common data store to contain data and metadata. Passing a DataSet throughout an application's tiers ensures that there is a familiar interface to the data and the metadata that can easily be interacted, and even bound directly to an ASP.NET DataGrid server control.
      I'll start by showing how to present a customized DataGrid that's bound to a DataSet. It allows the user to view the data, flip through pages of the data, and sort the data forwards or backwards. The data can also be modified right in the DataGrid and rows can be deleted from the rowset.
      To set up a DataGrid, first you need to decide where the server-side code will reside. The code could be put in the aspx file intermixed with the HTML and presentation code, similar to the conventional ASP 3.0 style. In this case, the code is more organized than in the previous version of ASP since ASP.NET is an object-based language. The server-side code can be placed in script blocks and refer to the presentation controls (HTML controls, server controls, third-party controls) as objects with properties, methods, and events.
      Another alternative is to put all of the server-side code in a partner file. By keeping the server-side code separate from the HTML, the pages can be worked on by different people at the same time. For complicated pages, this technique is particularly effective since it segregates the code from the presentation, thus eliminating the need to wade through a tangle of code. Setting up a second page that contains all of the source code is quite simple, as shown here:

  <%@ Page Language="vb" src="WebForm.aspx.vb" Explicit="true" 
Inherits="WebAppMSDN_VB.WebForm" %>


Notice that the source file is pointing to the WebForm.aspx.vb file, which is the codebehind file. This file contains all of the source code for the aspx file.
      To customize the DataGrid to present the customer data, the properties need to be set up accordingly. Notice in Figure 2 that the DataGrid allows sorting, and when a column is clicked by the user, the onsortcommand property indicates that the OnPageSort event will fire. The event properties of the DataGrid are associated with events that reside in the codebehind page (WebForm.aspx.vb in this case). It is imperative that the event names in the DataGrid's properties match the event names in the codebehind page. Setting the oneditcommand property tells the DataGrid that when the user clicks the edit hyperlink, the OnEditCommand event in the codebehind should be executed.
      Keep in mind that the properties of the DataGrid and the event names do not have to be exactly the same. I used that convention for demonstration purposes only. For example, the oneditcommand property could be set to the event called GoDoIt in the codebehind. This will work as long as there really is a GoDoIt event in the codebehind.

Column Types

      To customize how the data is displayed, the DataGrid's columns are defined. First, a button is created for each row that will delete the corresponding row. To do this, a ButtonColumn is created with its ButtonType attribute set as a hyperlink. When this hyperlink is clicked, it calls the event that is set in the DataGrid's ondeletecommand property (shown in Figure 2) because the CommandName attribute is set to Delete (shown in Figure 3).
      The next column is the editcommandcolumn. This is a special column that can be an edit hyperlink or two hyperlinks: cancel and update. In its default state, this column shows the edit hyperlink. When a user clicks the edit hyperlink, the event associated with the DataGrid's oneditcommand property will execute in the codebehind and the OnEditCommand event in the codebehind flips the DataGrid into edit mode. When the DataGrid is reposted, the edit hyperlink is no longer visible; however, the cancel and update hyperlinks are present for that row. Then, when the user clicks on the update hyperlink, the OnUpdateCommand event is fired in the codebehind. I'll get back to the code within these events shortly, once I finish discussing the remaining column types.
      The rest of the columns are either of type TemplateColumn or BoundColumn. A BoundColumn type is the quick and easy way of creating a column that is bound to a DataSet. It can be set to be readonly, which tells the DataGrid not to allow editing to the readonly column if it goes into edit mode. A good candidate for a readonly bound column is the primary key of the rowset. In Figure 3, notice that the CustomerID key is bound to a readonly BoundColumn. If the readonly attribute is set to false, then when the DataGrid goes into edit mode, the column for the affected row will become a textbox, as in the City field in Figure 4.

Figure 4 Textboxes in Edit Mode
Figure 4 Textboxes in Edit Mode

      Columns of type TemplateColumn are generally used when a column requires more flexibility. For this application, I could have used BoundColumn types for all of the data columns, but I wanted to demonstrate that either one can be used. The TemplateColumn types require more typing, but they can be customized so that you can define the exact type of control to use when the column is either being edited or simply being viewed. The code in Figure 3 uses an ASP Label to display the bound data when it is being viewed and a textbox when it is being edited, as in the case of the CompanyName or ContactName fields. In either case, both types of columns can have a SortExpression attribute set for them. This tells the DataGrid how you wanted to sort it when the user clicks on the corresponding column.

The Codebehind Events

      As I mentioned, when the edit hyperlink is clicked, the event associated with the oneditcommand property of the DataGrid is executed. This event, called OnEditCommand, exists in the codebehind (WebForm.aspx.vb):

  Public Sub OnEditCommand(ByVal sender As Object, _
ByVal e As DataGridCommandEventArgs)
grdCustomer.EditItemIndex = e.Item.ItemIndex
End Sub

Public Sub OnCancelCommand(ByVal sender As Object, _
ByVal e As DataGridCommandEventArgs)
grdCustomer.EditItemIndex = -1
End Sub


The event grabs the index of the row that the user clicked on by using the e.Item.ItemIndex property. The row index is then assigned to the EditItemIndex property of the DataGrid. Whenever this property is set to a valid row index, the DataGrid flips into edit mode for that row. Finally, the event wraps up by reloading the data back into the DataGrid.
      The OnCancelCommand event, shown in the previous code, also sets the EditItemIndex property of the DataGrid. However, when setting this property to -1, the DataGrid reverts back to its default state in which all rows are in view mode. Then the event wraps up by reloading the DataGrid by calling the LoadData method.
      The LoadData method, shown in Figure 5, is responsible for retrieving the customer data from the database by way of the business services. The data is then bound directly to the DataGrid. An instance of the Customer class is created and its GetData method is called, which then returns a DataSet full of the customer data. Then the data is bound to the DataGrid before the page is reloaded.

A Little State

      The key to this application is that all of the data is passed around in the DataSet to and from the business services. In the LoadData method, the data is retrieved via the DataSet from the business services. In the update and delete events, the DataSet is reconstructed using its XML schema. Then the row of data that is being modified is retrieved from the DataGrid's column controls and stored in the reconstructed and empty DataSet. This DataSet can then be passed to the business services to make the data modification to the database.
      Since I'm dealing with a stateless environment, the DataSet will disappear once I reload the page. So where does the DataSet get stored? I stored it in the Session object. Actually, I chose not to store the entire DataSet; rather, I stored the schema of the DataSet in the Session object. The DataSet's data and schema can be deconstructed and put into XML format using the GetXml method of the DataSet. But since I am interested only in retaining the structure of the DataSet, there is no need to keep all of the data around, too. So instead, I used the GetXmlSchema method to store the skeleton of the DataSet in the Session object.
      The Session object provides quick access to data stored in it, but it also can be a resource hog if used in an enterprise application. There are other alternatives, one of which is to have the Session object store its data in a SQL Server™ database instead of using it as in-process memory (its default mode). The access to the data is slightly slower than in-process session state, but the data is not volatile in the database and can support more concurrent users.

Reconstructing the DataSet

      So now I've stored the schema of the DataSet for later use and I know I can get the data I need to fill it when a user clicks on the update or delete hyperlinks. To see this in action, examine Figure 6 in which the OnDeleteCommand event is defined. In this event, the XML schema of the DataSet is retrieved from the Session object and stored in a string variable. The string of XML schema is then read into a StringReader object, which is then used to reconstitute the DataSet. The actual reassembly of the DataSet is done by passing the StringReader object to the DataSet object's ReadXmlSchema method. At this point, the DataSet is empty and structured using the schema that I originally received from the business services' Customer class.
      To retrieve the row key, the code grabs the value in the table cell with an index of 2 (the cells are zero-based). This gets the Customer ID, which can be used to delete the row from the database.

  Dim sCustomerID As String = e.Item.Cells(2).Text


      Now, a new row is added to the DataSet and the DataGrid's row ID (in this case, the Customer ID) is assigned to the CustomerID column of the new DataSet's only row. This application sends the data modifications to the business services via a DataSet. This is why the DataSet is reconstructed without its data and the one row that's modified is entered into the DataSet. Because this row is being deleted, only the row key is required. The row is then deleted using the Delete method. But remember that the Delete method of the DataSet does not actually obliterate the row. Instead, this method marks the row for deletion, so that when the business services sends it to the database to be updated, the DataAdapter that updates the database knows which rows to delete:

  m_oDA.Update(oDS, "Customer");


      The OnUpdateCommand event, shown in Figure 7, works like the OnDeleteCommand event. The difference is that instead of only putting the row key in the newly reconstructed empty DataSet, the DataSet's new row is filled from the user's modifications to the DataGrid's row.

Wrapping Up

      This example shows how to create a tiered app that transports the data between the business services and the presentation services using a DataSet. One advantage to passing the DataSet to the business services to make the data modifications is that ADO.NET allows for a DataSet to be passed to the SqlDataAdapter's Update method and have its modifications be applied directly to the database. That means inserts, updates, and deletions can be applied by using just the Update method of the SqlDataAdapter object.
      In this installment of Data Points I demonstrated how to deconstruct a DataSet into its XML schema and then reconstruct it from an XML string. This same concept can be used at other times such as when you need to pass a data structure to another application over the Internet using HTTP. Of course, this application could forego the use of a DataSet to pass the data modifications down to the business services and instead pass individual values for each field. But passing a DataSet allows the application more flexibility since it can send a single parameter which can contain many rows of modifications.
      One of the greatest features of n-tier development is that the same business services can be used for multiple applications, whether on Windows or the Web. To reinforce that premise, I used the same business services for this application that I used in the February 2002 issue, where the business services' code is discussed in detail. For simplicity, I have included all code used in this month's application in the download, available from the link at the top of this article.

Send questions and comments for Johnny to

Johnny Papa is VP of Information Technology at MJM Investigations in Raleigh, NC, has authored Professional ADO 2.5 RDS Programming with ASP 3.0 (Wrox, 2000), and can often be found speaking at industry conferences. You can reach him at

From the May 2002 issue of MSDN Magazine