When we use the DataMap property of the C1Flexgrid to associate cell values with display values in a column and sort this column, then sorting is based on the actual values, i.e., Key value. Many customers require the sorting of this datamap column with the display values and this blog shows how we can achieve this requisite. We will begin with binding C1Flexgrid to a datatable. Save Products table of Northwind database and then create two objects of Dictionary objects which can be set to the DataMap property of SupplierID and CategoryID columns. The code below is used to display CompanyName instead of SupplierID and CategoryID with CategoryName:
// load data into some tables
var dtProducts = GetDataTable("Products");
var dtCategories = GetDataTable("Categories");
var dtSuppliers = GetDataTable("Suppliers");
// create two data maps
var mapCat = new Dictionary<int, string>();
foreach (DataRow row in dtCategories.Rows)
{
var key = (int)row["CategoryID"];
var val = (string)row["CategoryName"];
mapCat[key] = val;
}
var mapSup = new Dictionary<int, string>();
foreach (DataRow row in dtSuppliers.Rows)
{
var key = (int)row["SupplierID"];
var val = (string)row["CompanyName"];
mapSup[key] = val;
}
// bind grid
_flex.DataSource = dtProducts;
// add data maps
_flex.Cols["CategoryID"].DataMap = mapCat;
_flex.Cols["SupplierID"].DataMap = mapSup;
If we run the above code and try to sort the CategoryID/SupplierID column then it will be sorted by the key values. Now, the real work begins. The trick is to handle the BeforeSort event to add a new column to the DataTable. This new column is made invisible and is filled with the mapped values. The grid will then sort the table based on this new column rather than on the one that was clicked. Hence, sorting will be on the basis of description instead of key values. The code will look like:
// name of the hidden column used to perform the actual sorting
const string SORT\_COL\_NAME = "_dtSort";
// column being used as a source for the hidden source column
C1.Win.C1FlexGrid.Column _sourceColumn;
// apply custom sort (use mapped values instead of raw)
void \_flex\_BeforeSort(object sender, C1.Win.C1FlexGrid.SortColEventArgs e)
{
// if the column contains a data map
\_sourceColumn = \_flex.Cols[e.Col];
if (_sourceColumn.DataMap != null)
{
// add a hidden column just for sorting
var dt = _flex.DataSource as DataTable;
if (!dt.Columns.Contains(SORT\_COL\_NAME))
{
var dcol = dt.Columns.Add(SORT\_COL\_NAME);
\_flex.Cols[SORT\_COL_NAME].Visible = false;
}
// remove current sort (if any)
dt.DefaultView.Sort = string.Empty;
// populate the sort column
foreach (DataRow dr in dt.Rows)
{
var key = dr[_sourceColumn.Name];
dr[SORT\_COL\_NAME] = _sourceColumn.DataMap[key];
}
// apply the new sort
var sort = SORT\_COL\_NAME;
if ((e.Order & C1.Win.C1FlexGrid.SortFlags.Descending) != 0)
{
sort += " DESC";
}
dt.DefaultView.Sort = sort;
// handled
e.Handled = true;
}
}
One thing left is handling the AfterEdit event to update the content of the hidden sort column in case the user edits the column that contains the data map so the sorting is updated automatically. The following is the code for the same:
// update sort when the user edits the sorted column
void \_flex\_AfterEdit(object sender, C1.Win.C1FlexGrid.RowColEventArgs e)
{
// if the user edited the column being sorted on
if (\_flex.Cols[e.Col] == \_sourceColumn)
{
// refresh the content of the sort column
var dt = _flex.DataSource as DataTable;
foreach (DataRow dr in dt.Rows)
{
var key = dr[_sourceColumn.Name];
dr[SORT\_COL\_NAME] = _sourceColumn.DataMap[key];
}
}
}
Hunter Haaf