Sorting by Name in C1Flexgrid DataMap Column

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];  
         }  
     }  
 }

A complete sample implementing the same is attached. Download Sample