Friday, October 27, 2006

Sort datatable with the Select method when column names contain commas

Well, basically, you can't do it.
I am looking at the internal ParseSortString(string sortString) in the DataTable object of NET 1.1, where the first thing the method does is to split the string by commas, then check for square brackets. This insures that there is no way to sort datatables by columns with commas in their names. The funny thing is that the filtering expression is treated like royalty by an ExpressionParser object, and allows column names with commas inside.
Now let's check the code for NET 2.0. It's identical.

The solution is a little annoying code like this:
private DataRow[] SelectSafe(DataTable dt, string filter, string sort)
{
  var columns = new string[dt.Columns.Count];
  for (var c=0; c<dt.Columns.Count; c++)
  {
    columns[c] = dt.Columns[c].ColumnName;
    if (dt.Columns[c].ColumnName.IndexOf(',')>-1)
    {
      dt.Columns[c].ColumnName = dt.Columns[c].ColumnName.Replace(',', ';');
      // assume that the column name was bracketed correctly in the select
      sort = sort.Replace(
        "[" + columns[c] + "]",
        "[" + dt.Columns[c].ColumnName + "]");
    }
  }
  var dr = dt.Select(filter, sort);
  for (int c=0; c<dt.Columns.Count; c++) {
    dt.Columns[c].ColumnName = columns[c];
  }
  return dr;
}


I am sure there is more elegant code, but this seems to be the only solution so far except manually sorting a DataTable.

0 comments: