Data Set Sorting on Generated Columns

• Download full project - 19 Kb

Introduction

This articles main purpose is to demonstrate how to sort columns that have been calculated from data retrieved from an external datasource and how to display this information natively in the datagrid control and allow for paging of the various data types that might be implemented.

Background

The need for this type of datagrid control came when I was developing a small accounting program which had a number of reports.  One of the reports had a calculated date and a couple of calculated amounts.  The only option I knew at the time was to have Sql Server calculate these totals for me and then display them in the datagrid.  I could have then used a sql 'Order By' to achieve whatever sorting I needed.  This option was very hard for me to implement for various reasons.  So I went looking for another option and the methods in this article are what I came up with.

Analyzing the Code Behind

The code snippet below is from a sample project I built to showcase the method I use for custom sorting. I welcome criticism of my code.  It helps me better my coding style and techniques.

Sub bindData()
  'Initialize Dataset
  Dim myDataSet As DataSet = New DataSet
  Dim i As Integer
  Dim drow As DataRow


  'Add primary table that we will modify
  myDataSet.Tables.Add("Table")

  'Load extra data columns before binding xml data
  'so these fields can be typed for sorting and modification
  myDataSet.Tables("Table").Columns.Add("ItemName")
  myDataSet.Tables("Table").Columns("ItemName").DataType = GetType(String)
  myDataSet.Tables("Table").Columns.Add("ItemPrice")
  myDataSet.Tables("Table").Columns("ItemPrice").DataType = GetType(Double)
  myDataSet.Tables("Table").Columns.Add("Tax")
  myDataSet.Tables("Table").Columns("Tax").DataType = GetType(Double)
  myDataSet.Tables("Table").Columns.Add("Total")
  myDataSet.Tables("Table").Columns("Total").DataType = GetType(Double)

  'Load Xml Data
  myDataSet.ReadXml(Server.MapPath("DataSet.xml"))

  'Do calculations on each row
  For i = 0 To myDataSet.Tables("Table").Rows.Count() - 1
    drow = myDataSet.Tables("Table").Rows(i)

    bindRow(drow)
  Next



  'Load a DataView with information from the table we built
  'using the dataset
  Dim myDataView As DataView = myDataSet.Tables("Table").DefaultView
  myDataView.Sort = lblOrderBy.Text & " " & lblOrderDir.Text

  DataGrid.DataSource = myDataView
  DataGrid.DataBind()
End Sub

Private Sub bindRow(ByVal drow As DataRow)
  Dim ItemPrice, Tax, Total As Double

  'Grab the value of the itemprice from the 
  'xml file for the current record
  ItemPrice = CType(drow("ItemPrice"), Double)
  Tax = ItemPrice * 0.076
  Total = Tax + ItemPrice
  'do calculation
  drow("Tax") = Tax
  drow("Total") = Total

End Sub

Lets just dive in at the start.  The first function bindData() is used to access the DataSource, fill the table and bind the table to the control.  I have used a small XML file for example data but, any dataset could be used such as a DataSet returned from a Sql call.  The only difference in using a datasource from a Sql call would be that you only have to define columns you are adding and not columns which will be automatically filled from the column names in the corresponding sql table.

We must first add our table to the dataset.  In this case it is named "Table" to correspond to the name of the XML data in the file we will load later in the function.  Next we add each column defined in the Xml file and also associate the new column with a data type that will aid in sorting.  Now that our new table is built we can load the data from the XML file.  The two columns 'Tax' and 'Total' are not found in the Xml file so they are not assigned values.

Now we have all our data accessible we want to do all our calculations.  Directly after the call to ReadXml() the program loops through all rows in the DataTable and passes each row individually to our calculation function bindRow().   Bind row will then pull each price retrieved from the XML file and perform our fictional calculations.  These calculations are then loaded back into the DataRow as the data type we assigned when we created these columns in bindData() . 

After all rows have been edited we create a DataView from our information and apply a sorting statement similar to a Sql 'Order By'.  Which I will show a little later. The DataView we created can now be tied to our DataGrid and the information bound.

DataGrid Specification

Here is what the actual DataGrid looks like in the aspx file:

<asp:datagrid id="DataGrid" runat="server" autogeneratecolumns="False" 
      allowsorting="True" bordercolor="#E0E0E0">
  <itemstyle backcolor="#E0E0E0"></itemstyle>
  <headerstyle backcolor="Silver"></headerstyle>
  <columns>
    <asp:boundcolumn datafield="ItemName" sortexpression="ItemName" 
      headertext="Item Name"></asp:boundcolumn>
    <asp:boundcolumn datafield="ItemPrice" sortexpression="ItemPrice" 
      headertext="Item Price" dataformatstring="{0:c}"></asp:boundcolumn>
    <asp:boundcolumn datafield="Tax" sortexpression="Tax" headertext="Tax" 
      dataformatstring="{0:c}"></asp:boundcolumn>
    <asp:boundcolumn datafield="Total" sortexpression="Total" 
      headertext="Total" dataformatstring="{0:c}"></asp:boundcolumn>
  </columns>
</asp:datagrid>
<asp:label id="lblOrderDir" runat="server" visible="False">
  ASC</asp:label>
<asp:label id="lblOrderBy" runat="server" visible="False">
  ItemName</asp:label>
    

This is a simple datagrid which defines that we will be using paging and creates four bound columns which relate to the column names we created in the previous step.  We also will format our data to currency so that we will get a standard look for all column values regardless of the calculations.

Also two invisible labels are created with default sorting variables that will persist no matter what other actions are taken by other elements in the page. Now all we have to do is handle what is done when a header is clicked that has a sort expression related to it.

Sorting 101

Below is a function used to catch when a sort command has been clicked:

Private Sub DataGrid_SortCommand(ByVal source As Object, _
    ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) _ 
    Handles DataGrid.SortCommand
  'this code is used to reverse the direction of the sortd

  If lblOrderBy.Text = e.SortExpression Then
    If lblOrderDir.Text = "ASC" Then
      lblOrderDir.Text = "DESC"
    Else
      lblOrderDir.Text = "ASC"
    End If
  Else
    lblOrderDir.Text = "ASC"
  End If

  lblOrderBy.Text = e.SortExpression

  bindData()
End Sub
    

The sorting code is pretty straight forward. It first checks to see if the sort expression passed to the function is the same as the previous sort. If it is the same it flips the directions of the order.  Otherwise it assigns the default direction and assigns the new column to be used to sort the DataView in bindData().

Points of Interest

Always remember to check for post back so that you do not rebind the data again after you sort in Page_Init(). Also you can not assign a data type to a column after it has been added to the dataset. To create and edit you extra columns the table has to be created first and then the data can be loaded into the DataSet.

History

• 08/26/03: Article Submitted