Saturday, February 28, 2009

Handling multiple selects in a grid control that supports grouping and sorting

Currently in an application I am coding, I have a grid control that displays rows from a database table and allows me to group by column values in the table. For example, if the grid is displaying columns C1, C2 and C3, I can group the rows by the values of C1 by dragging that column name to the top panel of the grid control. So if C1 was the column for city, I can group all the Toronto records together.

This application must also support multiple selects. For example, when I click on 2-3 rows of the grid, I must be able to extract these rows in another class for processing. The grid control I am using, known as DevXpress, contains a method int[] GetSelectedRows(), which returns the row indices of all the rows in the grid selected by the user. So if I fill the grid with a DataSet based on a database table and select multiple rows, I can get the row indices and use those to extract the desired rows from the DataSet and use them to load another DataTable. Try this out on a regular DevXpress GridControl without the grouping and notice it returns the correct rows. The C# code is sort of like this:

DataTable dt2 = gridCtrlDataSet.m_dataTable.Clone();
Int[] selectedRows = gridCtrl.GetSelectedRows();
Foreach (int index in selectedRows)
{
Dt2.ImportRow(gridCtrlDataSet.m_dataTable.Rows[index]);
}

Note I specifically clone the DataSet’s DataTable and assign it to the target DataTable. This is required if you wish to use the DataTable.ImportRow() method.

In the above example, I specifically stated to not use any column groupings. I did this to illustrate a point. Try grouping the rows by one of the column values by dragging the column name onto the top panel of the GridControl. Select a few rows on the GridControl and use GetSelectedRows() to get their indices, and use these indices to get the desired rows from the DataSet bound to the Database table. If you look at the rows, you will notice they are not the same ones as you selected in the grouped GridControl. That’s because when the GridControl was grouped, the indices of the rows in the GridControl’s GridView changed, so they no longer corresponded with the indices in the bound DataSet.

How do we get the selected rows from the DataSet?
Before binding the DataSet to the database table, create an extra column to hold the row indices, and make it an identity column that auto-increments. Then, after calling the GetSelectedRows() method to get the indices of the selected DataGrid rows, get the index column and use those indices to get the correct rows from the DataSet. Upon doing this, you will find that you can get the rows you really selected in the GridControl. The C# code is sort of like this:

DataTable dt2 = gridCtrlDataSet.m_dataTable.Clone();
Int[] selectedRows = gridCtrl.GetSelectedRows();
Foreach (int i in selectedRows)
{
Int index = gridCtrView.DataRowView[i].Row[“ID”];
Dt2.ImportRow(gridCtrlDataSet.m_dataTable.Rows[index]);
}


What is the moral of the story?
Be sure to understand the behaviour of your GridControls before assuming what their methods for return selected rows actually do return.