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
ELSE
    replicate('0', 6)
END
    NumericExtension

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.

Useful Development Tools

ASP Documentation Tool™

Automatically creates developer documentation for ASP 2.0 and 3.0 web applications written in VBScript and JScript. Documentation for Microsoft Access, SQL Server 7/2000 databases and Visual Basic 6.0 components associated with the web application can also be incorporated into the reports. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5.2Mb ZIP file).

.NET Documentation Tool

Automatically creates technical documentation for .NET Framework Windows and ASP.NET applications written in C# or VB.NET and SQL Server 7/2000/2005 or Microsoft Access databases associated with the application. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5Mb ZIP file).

SQL Documentation Tool

The SQL Documentation Tool creates technical documentation for Microsoft SQL Server 7.0 and 2000 databases. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced. The SQL Documentation Tool documents SQL Server Tables, Views, Stored Procedures, Triggers and Table Relationships.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (10.3Mb ZIP file).

VB Documentation Tool

The VB Documentation Tool creates technical documentation for Microsoft Visual Basic 6.0 projects. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1Mb ZIP file).

Indexing Service Companion

The Indexing Service Companion is a Windows application that extends the functionality of the Microsoft Windows Indexing Service so that it is able to index content from remote websites and also from ODBC databases. As such it can be used as a low cost alternative to Sharepoint Portal Search Services.

Try Sample Search Facility.     Download Trial Version (1.7Mb ZIP file).

The Website Utility

The Website Utility examines websites for errors and areas that need to be optimised for search engines by using a built in web crawling engine. Errors checked for include broken or moved hyperlinks, missing page titles and missing meta tags. It also generates HTML for use in creating website site maps (table of contents pages - like this one), and is able to create both client-side JavaScript Search Engines and server-side ASP Search Engines for a website.

View Sample Output (HTML Format).     Download Trial Version (3Mb ZIP file).

PHP Documentation Tool™

Automatically creates developer documentation for PHP web applications. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1.0Mb ZIP file).