Introduction
The main purpose of this article 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 improve my coding style and techniques.
Sub bindData()
Dim myDataSet As DataSet = New DataSet
Dim i As Integer
Dim drow As DataRow
myDataSet.Tables.Add("Table")
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)
myDataSet.ReadXml(Server.MapPath("DataSet.xml"))
For i = 0 To myDataSet.Tables("Table").Rows.Count() - 1
drow = myDataSet.Tables("Table").Rows(i)
bindRow(drow)
Next
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
ItemPrice = CType(drow("ItemPrice"), Double)
Tax = ItemPrice * 0.076
Total = Tax + ItemPrice
drow("Tax") = Tax
drow("Total") = Total
End Sub
Let's 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
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 postback so that you do not rebind the data again after you sort in Page_Init()
. Also, you cannot assign a data type to a column after it has been added to the dataset
. To create and edit your extra columns, the table has to be created first and then the data can be loaded into the DataSet
.
History
- 08/26/2003: Article submitted
License
This article has no explicit license attached to it, but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below. A list of licenses authors might use can be found here.
I am a consultant for St Loiuis based Oakwood Systems Group. I work primarily in the .Net languages.