How to Non-alphabetically Sort GridView Columns with Text Data

Introduction

Regular sorting of a GridView is a relatively simple procedure. However, there is no default mechanism for sorting a GridView on a column that contains text (alphabetic) data but needs to be sorted in a non-alphabetical way. A column containing months (or their abbreviations) is a good example. Below, we'll see how to do just that.

Background

When implemented declaratively, GridView sorting in ASP.NET relies on the underlying data source's sorting mechanisms. This means that a SqlDataSource will sort alphabetically or numerically. You can sort your GridView differently in the code-behind, or provide another column to the data source that can be sorted in one of these two ways.

Using the Code

Let's assume we have a table that stores the number of call records a customer service representative has closed each month. The table design looks like this:

The following code creates a Stored Procedure that returns the values needed to populate a GridView:

CREATE PROCEDURE CallsByRepAndMonth
AS
SELECT CustomerServiceRep, 
	Convert(char(3), CallDate, 107) AS [Month],
	Count(Month(CallDate)) AS [Calls]
FROM CallHistory
GROUP BY CustomerServiceRep, Convert(char(3), CallDate, 107), 
Month(CallDate)

Here's the code in the ASPX page that creates a SqlDataSource to return the data and a GridView to display it:

<asp:SqlDataSource SelectCommand="exec CallsByRepAndMonth"
    ConnectionString="server=localhost; integrated security=true; 
                      initial catalog=CustomerService" 
    runat="server" ID="dsCalls">
<asp:GridView AllowSorting="true" AutoGenerateColumns="true" 
    DataSourceID="dsCalls" runat="server" ID="GVCalls" />

If you view this page in a browser and click on the Month column header, you will see this:

Notice how the GridView is alphabetically sorted by the name of the month. You can always scan down through the list knowing where to find the name of the month you are seeking. It would be nice, though, if the GridView would sort automatically by the cardinal number representing the month, e.g., putting January first and February second, etc.

This can be accomplished fairly easily by returning another column in the Stored Procedure. The new Stored Procedure looks like this:

CREATE PROCEDURE CallsByRepAndMonth_WithNumber
AS
SELECT CustomerServiceRep, 
	Convert(char(3), CallDate, 107) AS [Month],
	Month(CallDate) AS MonthNumber,
	Count(Month(CallDate)) AS [Calls]
FROM CallHistory
GROUP BY CustomerServiceRep, Convert(char(3), CallDate, 107), 
Month(CallDate)

The new code for the SqlDataSource and the GridView:

<asp:SqlDataSource SelectCommand="exec CallsByRepAndMonth_WithNumber" 
    ConnectionString="server=localhost; integrated security=true; 
                      initial catalog=CustomerService" 
    runat="server" ID="dsCallsWithNumber">
<asp:GridView AllowSorting="true" AutoGenerateColumns="false" 
    DataSourceID="dsCallsWithNumber" runat="server" ID="GVCallsWithNumber">
    <Columns>
      <asp:BoundField SortExpression="CustomerServiceRep" 
      HeaderText="Customer Service Rep" DataField="CustomerServiceRep">
      <asp:BoundField SortExpression="MonthNumber" 
      HeaderText="Month" DataField="Month">
      <asp:BoundField SortExpression="Calls" DataField="Calls" HeaderText="Calls"</columns />>
    </Columns>

See how the AutoGenerateColumns property is set to false? This means we have to specify the bound columns. There are three columns, just as there were in the previous GridView, and each column specifies the SortExpression attribute. The second column displays the names of the months, but notice that its SortExpression is set to MonthNumber. This is the numerical value for the month returned from the Stored Procedure. There is no GridView column bound to this field returned by the Stored Procedure. It has been included in the Stored Procedure merely to allow for the column to be sorted.

Fire up your browser and click on the Month column to sort the GridView. You will see the following on odd-numbered sort attempts...

... and this one on even-numbered sorts:

It is now sorting by the month's numerical value rather than alphabetically by month name.

History

• 21^st April, 2009: Initial post
• 22^nd April, 2009: Article updated - simplified queries