ASP.Net Web Developer Guide |
||||||||
|
||||||||
Sitemap : Content in Chapter 1 |
Content in Chapter 2 |
Content in Chapter 3 |
||||||||
|
||||||||
Tuesday, March 28, 2006
Sorting DataGrid
Yes, on click of any of the column headers, we can dynamically sort the records of a data grid. However, please bear in mind that the DataGrid itself does not provide the sorting algorithm. It rather provides a mechanism to enable us to call a sorting routine. Fortunately, in our example (as shown in Figure 3.80), we do not need to implement a sorting algorithm ourselves.We have used the SQL ORDER BY clause to automatically sort the retrieved data.
Figure 3.80 Sorting Data in a DataGrid Control
The code for this application is shown in Figure 3.81. On the click event of a column header, our intention is to exploit the SQL’s ORDER BY clause to perform the sorting.This forces us to recreate the data set and subsequently to rebind the data grid. Please observe that we have designed the bindDataGrid routine slightly differently from the similar procedures in our previous examples.We included an optional parameter to this procedure so that we can pass a column name when we call this routine.This subprocedure will then extract the data from the database in the ascending order of the passed column. In the DataGrid tag, we have specified its AllowSorting property to be true. We have also set its OnSortCommand to a subprocedure named sortGrid. On the click event of any of the column header, the sortGrid subprocedure will be called.
Figure 3.81 DataGrid3.aspx
<!— Chapter3/DataGrid3.aspx —>
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" Debug="true" runat="server">
Sub Page_Load(Source As Object, E As EventArgs)
If Not IsPostBack Then
bindDataGrid
End If
End Sub
Sub bindDataGrid(Optional sortField As String="ProductId")
Dim myConn As OleDbConnection
Dim myOleDbAdapter As OleDbDataAdapter
Dim connStr, sqlStr As String
Dim myDataSet As New Dataset
connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Products.mdb"
sqlStr="SELECT ProductId, ProductName, Price " _
+ " FROM Products WHERE Price > 40 ORDER BY " +
sortField
myConn= New OleDbConnection(connStr)
myConn.Open()
myOleDbAdapter =New OleDbDataAdapter(sqlStr,myConn)
myOleDbAdapter.Fill(myDataSet,"dtProducts")
dataGrid1.DataSource=myDataSet.Tables("dtProducts")
dataGrid1.DataBind()
myConn.Close()
End Sub
Sub sortGrid(s As Object, e As DataGridSortCommandEventArgs)
bindDataGrid(e.sortExpression)
End Sub
</script>
<html><head></head><body><form runat="server"><center>
<h4>Click a column heading to sort</h4>
<asp:DataGrid runat="server" id="dataGrid1"
AutoGenerateColumns="true"
AllowSorting="true"
OnSortCommand="sortGrid"
Width="75%"
BackColor="White"
BorderWidth="1px" BorderStyle="Solid"
CellPadding="2" CellSpacing="0"
BorderColor="Salmon"
Font-Name="Verdana" Font-Size="8pt">
<HeaderStyle Font-Size="8" Font-Names="Arial"
Font-Bold="True" BackColor="Yellow"
HorizontalAlign="center">
</HeaderStyle>
<AlternatingItemStyle BackColor="Beige"/>
</asp:DataGrid>
</center></form></body></html>
Figure 3.80 Sorting Data in a DataGrid Control
The code for this application is shown in Figure 3.81. On the click event of a column header, our intention is to exploit the SQL’s ORDER BY clause to perform the sorting.This forces us to recreate the data set and subsequently to rebind the data grid. Please observe that we have designed the bindDataGrid routine slightly differently from the similar procedures in our previous examples.We included an optional parameter to this procedure so that we can pass a column name when we call this routine.This subprocedure will then extract the data from the database in the ascending order of the passed column. In the DataGrid tag, we have specified its AllowSorting property to be true. We have also set its OnSortCommand to a subprocedure named sortGrid. On the click event of any of the column header, the sortGrid subprocedure will be called.
Figure 3.81 DataGrid3.aspx
<!— Chapter3/DataGrid3.aspx —>
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" Debug="true" runat="server">
Sub Page_Load(Source As Object, E As EventArgs)
If Not IsPostBack Then
bindDataGrid
End If
End Sub
Sub bindDataGrid(Optional sortField As String="ProductId")
Dim myConn As OleDbConnection
Dim myOleDbAdapter As OleDbDataAdapter
Dim connStr, sqlStr As String
Dim myDataSet As New Dataset
connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Products.mdb"
sqlStr="SELECT ProductId, ProductName, Price " _
+ " FROM Products WHERE Price > 40 ORDER BY " +
sortField
myConn= New OleDbConnection(connStr)
myConn.Open()
myOleDbAdapter =New OleDbDataAdapter(sqlStr,myConn)
myOleDbAdapter.Fill(myDataSet,"dtProducts")
dataGrid1.DataSource=myDataSet.Tables("dtProducts")
dataGrid1.DataBind()
myConn.Close()
End Sub
Sub sortGrid(s As Object, e As DataGridSortCommandEventArgs)
bindDataGrid(e.sortExpression)
End Sub
</script>
<html><head></head><body><form runat="server"><center>
<h4>Click a column heading to sort</h4>
<asp:DataGrid runat="server" id="dataGrid1"
AutoGenerateColumns="true"
AllowSorting="true"
OnSortCommand="sortGrid"
Width="75%"
BackColor="White"
BorderWidth="1px" BorderStyle="Solid"
CellPadding="2" CellSpacing="0"
BorderColor="Salmon"
Font-Name="Verdana" Font-Size="8pt">
<HeaderStyle Font-Size="8" Font-Names="Arial"
Font-Bold="True" BackColor="Yellow"
HorizontalAlign="center">
</HeaderStyle>
<AlternatingItemStyle BackColor="Beige"/>
</asp:DataGrid>
</center></form></body></html>
posted by Mr.Q at 10:28 PM
0 Comments:
Post a Comment
<< Home