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 ASC |
LastName |
Last Name |
LastName ASC |
FirstName |
First Name |
FirstName ASC |
Title |
Title |
Title ASC |
City |
City |
City ASC |
Country |
Country |
Country ASC |
Run-time:
Pseudo logic:
- Trap sortcommand event of the datagrid control.
- Read sortexpression property of the event argument.
- Sort data in grid using current sort expression.
- Use split function to separate column name (which represents database field name) and sort direction. The first element of an array contains database field name.
- If sort direction is ASC then new sort direction is DESC and vice versa. Save the new direction into 2nd element of the array.
- Loop through all columns
If column’s sort expression matches sort expression returned from event argument then
Concatenate first element of an array, space and second element of an array to
build a new sort expression.
Assign new sort expression to current column’s sortexpression property.
Exit loop
End If
End Loop
Note: In order for this example to work properly write binding SQL query with order by clause with no mention of column names. The column names are appended at run-time to the binding SQL query.
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
BindDataGrid("EmployeeID ASC")
End If
End Sub
Private Sub BindDataGrid(ByVal strSortField As String)
Dim cn As SqlConnection
Dim cmdSelect As SqlCommand
cn = New SqlConnection("Server=localhost;UID=sa;PWD=;Database=Northwind")
cmdSelect = New SqlCommand("Select * From employees Order By " & strSortField, cn)
cn.Open()
DataGrid1.DataSource = cmdSelect.ExecuteReader()
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 arrSortExpr() As String
Dim i As Integer
If e.SortExpression = "" Then Return
BindDataGrid(e.SortExpression)
arrSortExpr = Split(e.SortExpression, " ")
For i = 0 To DataGrid1.Columns().Count - 1
If (DataGrid1.Columns(i).SortExpression = e.SortExpression) Then
If UCase(arrSortExpr(1)) = "ASC" Then
arrSortExpr(1) = "DESC"
ElseIf UCase(arrSortExpr(1)) = "DESC" Then
arrSortExpr(1) = "ASC"
End If
DataGrid1.Columns(i).SortExpression = arrSortExpr(0) & " " & arrSortExpr(1)
Exit For
End If
Next
End Sub