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.RowStateFilter=DataViewRowState.CurrentRows;
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;
}
and Ariel Constantinof
.
here