Sorting Data with Data Source Controls
The GridView control can order the data that it is bound to by taking advantage of the sorting capabilities provided by data source controls.
Enabling Sorting in a Data Source Control
The data source controls that support sorting are the ObjectDataSource, SqlDataSource, and AccessDataSource controls. The SqlDataSource and AccessDataSource controls support sorting only when their DataSourceMode property is set to DataSet (the default) as in the following example:
<asp:GridView ID="EmployeesGridView"
DataSourceID="EmployeesSqlDataSource"
DataKeyNames="EmployeeID"
AllowSorting="True"
RunAt="Server" />
<asp:SqlDataSource ID="EmployeesSqlDataSource"
SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees"
Connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
RunAt="server" />
<asp:GridView ID="EmployeesGridView"
DataSourceID="EmployeesSqlDataSource"
DataKeyNames="EmployeeID"
AllowSorting="True"
RunAt="Server" />
<asp:SqlDataSource ID="EmployeesSqlDataSource"
SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees"
Connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
RunAt="server" />
The ObjectDataSource control supports sorting if the object returned by the SelectMethod is a DataSet, DataTable, or DataView object. The ObjectDataSource also supports retrieving results in sorted order from the data source.
Custom Sorting
When using the ObjectDataSource or SqlDataSource controls, you can take advantage of sorting capabilities using the SortParameterName property. You can set the SortParameterName property to the name of the parameter that contains a sort expression being passed to the data source control. The sort expression is a comma-delimited list of fields to sort by (and optionally the DESC identifier to sort in descending order). For details about the format of the sort expression, see the System.Data.DataView.Sort property.
The parameter identified by the SortParameterName property is passed to the ObjectDataSource control's SelectMethod or passed as part of the parameter collection to the SqlDataSource control's SelectCommand. The ObjectDataSource control can use the information passed to it in the sort parameter to return the data in sorted order. For the SqlDataSource control, you must supply the name of a stored procedure that can take the sort parameter and return the sorted data, because you cannot pass a parameter as part of an ORDER BY clause.
The following code example shows an ObjectDataSource control declaration that identifies a parameter named sortColumns as the sort parameter name:
<asp:ObjectDataSource
ID="EmployeesObjectDataSource"
runat="server"
TypeName="Samples.AspNet.Controls.NorthwindEmployee"
SortParameterName="SortColumns"
EnablePaging="true"
StartRowIndexParameterName="StartRecord"
MaximumRowsParameterName="MaxRecords"
SelectMethod="GetAllEmployees" >
</asp:ObjectDataSource>
<asp:ObjectDataSource
ID="EmployeesObjectDataSource"
runat="server"
TypeName="Samples.AspNet.Controls.NorthwindEmployee"
SortParameterName="SortColumns"
EnablePaging="true"
StartRowIndexParameterName="StartRecord"
MaximumRowsParameterName="MaxRecords"
SelectMethod="GetAllEmployees" >
</asp:ObjectDataSource>
The following code example shows a method in the source object for the ObjectDataSource control. The method is identified as the SelectMethod. The parameter identified by the SortParameterName property is used to sort the data retrieved from the database.
Public Shared Sub Initialize()
' Initialize data source. Use "Northwind" connection string from configuration.
If ConfigurationManager.ConnectionStrings("Northwind") Is Nothing OrElse _
ConfigurationManager.ConnectionStrings("Northwind").ConnectionString.Trim() = "" Then
Throw New Exception("A connection string named 'Northwind' with a valid connection string " & _
"must exist in the <connectionStrings> configuration section for the application.")
End If
_connectionString = _
ConfigurationManager.ConnectionStrings("Northwind").ConnectionString
_initialized = True
End Sub
' Select all employees.
<DataObjectMethod(DataObjectMethodType.Select, True)> _
Public Shared Function GetAllEmployees(sortColumns As String, startRecord As Integer, maxRecords As Integer) As DataTable
VerifySortColumns(sortColumns)
If Not _initialized Then Initialize()
Dim sqlCommand As String = "SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode FROM Employees "
If sortColumns.Trim() = "" Then
sqlCommand &= "ORDER BY EmployeeID"
Else
sqlCommand &= "ORDER BY " & sortColumns
End If
Dim conn As SqlConnection = New SqlConnection(_connectionString)
Dim da As SqlDataAdapter = New SqlDataAdapter(sqlCommand, conn)
Dim ds As DataSet = New DataSet()
Try
conn.Open()
da.Fill(ds, startRecord, maxRecords, "Employees")
Catch e As SqlException
' Handle exception.
Finally
conn.Close()
End Try
If ds.Tables("Employees") IsNot Nothing Then _
Return ds.Tables("Employees")
Return Nothing
End Function
'''''
' Verify that only valid columns are specified in the sort expression to aSub a SQL Injection attack.
Private Shared Sub VerifySortColumns(sortColumns As String)
If sortColumns.ToLowerInvariant().EndsWith(" desc") Then _
sortColumns = sortColumns.Substring(0, sortColumns.Length - 5)
Dim columnNames() As String = sortColumns.Split(",")
For Each columnName As String In columnNames
Select Case columnName.Trim().ToLowerInvariant()
Case "employeeid"
Case "lastname"
Case "firstname"
Case ""
Case Else
Throw New ArgumentException("SortColumns contains an invalid column name.")
End Select
Next
End Sub
public static void Initialize()
{
// Initialize data source. Use "Northwind" connection string from configuration.
if (ConfigurationManager.ConnectionStrings["Northwind"] == null ||
ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString.Trim() == "")
{
throw new Exception("A connection string named 'Northwind' with a valid connection string " +
"must exist in the <connectionStrings> configuration section for the application.");
}
_connectionString =
ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
_initialized = true;
}
// Select all employees.
[DataObjectMethod(DataObjectMethodType.Select, true)]
public static DataTable GetAllEmployees(string sortColumns, int startRecord, int maxRecords)
{
VerifySortColumns(sortColumns);
if (!_initialized) { Initialize(); }
string sqlCommand = "SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode FROM Employees ";
if (sortColumns.Trim() == "")
sqlCommand += "ORDER BY EmployeeID";
else
sqlCommand += "ORDER BY " + sortColumns;
SqlConnection conn = new SqlConnection(_connectionString);
SqlDataAdapter da = new SqlDataAdapter(sqlCommand, conn);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds, startRecord, maxRecords, "Employees");
}
catch (SqlException e)
{
// Handle exception.
}
finally
{
conn.Close();
}
if (ds.Tables["Employees"] != null)
return ds.Tables["Employees"];
return null;
}
//////////
// Verify that only valid columns are specified in the sort expression to avoid a SQL Injection attack.
private static void VerifySortColumns(string sortColumns)
{
if (sortColumns.ToLowerInvariant().EndsWith(" desc"))
sortColumns = sortColumns.Substring(0, sortColumns.Length - 5);
string[] columnNames = sortColumns.Split(',');
foreach (string columnName in columnNames)
{
switch (columnName.Trim().ToLowerInvariant())
{
case "employeeid":
break;
case "lastname":
break;
case "firstname":
break;
case "":
break;
default:
throw new ArgumentException("SortColumns contains an invalid column name.");
break;
}
}
}
For more information, see Creating an ObjectDataSource Control Source Object.