Wednesday, April 25, 2007

Buggy DataTable Select strikes again: Safe Filtering without Sorting

A while ago I wrote a post about the bug in the DataTable.Select method with columns with comma in their names.

Today I discovered another bug, when using DataTable.Select with an empty sort string, but not an empty filter string, there is an implicit sorting by the first column. Short example: a DataTable with a single column called "words" containing values c,b,a,d , when Selected with a filter like "words is not null" and a null or empty sort string, will return a,b,c,d.

The only solution for this was to drop DataTable.Select entirely and use DataView, with its NET 2.0 method DataView.ToTable. So the code to take a DataTable and return the filtered and sorted table would look like this:
public static DataTable Select(DataTable table, string filter, string sort)
    if (table == null) return null;
    var dv=new DataView(table);
    dv.RowFilter = filter;
    dv.Sort = sort;
    return dv.ToTable();

But DataView has the same problem with columns with comma in their names. We solve it in the same way we solved it in the previous post: we change the column names, the sort and filter strings, we select, then we change the column names back:
public static DataTable SelectSafe(this DataTable table, string filter, string sort)
    var originalColumnNames = new Dictionary<string, string>();
    foreach (DataColumn dc in table.Columns)
        if (dc.ColumnName.IndexOf(',') > -1)
            var columnName = dc.ColumnName;
            var safeColumnName = columnName.Replace(",", ";");
            var  reg = new Regex(Regex.Escape("[" + columnName + "]"), RegexOptions.IgnoreCase);
            dc.ColumnName = safeColumnName;
            if (!String.IsNullOrEmpty(filter)) {
                filter = reg.Replace(filter, "[" + safeColumnName + "]");
            if (!String.IsNullOrEmpty(sort)) {
                sort = reg.Replace(sort, "[" + safeColumnName + "]");
            originalColumnNames[safeColumnName] = columnName;

    var newTable = Select(table, filter, sort);

    foreach (KeyValuePair<string, string> pair in originalColumnNames)
        table.Columns[pair.Key].ColumnName = pair.Value;
        newTable.Columns[pair.Key].ColumnName = pair.Value;
    return newTable;