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
- 21st April, 2009: Initial post
- 22nd April, 2009: Article updated - simplified queries