JavaScript Editor jscript editor     Web designer 
↑


Main Page

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:

Visual BasicВ CopyCode imageCopy Code
<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" />
C#В CopyCode imageCopy Code
<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:

Visual BasicВ CopyCode imageCopy Code
<asp:ObjectDataSource 
  ID="EmployeesObjectDataSource" 
  runat="server" 
  TypeName="Samples.AspNet.Controls.NorthwindEmployee" 
  SortParameterName="SortColumns"
  EnablePaging="true"
  StartRowIndexParameterName="StartRecord"
  MaximumRowsParameterName="MaxRecords" 
  SelectMethod="GetAllEmployees" >
</asp:ObjectDataSource>
C#В CopyCode imageCopy Code
<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.

Visual BasicВ CopyCode imageCopy Code
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
C#В CopyCode imageCopy Code
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.

See Also



JavaScript Editor jscript editor     Web designer 
→