Export (0) Print
Expand All

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" />

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>

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 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

Community Additions

ADD
Show:
© 2014 Microsoft