Generates a sorted view of a DataGridView based on a query.

Parameters

query

A SQL query.

dgv

A DataGridView.

Return Value

Returns a DataView sorted per the specified query.

Remarks

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)

the MultiColumnSort can only use the actual column names in the result set. Since an unaliased column will get a generic "Columnn" label, it could not easily be associated with the compound expression. The solution is to require an alias as in:

select name, len(name) as myLength
from accounts
order by myLength

This allows MultiColumnSort to convert the specified sort expression of len(name) to a realized sort expression of myLength. Note that most databases (SQL Server and Oracle, for example) allow you to use either the actual expression or the alias in the order by clause itself, // so MultiColumnSort allows this as well. Thus this will also work:

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.

Exceptions

See Also

MultiColumnSort Class | CleanCode.Data Namespace

---