Search: Go
 Transact SQL
 Other Articles
 Software Reviews

 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > ASP.NET Articles

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,

CASE WHEN isnumeric(Extension) = 1 THEN
    replicate('0', 6 - len(Extension)) + Extension
    replicate('0', 6)

FROM Employees

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" DataField="FirstName" SortExpression="FirstName"></asp:BoundColumn>
<asp:BoundColumn HeaderText="Last Name" DataField="LastName" SortExpression="LastName"></asp:BoundColumn>
<asp:BoundColumn HeaderText="Employee ID" DataField="
Extension" SortExpression="NumericExtension"></asp:BoundColumn>

A similar solution should be possible with other databases such as Oracle or MySQL.

  Site Map | Privacy Policy

All content is 1995 - 2012