The Part 1 example has slight limitation on the programmer side who wants to use the code above as is. The limitation is he/she has to remember to set the sortexpression property of column in datagrid as database field name followed by sort direction at design time e.g. lastname ASC.
The following example illustrates the use of adding sortexpression and sortdirection dynamic attributes to datagrid, maintain their state through view state and sort data in datagrid using dataview. The sortexpression property of datagrid set in this example is to generate sortcommand event only. The sortexpression attribute of datagrid stores last column name used for sorting and sortdirection attribute stores last direction used for sorting.
Design Time:
- Drag and drop a DataGrid on the form.
- Set AllowSorting property of the grid to True.
- Set AutogenerateColumns property of the grid to False.
- Add bound columns to datagrid and set their properties as follows. The sortexpression property refers to the name of column in database table.
Note: Employees table from Northwind database is being used to illustrate the purpose.
DataTextField |
Header Text |
Sort Expression |
EmployeeID |
Employee ID |
EmployeeID |
LastName |
Last Name |
LastName |
FirstName |
First Name |
FirstName |
Title |
Title |
Title |
City |
City |
City |
Country |
Country |
Country |
Run-time:
Pseudo logic:
- Add dynamic attributes sortexpression and sortdirection to datagrid first time the page is loaded.
- Assign appropriate column name and sort order to sortexpression and sortdirection attribute respectively to sort data in order to get initial look and feel of data when the page is loaded first time.
- Get dataset and dataview objects. Set sort property of dataview object to concatenated string built using sortexpression and sortdirection dynamic attributes.
- Bind datagrid using dataview object.
- On every consecutive page loads and column clicks, trap sortcommand event of datagrid.
- Compare sortexpression property of the event argument obtained from sortcommand event with dynamic attribute sortexpression.
- If Match is found then
- Check sortdirection attribute.
- If sortdirection equals “ASC” then
i. Assign DESC to sortdirection attribute.
- Else
i. Assign ASC to sortdirection attribute.
- End If
- Repeat steps from Step 3 onwards.
Note: When you run the following code and click the same column two or more times then sort direction is reversed automatically for that column. However if column 3 is clicked followed by column 1 and 2 click then column 3 is sorted only in ascending direction no matter what the sort direction was before for the same column. This is because previous sort order is not preserved in the sortexpression property of the column. The overall result is same depending how you perceive it. This example teaches you how to make use of dynamic attributes and dataview object.
VB.Net Code:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
If DataGrid1.Attributes("SortExpression") Is Nothing Then
DataGrid1.Attributes("SortExpression") = "LastName"
DataGrid1.Attributes("SortDirection") = "ASC"
End If
BindDataGrid()
End If
End Sub
Private Sub BindDataGrid()
Dim cn As SqlConnection
Dim cmdSelect As SqlCommand
cn = New SqlConnection("Server=amandrek;UID=sa;PWD=;Database=Northwind")
Dim strSQL As String = "Select * From employees"
Dim da As New SqlDataAdapter(strSQL, cn)
Dim ds As New DataSet()
da.Fill(ds, "Table1")
Dim dv As DataView = ds.Tables(0).DefaultView
Dim SortExpression As String = DataGrid1.Attributes("SortExpression")
Dim SortDirection As String = DataGrid1.Attributes("SortDirection")
dv.Sort = SortExpression + " " + SortDirection
DataGrid1.DataSource = dv
DataGrid1.DataBind()
cn.Close()
End Sub
Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles DataGrid1.SortCommand
Dim SortExpression As String = e.SortExpression
Dim SortDirection As String = "ASC"
If SortExpression.Equals(DataGrid1.Attributes("SortExpression").ToString()) Then
If DataGrid1.Attributes("SortDirection").ToString().StartsWith("ASC") Then
SortDirection = "DESC"
Else
SortDirection = "ASC"
End If
End If
DataGrid1.Attributes("SortExpression") = SortExpression
DataGrid1.Attributes("SortDirection") = SortDirection
BindDataGrid()
End Sub