The Big ASP.NET DataGrid Article, Part 3

Welcome to the final part of the ASP.NET DataGrid mini-series. Today, we’ll be looking at implementing the likes of paging and sorting in your DataGrid… but start off with that most gruesome of tasks: adding an editable drop-down box to your DataGrid!

The Lowdown on Using Dropdown Boxes in a Web Grid

Want to add a dropdown box to your Web page, populated with values from your table? You’re in for a sweet surprise—it’s easy!

Just add the DropDownList control to your Web form and set the DataSource, DataMember, DataTextField, and possibly the DataValueField properties. As soon as you fill the DataSource, the bound DropDownList will automatically populate itself with a list of fields and values. You then can find out what the user selects through its SelectedItem property.

If you want to add dropdown boxes inside your Web grid, however, you’re in for a shocker. It’s pretty darn difficult.

Practically every public sample demonstrating the use of dropdown boxes in Web grids is unfeasible: The samples are based on pure text fields (not foreign keys); the old selected dropdown values aren’t preserved when you enter Edit mode; the update code is always buggy as hell. But there are so many different ways of implementing a dropdown box solution, it’s hardly surprising so much confusion abounds.

I spent the best part of one whole week figuring out how dropdown boxes worked with the DataGrid control—and, here, I’m going to reveal the quickest, most stable technique I found for handling the situation. My solution is based on what I deem to be the most common use for dropdown lists: allowing the user to select an ‘English’ value for a foreign key field. For example, you may have a news article table containing a “reporterid” field. Your actual list of reporter names, however, will most likely be in another table altogether. Your task is to make the fact that your main table uses a foreign key totally transparent: The user just wants to be able to select a reporter, period. That’s what this tip shows you.

To begin with, let’s get access to our core data. Locate your “parent” and “child” tables via a data connection in the Server Explorer (View > Server Explorer), then drag-and-drop onto your Web form. When you’ve finished, you’ll be left with one Connection object and two DataAdapter objects (as usual, you may wish to alter the ConnectionString property of the Connection object to specify a ‘user id’ and ‘password’ value). Right-click on one of the DataAdapter objects and select “Generate Dataset”. In the list of tables, check both your tables. Leave the rest of the defaults and click OK. An XSD template will be created and a typed DataSet added to your form, which we’ll use to feed our DataGrid. So far, this is all pretty standard stuff.

Figure: My Web form so far, hosting one Connection, two DataAdapters, and a typed DataSet

Top Tip: Don’t be too afraid of graphically dragging and dropping tables. Back in the Visual Basic 6 days, visually designing database applications like this was deemed bad practice. With VB.NET however, it’s exactly the same as instantiating objects and using them in code—just a little easier.

Add a DataGrid to your Web form and design it as necessary. When ready, open the Property Builder and, on the General property sheet, specify the DataSource, DataMember and, optionally, the Data key field. These values should all point to the parent “news article” table in your DataSet. Next, switch to the Columns property sheet, uncheck “Create columns automatically at run time”, and add an “Edit, Update, Cancel” button to the selected columns list. This button will be used to begin editing of a row.

Next, manually add all the data fields you wish to view in your grid to the selected columns list—ensuring you include both the primary key and foreign key fields, making both invisible by unchecking the Visible checkbox. Spend a few minutes setting any column properties required, such as “Header text”.

Then, cycle through each of those data columns and choose a ‘Sort expression’ from the dropdown box. Make sure you choose the name of the field supplying data for that column. Although this ‘Sort expression’ value is typically used for sorting columns of data (see the “Sorting in Seconds, with Your Web Grid” tip), we use it as a record of which columns bind to which database fields. You’ll see why later.

Finally, add a “Template Column” to the list. This is the column that will hold your dropdown box. Position it appropriately and change the “Header text” if required. Click OK to close the Property Builder when finished.

Figure: Setting properties for our DataGrid columns, through the Property Builder

Moving on, and it’s time to set up this new template column. Right-click on your DataGrid, select Edit Template, and choose the column you just added. The middle two portions of this “template” are most important: The ItemTemplate section displays what will appear in the cell when you’re viewing data, and the EditItemTemplate shows how that cell will look when you’re editing data.

In the ItemTemplate section, drag and drop a DropDownList control. We want this to display a noneditable list of options, with one selected. Change the Enabled property of the control to False and provide it with a name through the ID property. I’m calling mine “ddl1”.

In the EditItemTemplate section, drag and drop another DropDownList control. This is what the user will see when they enter Edit mode. Keep its Enabled property set to True this time, so they can change the selection, then change the ID property. I’m setting mine to “ddl2”.

Figure: Designing how our template cell will look, in “view” and “edit” mode

Next, we need to think about getting our actual list of options into our dropdown boxes. Change the DataSource property of both boxes to point to our main DataSet, and the DataMember to point to the child “reporter” table. Next, you need to choose the DataTextField (the field that contains the text options you want the dropdown to display) and the DataValueField (the field containing the key value for this option, such as an “id” field). As soon as we populate the DataSet, these dropdown boxes will automatically fill with values from our table.

When finished, right-click on the DataGrid and choose End Editing. Notice how your DataGrid appears now?

Figure: How the DataGrid looks so far, dropdown box and all

Next, let’s think about adding code. Much of this will be relatively standard; however, we’ll also need to do a little special processing. For example, we’ll need to write code to initially select the correct value from our dropdown field—taking the foreign key value and choosing an appropriate option from the list. We’ll also need to handle the update, by taking the value from our dropdown and putting it into the foreign key field, then cycling through the edited text boxes and updating the backend DataSet as you would usually. As you’ll see, the DataGrid really does very little of this work for you.

We’ll begin with a simple chunk of standard code, to be added in response to the page Load event:

If Not IsPostBack Then
  ParentNewsArticleDataAdapter.Fill(MyDataSet)
  ChildReporterDataAdapter.Fill(MyDataSet)
  MyDataGrid.DataBind()
End If

Here, we’re simply using our two DataAdapters to fill our DataSet with two chunks of information: the table containing our parent “news article” data, and the child table containing our “reporter” data. We’ve already set the DataGrid DataSource and DataMember properties, so we simply do a .DataBind to put it into action.

This is all standard stuff. Next, we’re going to add a little special code to run after we’ve initiated the .DataBind. We’ll add it to respond to the grid ItemDataBound event, which fires once for every row “item” being displayed in a data bound DataGrid. Here goes:

' If this is a valid item...
If e.Item.ItemType = ListItemType.AlternatingItem Or _
  e.Item.ItemType = ListItemType.Item Then
  ' Retrieve the foreign key value
  Dim intForeignKey As Integer = _
      e.Item.Cells(FOREIGN_KEY_COL_NUM).Text
  ' Find the dropdown containing our list of options
  Dim ddl As DropDownList = _
      CType(e.Item.FindControl("ddl1"), DropDownList)
  ' Select the correct entry in the dropdown
  ddl.SelectedIndex = _
      ddl.Items.IndexOf( _
      ddl.Items.FindByValue(intForeignKey))
End If

Here, we check that we’re dealing with a valid item. We then begin by retrieving our foreign key value (replace FOREIGN_KEY_COL_NUM with the column number containing your foreign key value. You may wish to use the Property Builder to help find the column number, remembering to start counting at zero and include any hidden fields). Next, we find our dropdown list on the row, then select the relevant item depending on the foreign key value. Do you see how that works?

So, that’s selected the DataGrid dropdown in regular “view” mode. Now how about editing?

You may remember the Edit-Update-Cancel button we added earlier. When the user clicks “Edit”, the EditCommand of the DataGrid fires off. And that’s exactly the event this next block of code should run in response to:

' Select the current row for 'edit mode'
MyDataGrid.EditItemIndex = e.Item.ItemIndex
' Refill DataSets with information
ParentNewsArticleDataAdapter.Fill(MyDataSet)
ChildReporterDataAdapter.Fill(MyDataSet)
' Rebind data
MyDataGrid.DataBind()
' Select default dropdown option, as earlier
Dim ddl As DropDownList = _
  CType(MyDataGrid.Items(MyDataGrid.EditItemIndex). _
  FindControl("ddl2"), DropDownList)
ddl.SelectedIndex = _
  ddl.Items.IndexOf( _
  ddl.Items.FindByValue(e.Item.Cells(FOREIGN_KEY_COL_NUM).Text))

Much of the code here is typical of that you’d find responding to an edit event. We set the .EditItemIndex. We populate our DataSets again and rebind. Then, we run special code to locate our second dropdown (the editable one) and preselect the correct default value (again, replace FOREIGN_KEY_COL_NUM with the column number containing your foreign key value). Great stuff.

What’s left to do? Well, we’ve handled displaying a default item in regular “view” mode and pre-selecting one in edit mode. The user will then begin editing the grid—and when finished, either click “Update”, wishing to store their changes, or “Cancel” to forget about them.

Figure: Visualizing the “Update” and “Cancel” options our user will have after clicking ‘Edit’

When the user clicks “Update”, the DataGrid UpdateCommand event runs—then it’s over to you to take the alterations and update your database. That’s just what this next chunk of slightly-more-confusing code does, to be run in response to the UpdateCommand event:

' Step one: Put dropdown list value into foreign key field
Ctype.Item.Cells(FOREIGN_KEY_COL_NUM).Controls(0), TextBox).Text = _
  CType(e.Item.FindControl("ddl2"), DropDownList).SelectedItem. _
        Value()

' Step two: Fill DataSet and identify row to edit
ParentDataAdapter.Fill(MyDataSet)
ChildDataAdapter.Fill(MyDataSet)
Dim objEditedRow As DataRow = MyDataSet.Tables(0).Rows.Find( _
  CType(e.Item.Cells(ID_NUM).Controls(0), TextBox).Text)

' Step three: Cycle through valid "data" cells and put
'             information back in underlying DataSet
Dim intCount As Integer
For intCount = 0 To e.Item.Cells.Count - 1
  If e.Item.Cells(intCount).Controls.Count > 0 Then
    If TypeOf (e.Item.Cells(intCount).Controls(0)) Is TextBox Then
      ' This appears to be a TextBox-holding "data" cell
      Dim strValue As String = CType(e.Item.Cells(intCount). _
          Controls(0), TextBox).Text
      ' Put value (or null if empty) back into relevant DataSet
      ' field
      If strValue = "" Then
         objEditedRow.Item(MyDataGrid.Columns(intCount). _
                      SortExpression) = System.DBNull.Value
      Else
        objEditedRow.Item(MyDataGrid.Columns(intCount). _
          SortExpression) = strValue
      End If
    End If
  End If
Next

' Update backend data
ParentNewsArticleDataAdapter.Update(MyDataSet)

' Deselect DataGrid items and rebind
With MyDataGrid
     .SelectedIndex = -1
     .EditItemIndex = -1
     .DataSource = MyDataSet
     .DataBind()
End With

This code consists of three core steps. In the first, we retrieve the value behind our dropdown and put it in the foreign key field (replace FOREIGN_KEY_COL_NUM with the column number containing your foreign key value). In the second, we fill our DataSet as normal, and then filter using the primary key value to locate the row our user has edited (replace ID_NUM with the column number of your primary key value). The third and final step cycles through all the cell TextBox controls, figures out which field the data inside each box belongs to (using the column sort expression value), and updates the underlying DataSet. We then deselect any DataGrid items and rebind. It looks confusing, and the DataGrid does none of the work for us, but the underlying process is actually relatively simple.

Finally, we need to add code to respond just in case the user clicks “Cancel”. This is relatively standard code and should be added to respond to the CancelCommand event. We simply reset the grid and rebind:

MyDataGrid.SelectedIndex = -1
MyDataGrid.EditItemIndex = -1
ParentNewsArticleDataAdapter.Fill(MyDataSet)
ChildReporterDataAdapter.Fill(MyDataSet)
MyDataGrid.DataBind()

And that’s it! Admittedly, it seems one heck of a long process for something so relatively simple, but, if you’ve seen some of the other demonstrations of this technique elsewhere, you’ll appreciate the concise nature of this blighter. And, once you’ve done it a couple of times, well, let’s just say it gets a little easier. Phew.

Top Tip: For simplicity and to ensure this tip can run “standalone”, it uses the standard Edit-Update-Cancel button integrated into the DataGrid. If you’re building on top of the “Nine Steps to a Quick, Editable Web Grid” tip, you may wish to edit certain parts of this code so it kicks in behind your own Edit-Update-Cancel buttons and possibly uses the DataSave, DataRetrieve, and DataExists routines. You may also want to add your own error handling or concurrency checks, too. Or you might just decide you’ve already pushed editing with the DataGrid too far and opt for a third party-solution. Your call.

Figure: Give yourself a promotion: demonstrating an editable Web grid dropdown

Speedy, Personalized Web Data Binding

If you’re simply viewing data using your Web grid and want to personalize the output a little more, then you’ll be pleased to know that, despite the rumors, it’s actually pretty simple and can be done without even delving into those special <asp> tags you find dotted in most books.

To personalize the DataGrid, first set it up as usual. In the General property sheet of the Property Builder, choose a DataSource, DataMember, and an optional Data key field. Under the Columns property sheet, uncheck “Create columns automatically at run time” and instead add the data fields you require to the list of selected columns.

Now, cycle through each of the columns in the selection column list and click on the “Convert this column into a Template Column” link at the bottom of the property sheet. This takes the standard DataGrid method of displaying information from a DataSource and changes it into a template, letting you edit how each column field looks. Click on OK to close the Property Builder.

Next, right-click on the DataGrid, select Edit Template, and look at the ItemTemplate section for one of your columns. You’ll notice that it already contains one label, which links to a field in your table through the DataBindings property.

Begin your customization. You may, for example, add two Label controls and change the DataBindings property so that one points to, for example, Container > DataItem > FirstName, while the second binds to Container > DataItem > LastName. You could perhaps add a little text as a prefix to the field value, too.

There are more possibilities. You may add a HyperLink control and change DataBindings so that the Text and NavigateUrl are provided by fields from your table. You might use your data to supply the ImageUrl property of an ImageButton control—simply select the field containing your filename. (You may also wish to change the ‘Format’ box to something such as http://www.yoursite.com/images/{0} if you wish to add a prefix to your filename—the {0} will get replaced with the data from your field.) You could even merge all of the above and create a single column listing of all your catalogue products: pictures, descriptions, purchase links and all.

Top Tip: Want to customize even further? Delve straight into the HTML. Right-click on your DataGrid and select View HTML Source.

When finished, right-click on the grid and select End Template Editing. Then, bind view your data as normal.

In brief, with the ItemTemplate (and related) sections, you can really push the way your DataGrid displays its simple bound data. Truthfully, in-grid editing is troublesome, but when you’re just viewing… the power is yours!

Top Tip: In case you’re wondering, the DataList control works in much the same way as the DataGrid when you get to this level of customization. It holds advantages and disadvantages over the DataGrid—for example, although you can’t edit through the DataList, you can list rows horizontally or vertically. The brother Repeater control can also be bound, but it requires editing of the <asp> tags in HTML and is more limited in the way it works. Look up “DataList control, vs. other list controls” in the help index for more information.

Quick and Easy Data Paging, with Your Web Grid

Most Web developers tend to shiver at the mere mention of the word paging. Even old-hat ASP.NET programmers are a little confused over its implementation. Many of the new .NET books devote a chapter or so to the subject—but it really isn’t that complicated.

Paging is a method of splitting results in your DataGrid over multiple “pages.” Google uses paging in its search results. Yahoo! uses paging if it can’t fit all its matching sites onto one page. It’s useful—and can be set up in seconds.

First off, set up your DataGrid as usual. Add all your regular binding code. When you’re done, right-click on the grid and select Property Builder. Choose the Paging property sheet and check “Allow paging”. Alter the page size so it represents the number of items you want to display on any one page. You may also want to change the navigation text just below. Click OK when finished.

Next, add code similar to the following to respond to the PageIndexChanged event of your DataGrid control:

MyDataGrid.CurrentPageIndex = e.NewPageIndex
' Bind your data again here. You could load DataSet
' from ViewState, database, etc. For example:
MyDataAdapter.Fill(MyDataSet)
MyDataGrid.DataBind()

And that’s it—just fire off your DataGrid as normal and you’ve got instant paging!

Top Tip: If you’re going to use editing with paging, watch out when updating your DataSet. If you’re doing it purely on position, remember that, after you move to a new page, your first item will not correlate with the first item in your DataSet. You’ll have to code around this. You can find out the current page by looking up the .CurrentPageIndex property, and find out the number of items displayed per page with the .PageSize property.

Figure: Simple paging in seconds!

Sorting in Seconds, with Your Web Grid

When you’re looking at a grid full of data, it’s natural to want to order it somehow. You may want to view employees by salary amount, from the highest paid to the lowest. Or view a customized query showing an alphabetical list of sales staff and their respective telephone numbers.

That’s all easy, with sorting—something the ASP.NET DataGrid provides excellent built-in support for. Here, I’m going to demonstrate the sorting technique in its simplest form; however, you can greatly expand upon this to build a much more elegant solution.

First of all, set up your DataGrid as normal, adding all your regular binding code. When done, open up the Property Builder and in the General property sheet, set the DataSource, DataMember, and optional Data key field for your table. Moving on to the Columns property sheet, uncheck “Create columns automatically at run time”, and then move all the fields you want to view in this DataGrid across to the list of selected columns.

Next—and this is the important bit—specify a “sort expression” value for each of the columns you’re going to allow your users to sort by. This will turn the column header into a hyperlink that, when clicked, will fire an event. This event will receive your sort expression value as an argument, which you then can use to reorder the data appropriately. So, select a few columns and a related sort expression. For the purpose of this example, stick with the default of the field name as the sort expression. (You’ll see why it makes things easier next.) Click on OK when finished.

Now, we’re ready to add code to handle this sorting ourselves. No, this process isn’t automatic—we need to somehow reorder our data and then rebind to the DataGrid. I’m going to handle this by simply requerying my data source, although with an extra ORDER BY clause. Here’s the code I’ll be entering to respond to the SortCommand event of my DataGrid:

' Reset index, in case on a different page
MyDataGrid.CurrentPageIndex = 0
' Change SELECT statement, using the passed in 'sort expression'
' value we specified earlier
MyDataAdapter.SelectCommand.CommandText = _
  "SELECT * FROM mytablename ORDER BY " & e.SortExpression
' Refresh data and rebind
MyDataAdapter.Fill(MyDataSet)
MyDataGrid.DataBind ()

Here, you can see that my code initially resets the index, in case we’re on another “page” of the DataGrid somewhere. It then uses our DataAdapter to alter the underlying SELECT statement, ordering by the current sort expression (the field name we specified earlier). Note that there are other, more elegant ways of handling this, rather than directly rewriting the SELECT text, but, for simplicity and demonstration purposes, this technique’s a winner. Finally, we refill the DataSet and bind it back to the DataGrid.

We’re almost there now. To activate all your hard work, all you need to do is select your DataGrid and change its AllowSorting property to True.

And that’s it! Your grid will now allow you to click on the header text of any of your “sort-aware” columns and put them in correct, ascending order. With a little imagination, you can also expand on this code base to allow “reverse sorts,” too. Have a play around!

Figure: Click and sort, in just a few lines of code

About the Author

Karl Moore (MCSD, MVP) is an experience author living in Yorkshire, England. He is author of numerous technology books, including the new Ultimate VB .NET and ASP.NET Code Book, plus regularly features at industry conferences and on BBC radio. Moore also runs his own creative consultancy, White Cliff Computing Ltd. Visit his official Web site at www.karlmoore.com.