Transact SQL Other Articles Software Reviews
Numerically Sorting DataView Columns in the .NET Framework
In Microsoft's .NET Framework used in ASP.NET, C# and VB.NET the Sort() method can be used to sort datasets by a specified column. An obvious limitation of the Sort() method is that it only sorts alphabetically, so a column with a value of 1000 will appear before a column with a value of 10.
A possible solution to this is to output an extra column in the SQL result set that populates the DataSet from which the DataView is derived. The trick is to create a new column that prefixes zeros to numerical values, so that for example, a column with a value of 1 will become 00001 and 100 will become 00100. This column can then be used to sort the existing column.
The SQL Statement
The SQL code example below for SQL Server uses this technique to allow a DataGrid to be produced whereby the employee's telephone extension number is sortable. Since the extension numbers can be either a three or a four digit number, this allows three digit numbers to be returned before four digit numbers. Note that the number 6 in the code refers to the number of times the zero is replicated in the (i.e. the length of the number to be returned). If longer numbers occur in the column then this number will have to be increased accordingly.
SELECT EmployeeID, FirstName, LastName, Extension,
Creating the DataGrid
Don't forget that the new column doesn't have to be displayed within the DataGrid, it just needs to be returned with the SQL results set. This can be achieved because columns in a DataGrid have both a DataField property (i.e. the column to be displayed on the page) as well as a SortExpression property (i.e. the column used to sort the data by in this column). The example below shows a BoundColumn displaying the data for the employees data returned by the SQL statement shown above:
<asp:BoundColumn HeaderText="First Name"
A similar solution should be possible with other databases such as Oracle or MySQL.