System ObjectCleanCode.Data MultiColumnSort
Namespace: CleanCode.Data
Assembly: CleanCode (in CleanCode.dll) Version: 1.2.3.0 (1.2.03)
This class parses a SQL query, identifies the fields desired for sorting (from the ORDER BY clause), maps any aliases (from the "xxx as yyy" parts of the SELECT clause), then generates a new ORDER BY clause to feed to the Sort property of a DataView where the elements refer to the DataTable columns rather than the database columns.
Note that any compound expressions (e.g. len(name)) must have an alias. Though, for example, SQL Server could process this query:
select name, len(name) from accounts order by len(name)
select name, len(name) as myLength from accounts order by myLength
select name, len(name) as myLength from accounts order by len(name)
There are some drivers, however (notably the Microsoft Jet driver used by Access as well as ODBC/CSV connections) that do not allow aliases in the order by clause. In those situations, you must define the alias but then you must use the original expression in the order by, as in the last example above.
Some valid SQL code will not quite work properly due to a limitation of MultiColumnSort. An expression such as this:
SELECT users.name FROM users ORDER BY users.name
SELECT users.name as name FROM users ORDER BY users.name
-- Unaliased simple name: SELECT name FROM NameTable ORDER BY name -- Aliased simple name ordered by alias: SELECT name as myName FROM NameTable ORDER BY myName -- Aliased simple name ordered by name: SELECT name as myName FROM NameTable ORDER BY name -- Aliased compound name ordered by alias: SELECT table.name as myName FROM NameTable ORDER BY myName -- Aliased compound name ordered by compound name: SELECT table.name as myName FROM NameTable ORDER BY table.name -- Aliased expression ordered by alias: SELECT len(name) as myNameLength FROM NameTable ORDER BY myNameLength -- Aliased expression ordered by expression: SELECT len(name) as myNameLength FROM NameTable ORDER BY len(name) -- Delimited simple name ordered by non-delimited name: SELECT [name] FROM NameTable ORDER BY name -- Delimited simple name ordered by delimited name: SELECT [name] FROM NameTable ORDER BY [name] -- Aliased, delimited compound name ordered by delimited, compound name: SELECT [nt].[name] as myName FROM NameTable nt ORDER BY [nt].[name] -- Aliased, delimited compound name ordered by alias: SELECT [nt].[name] as myName FROM NameTable nt ORDER BY myName
MultiColumnSort is flexible in field name delimiters: you may use square brackets as shown in the table as well as single quotes, double quotes, or backquotes. Since different databases have different requirements on these, MultiColumnSort allows the superset.
MultiColumnSort uses simple parsing techniques; it is not designed to handle overly complex queries. For example, the parser expects that the list of fields in the ORDER BY clause is comprised of everything from the ORDER BY literal until the end of the query text. If it is not, it will not return a valid result. You can provide a hint, though, for cases where it cannot properly identify the ORDER BY clause, such as when there is cleanup following, like this (note the 4-character rather than 2-character comment prefix):
SELECT field1, field2, field3 FROM #myTempTable ---- [ORDERING START] ORDER BY field1, field2, field3 ---- [ORDERING END] DROP TABLE #myTempTable
Similarly, a complex query that may have multiple select statements could befuddle the simple parser, so use [SELECT START] and [SELECT END] brackets from the SELECT keyword up to and including the FROM keyword.
---- [SELECT START] SELECT field1, field2, field3 FROM #myTempTable ---- [SELECT END] ---- [ORDERING START] ORDER BY field1, field2, field3 ---- [ORDERING END] DROP TABLE #myTempTable
Since CleanCode 0.9.13.