Sort using hidden column

[Declan]

I am attempting to use grid sorting by clicking on a column heading. This works as expected but I need to sort on a different hidden column when the user clicks a visible column.

RegNumber is the visible column.
RegKEY is a hidden column that I need to sort the grid by if the user clicks the heading on RegNumber.

I guess I need to trap the column heading click event and override the sort in some way by replacing the sort expression. I found some info suggesting I use the SortChanging event but I don't seem to be able to get this to work.

I have tried changing the sort field name without success and adding an expression to the MasterGridViewTemplate.

Any help or samples would be appreciated.

Declan

[Declan]

This link Sorting-on-hidden-column takes me in the right direction but the performance is very bad. There are 45000 + rows in the table and using this method takes on average 45 seconds to sort. Sorting on any other column is almost instant.

It would be quicket to relaod the data with "OrderBy fieldname". Is there a better way?

Declan

[Jack]

Hello Declan ,

Sorting a hidden column is easy, you should just set the SortOrder property. For example:

this.radGridView1.Columns["RegKEY"].IsVisible = false;
this.radGridView1.Columns["RegKEY"].SortOrder = RadSortOrder.Ascending;

However, we should do a little trick to change the sort order when you click on the visible column. If it isn't necessary to edit the data, just handle the CellFormatting event and show the data for the hidden column. Please consider the code snippet below:

void radGridView1_CellFormatting(object sender, CellFormattingEventArgs e)
{
    GridViewDataColumn column = e.CellElement.ColumnInfo as GridViewDataColumn;
    if (column.FieldName == "RegKEY")
    {
        e.CellElement.Text = e.CellElement.RowInfo.Cells["RegNumber"].Value.ToString();
    }
}

In this case we actually show the RegKEY column and fill the cells with data from the RegNumber column.

I hope this helps. Should you have further questions, don't hesitate to ask.

Greetings,
Jack
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Declan]

Hi Jack,

Pardon my ignorance, as I am a new Telerik WinForms user, but where should I set the sorting order. Is this in the CustomSorting event handler? I guess not as this would be called for each row. Is there a "Header" click event in which I could set the order to sort column? I can't see one.

Just as with a normal visible column I want subsequent clicks of the header to change the order: None -> Ascending -> Descending etc.

I guess what I am looking for is somewhere to trap a specific column header click and identify the column that was clicked. If it was "RegNumber" then I need to sort the grid on the hidden column "RegKey".

Would I use private void RadGridView1_Click(object sender, EventArgs e) and somehow identify that it was the header that was clicked and then check which column header? If so how?


The column RegNumber will be editable with the correct RegKey calculated at database update time.

Regards,
Declan

[Jack]

Hi Declan ,

This is a good question. When clicking on the header cell you sort directly by the RegKEY column. Actually, the RegNumber column remains hidden. You just use the values from this column when handling the CellFormatting event.

However, this solution doesn't help when you want to edit the RegKEY column. You should use a custom header cell to overcome this issue. Follow these steps:

1. Create a custom cell element that inherits from GridHeaderCellElement
2. Override the UpdateInfo and ArrangeOverride methods to gain control over the arrow element
3. Replace the default header cell for the desired column by handling the CreateCell event
4. Handle the ContextMenuOpening and replace the "Clear Sorting" menu item
5. Create a new grid behavior class that inherits from BaseGridBehavior and override its OnMouseUp event.

And here is the code:

this.radGridView1.CreateCell += new GridViewCreateCellEventHandler(radGridView1_CreateCell);
this.radGridView1.ContextMenuOpening += new ContextMenuOpeningEventHandler(radGridView1_ContextMenuOpening);
this.radGridView1.GridBehavior = new MyGridBehavior();

void radGridView1_ContextMenuOpening(object sender, ContextMenuOpeningEventArgs e)
{
    GridHeaderCellElement cell = e.ContextMenuProvider as GridHeaderCellElement;
    if (cell != null && cell.ColumnInfo.HeaderText == "RegNumber")
    {
        e.ContextMenu.Items.RemoveAt(2);
        RadMenuItem clearMenuItem = new RadMenuItem();
        clearMenuItem.Text = "Clear Sorting";
        clearMenuItem.Enabled = this.radGridView1.Columns["RegKEY"].SortOrder != RadSortOrder.None;
        e.ContextMenu.Items.Insert(2, clearMenuItem);
        clearMenuItem.Click += new EventHandler(clearMenuItem_Click);
    }

}

void clearMenuItem_Click(object sender, EventArgs e)
{
    this.radGridView1.MasterGridViewTemplate.SortExpressions.Clear();
}

void radGridView1_CreateCell(object sender, GridViewCreateCellEventArgs e)
{
    if (e.CellType == typeof(GridHeaderCellElement) && e.Column.HeaderText == "RegNumber")
    {
        e.CellElement = new MyHeaderCell(e.Column, e.Row);
    }
}

public class MyGridBehavior : BaseGridBehavior
{
    public override bool OnMouseUp(MouseEventArgs e)
    {
        if (e.Button == MouseButtons.Left)
        {
            GridHeaderCellElement cell = this.GridControl.ElementTree.GetElementAtPoint(e.Location) as GridHeaderCellElement;
            if (cell != null && cell.ColumnInfo.HeaderText == "RegNumber")
            {
                switch (this.GridControl.Columns["RegKEY"].SortOrder)
                {
                    case RadSortOrder.None:
                        this.GridControl.Columns["RegKEY"].SortOrder = RadSortOrder.Ascending;
                        break;

                    case RadSortOrder.Ascending:
                        this.GridControl.Columns["RegKEY"].SortOrder = RadSortOrder.Descending;
                        break;

                    case RadSortOrder.Descending:
                        this.GridControl.Columns["RegKEY"].SortOrder = RadSortOrder.Ascending;
                        break;
                }
                return true;
            }
        }
        return base.OnMouseUp(e);
    }
}

public class MyHeaderCell : GridHeaderCellElement
{
    public MyHeaderCell(GridViewColumn column, GridRowElement row):
        base(column, row)
    {
    }

    public override void UpdateInfo()
    {
        base.UpdateInfo();
        UpdateArrowState();
    }

    protected override SizeF ArrangeOverride(SizeF finalSize)
    {
        base.ArrangeOverride(finalSize);
        UpdateArrowState();
        return finalSize;
    }

    private void UpdateArrowState()
    {
        if (this.GridControl.Columns["RegKEY"] != null)
        {
            if (this.GridControl.Columns["RegKEY"].SortOrder == RadSortOrder.Ascending)
            {
                this.Arrow.Direction = Telerik.WinControls.ArrowDirection.Up;
                this.Arrow.Visibility = Telerik.WinControls.ElementVisibility.Visible;
                this.Padding = new Padding(0, 0, (int)this.Arrow.DesiredSize.Width * 2, 0);
            }
            else if (this.GridControl.Columns["RegKEY"].SortOrder == RadSortOrder.Descending)
            {
                this.Arrow.Direction = Telerik.WinControls.ArrowDirection.Down;
                this.Arrow.Visibility = Telerik.WinControls.ElementVisibility.Visible;
                this.Padding = new Padding(0, 0, (int)this.Arrow.DesiredSize.Width * 2, 0);
            }
            else if (this.GridControl.Columns["RegKEY"].SortOrder == RadSortOrder.None)
            {
                this.Arrow.Visibility = Telerik.WinControls.ElementVisibility.Hidden;
                this.Padding = new Padding(0);
            }
        }
    }
}

If you need further assistance, I will be glad to help.

Sincerely yours,
Jack
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Declan]

Jack,

This looks excellent. You explain what I need to do and then write the code for me. What else could I ask for. 

There are a couple of tweeks needed which I will have a go at and let you know how I get on.

Thanks,
Declan

[Jack]

Hi Declan ,

I am glad I could help. Thank you for the nice feedback. If there is something else, do not hesitate to write me back.

Sincerely yours,
Jack
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Declan]

Hi Jack,

I suspect there is a bug in the code above that I cannot find. I have setup a test table with 4 columns:

id - int PK
RegSerial - nchar(10)
RegKey - nchar(10)
Model - nchar(10)

In my code I show all columns (for testing) but the RegKey colum is the "hidden" column.

RegSerial values are 1, 2, 3, 4, 5 ... 20
RegKey values are 01, 02, 03, 04, 05, ... 20
Model values are a through to t but not in order
Under normal circumstances clicking on RegSerial (Ascending) will give me:
1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 3, 4, 5, 6, 7, 8, 9

Clicking on RegKey (Ascending) will give me:
01, 02, 03, 04, ... 19, 20

Clicking on Model (Ascending) will sort alphabetically:
a, b, c ... t

In my case what I need is to click on RegSerial and actually sort on the "hidden" column RegKey.

If I run the code and click RegSerial it will correctly sort using the RegKey column. The problem is that if I click on Model, it correctly sorts on Model but if I then click RegSerial the sort on Model remains in effect and it sub sorts Model on RegKey. The sort on Model is not cancelled.

What must I do to cancel any other sort in effect before sorting on RegKey having clicked RegSerial?


On a separate issue I note that RadGrid sorting a table of 45000 rows is approximately 10 times slower than the "out of the box" DataGridView. It is also considerably slower in loading the grid. Would you expect this to be the case?

Regards,
Declan

[Jack]

Hello Declan ,

You should clear all sort expressions by calling SortExpressions.Clear method. Here is a sample:

this.radGridView1.MasterGridViewTemplate.SortExpressions.Clear();

If this doesn't help, please open a support ticket and send me your application. I will try to locate the issue and make it working.

Yes, currently we are slower than MS DataGridView in sorting operations. We know about this issue and we plan to rework our data layer in near future.

Should you have any other questions, please write me back.

Regards,
Jack
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Declan]

Perfect thanks.

For others that may need this: I found that it was necessary to place thise line of code in the OnMouseUp event of MyGridBehaviour. It needs to go in each of the switch elements otherwise the previous sort will be cleared before it is tested in the switch to define the next sort order.

    switch (GridControl.Columns[StrColumnKeyName].SortOrder)
    {
        // It is necessary to clear previous sorts in each case
        // If done above it will clear the current sort thus preventing
        // re-ordering on key columns
        case RadSortOrder.None:
            GridControl.MasterGridViewTemplate.SortExpressions.Clear();
            GridControl.Columns[StrColumnKeyName].SortOrder = RadSortOrder.Ascending;
            break;

        case RadSortOrder.Ascending:
            GridControl.MasterGridViewTemplate.SortExpressions.Clear();
            GridControl.Columns[StrColumnKeyName].SortOrder = RadSortOrder.Descending;
            break;

        case RadSortOrder.Descending:
            GridControl.MasterGridViewTemplate.SortExpressions.Clear();
            GridControl.Columns[StrColumnKeyName].SortOrder = RadSortOrder.Ascending;
            break;
    }

[Declan]

To allow for sorting on multiple hidden columns I have updated the code provided above by Jack.

Define a Hashtable to hold the Visible/Hidden column name key value pairs.

Hashtable _KeyColumns = new Hashtable();

In Form Load add:

    _KeyColumns.Add("Vis Col 1", "Col1Key");
    _KeyColumns.Add("Vis Col 2", "Col2Key");
    _KeyColumns.Add("Vis Col 3", "Col3Key");

Configure grid as required and add code to setup sorting on hidden columns:

// Setup for sorting on a hidden column (RegKey)
sortTestRadGridView.CreateCell += new GridViewCreateCellEventHandler(sortTestRadGridView_CreateCell);
this.sortTestRadGridView.ContextMenuOpening += new ContextMenuOpeningEventHandler(SortTestRadGridViewContextMenuOpening);
this.sortTestRadGridView.GridBehavior = new MyGridBehavior(_KeyColumns);

Note _KeyColumns, the list of hidden columns, is passed in the constructor of MyGridBehaviour.

Now add the following to your form code file:

       // Code to handle sorting on hidden column.
        private void sortTestRadGridView_CreateCell(object sender, GridViewCreateCellEventArgs e)
        {
            if (e.CellType == typeof(GridHeaderCellElement) && _KeyColumns.Contains(e.Column.HeaderText))
            {
                // Pass in the cell and row and key field
                e.CellElement = new MyHeaderCell(e.Column, e.Row, _KeyColumns[e.Column.HeaderText].ToString());

            }
        }

        private void SortTestRadGridViewContextMenuOpening(object sender, ContextMenuOpeningEventArgs e)
        {
            GridHeaderCellElement cell = e.ContextMenuProvider as GridHeaderCellElement;
            if (cell != null && _KeyColumns.Contains(cell.ColumnInfo.HeaderText))
            {



                e.ContextMenu.Items.RemoveAt(2);
                RadMenuItem clearMenuItem = new RadMenuItem();
                clearMenuItem.Text = "Clear Sorting";
                clearMenuItem.Enabled = sortTestRadGridView.Columns[_KeyColumns[cell.ColumnInfo.HeaderText].ToString() ].SortOrder != RadSortOrder.None;
                e.ContextMenu.Items.Insert(2, clearMenuItem);
                clearMenuItem.Click += new EventHandler(ClearMenuItemClick);
            }
        }

        void ClearMenuItemClick(object sender, EventArgs e)
        {


            sortTestRadGridView.MasterGridViewTemplate.SortExpressions.Clear();
        }

Paste this into a separate .cs file:

    public class MyGridBehavior : BaseGridBehavior
    {
        // Value of key field header text used for sorting.
        private string SortColumnName = "";
        private string StrColumnKeyName = "";

        // Hash table passed in containing column name/key colum pairs
        private Hashtable KeyColumns;

        public MyGridBehavior(Hashtable tbl)
        {
            // Setup sort keys
            KeyColumns = tbl;
        }

        /// <summary>
        /// Overrides the default behaviour for sorting.
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        public override bool OnMouseUp(MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                GridHeaderCellElement cell = GridControl.ElementTree.GetElementAtPoint(e.Location) as GridHeaderCellElement;

                // Is this column in the list of keys for sorting on hidden column?
                if (cell != null && KeyColumns.Contains(cell.ColumnInfo.HeaderText))
                {
                    StrColumnKeyName = KeyColumns[cell.ColumnInfo.HeaderText].ToString();
                    SortColumnName = cell.ColumnInfo.HeaderText;

                    // Is this column the sort column clicked
                    if (cell.ColumnInfo.HeaderText == SortColumnName)
                    {
                        switch (GridControl.Columns[StrColumnKeyName].SortOrder)
                        {
                            // It is necessary to clear previous sorts in each case
                            // If done above it will clear the current sort thus preventing
                            // re-ordering on key columns
                            case RadSortOrder.None:
                                GridControl.MasterGridViewTemplate.SortExpressions.Clear();
                                GridControl.Columns[StrColumnKeyName].SortOrder = RadSortOrder.Ascending;
                                break;

                            case RadSortOrder.Ascending:
                                GridControl.MasterGridViewTemplate.SortExpressions.Clear();
                                GridControl.Columns[StrColumnKeyName].SortOrder = RadSortOrder.Descending;
                                break;

                            case RadSortOrder.Descending:
                                GridControl.MasterGridViewTemplate.SortExpressions.Clear();
                                GridControl.Columns[StrColumnKeyName].SortOrder = RadSortOrder.Ascending;
                                break;
                        }
                        return true;
                    }
                }
            }
            return base.OnMouseUp(e);
        }
    }

    public class MyHeaderCell : GridHeaderCellElement
    {
        // Value of key field header text used for sorting.
        private string SortColumnName = "";

        public MyHeaderCell(GridViewColumn column, GridRowElement row, string keyColumnName) : base(column, row)
        {
            // Key column name is passed in
            SortColumnName = keyColumnName;
        }

        /// <summary>
        /// Override default behaviour to handle arrow element of column header.
        /// </summary>
        public override void UpdateInfo()
        {
            base.UpdateInfo();
            UpdateArrowState();
        }

        /// <summary>
        /// Override default behaviour to handle arrow element of column header.
        /// </summary>
        /// <param name="finalSize"></param>
        /// <returns></returns>
        protected override SizeF ArrangeOverride(SizeF finalSize)
        {
            base.ArrangeOverride(finalSize);
            UpdateArrowState();
            return finalSize;
        }

        /// <summary>
        /// Handle Arrow element of column header. This overrides the default behaviour
        ///
        /// </summary>
        private void UpdateArrowState()
        {
            // This handles the Key column only
            if (GridControl.Columns[SortColumnName] != null)
            {
                if (GridControl.Columns[SortColumnName].SortOrder == RadSortOrder.Ascending)
                {
                    Arrow.Direction = Telerik.WinControls.ArrowDirection.Up;
                    Arrow.Visibility = Telerik.WinControls.ElementVisibility.Visible;
                    Padding = new Padding(0, 0, (int)Arrow.DesiredSize.Width * 2, 0);
                }
                else if (GridControl.Columns[SortColumnName].SortOrder == RadSortOrder.Descending)
                {
                    Arrow.Direction = Telerik.WinControls.ArrowDirection.Down;
                    Arrow.Visibility = Telerik.WinControls.ElementVisibility.Visible;
                    Padding = new Padding(0, 0, (int)Arrow.DesiredSize.Width * 2, 0);
                }
                else if (GridControl.Columns[SortColumnName].SortOrder == RadSortOrder.None)
                {
                    Arrow.Visibility = Telerik.WinControls.ElementVisibility.Hidden;
                    Padding = new Padding(0);
                }
            }
        }
    }

I am sure the code could be improved but this allows me have any number of hidden columns to sort on.

The only other issue is that the hidden column will still appear in the column chooser list. Toprevent this just add the following to your grid setup code:

radGridView.Columns["MyHiddenColumnName"].VisibleInColumnChooser = false;

[Jack]

Declan, this solution looks perfect. Thank you for sharing it with the community.

Regards,
Jack
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Declan]

There is a bug in the above. If the column header text is changed the arrows will not update on the visible column.

In my example the visible column is RegSerial. I prefer to show this as "Reg/Serial" or "Reg / Serial" in the column heading. If I do this in my ConfigureGrid() the arrows will not show. This took a while to track down but is an easy fix.


Original:

// Setup for sorting on a hidden column (RegKey)

this.sortTestRadGridView.CreateCell += new GridViewCreateCellEventHandler(sortTestRadGridView_CreateCell);

this.sortTestRadGridView.ContextMenuOpening += new ContextMenuOpeningEventHandler(SortTestRadGridViewContextMenuOpening);

this.sortTestRadGridView.GridBehavior = new MyGridBehavior(_KeyColumns);

sortTestRadGridView.Columns["RegSerial"].HeaderText = StrRegSerial;

All that is required is to move setting the text value up before setting up the sorting.

                sortTestRadGridView.Columns["RegSerial"].HeaderText = StrRegSerial;

               // Setup for sorting on a hidden column (RegKey)
                this.sortTestRadGridView.CreateCell += new GridViewCreateCellEventHandler(sortTestRadGridView_CreateCell);
                this.sortTestRadGridView.ContextMenuOpening += new ContextMenuOpeningEventHandler(SortTestRadGridViewContextMenuOpening);
                this.sortTestRadGridView.GridBehavior = new MyGridBehavior(_KeyColumns);

What was wrong is that the CreateCell was called with the original column name (from the table) before the column header text was changed.

Declan


PS: 

Jack,

Do you have any indication of time scale for when the data layer will be re-worked to speed up processing? As it stands, on a 45000 row table, this issue is fast becoming a show stopper for this project. As I said RadGridView is approximately 10 times slower than MS DataGridView.

[Jack]

Hello Declan ,

You could use FieldName property of GridViewDataColumn. This will solve the last issue. Just cast the ColumnInfo property this way:
 

GridViewDataColumn dataColumn = e.CellElement.ColumnInfo as GridViewDataColumn;
if (dataColumn != null && dataColumn.FieldName == "MyColumn")
{
    //...
}

I can't give exact time frame when we will rework the data layer, however I can say that this will happen after our Q2 release.

If you have other questions, don't hesitate to write us.

Kind regards,
Jack
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Declan]

If you wish to use filetering

radGridView.EnableFiltering = true;

you must set this after adding the event handlers. If done before the CreateCellEvent will not be called.

    // Setup for sorting on a hidden column
    this.radGridView.CreateCell += new GridViewCreateCellEventHandler(RadGridViewCreateCell);
    this.radGridView.ContextMenuOpening += new ContextMenuOpeningEventHandler(RadGridViewContextMenuOpening);
    this.radGridView.GridBehavior = new MyGridBehavior(KeyColumns);

    // Must be after code to add events
    radGridView.EnableFiltering = true;

Declan