Change the column order when reading from a dataset into a gridcontrol?

[Markus Persson]

I have a DataSet that contains two tables with a relation between them.
I'm using Syncfusion 6.4.0.14 and VS 2005.
I'm using this code to populate my GridControl from a dataset.

Me.GridControl1.BeginUpdate()
Dim rows As Integer = ds.Tables(0).Rows.Count
Me.GridControl1.RowCount = rows
Me.GridControl1.PopulateValues(GridRangeInfo.Cells(1, 1, rows, Me.GridControl1.ColCount), ds.TABLE_NAME)
Me.GridControl1.EndUpdate()
Me.GridControl1.Refresh()

The GridControl gets populated nicely, but I have a different column order (in fact, the user might change the column order and my program remembers it and load it up as the user had it before. So one user can have "FirstName", "LastName", "City". While another one can have "FirstName", "City", "LastName").
So, is there any way to change the column order in my dataset to match the columns in the gridcontrol?
The current column order in my dataset seems to be in the way I have created the tables/columns in my dataset.
Or do someone have another solution for my problem?

[Markus Persson]

"The current column order in my dataset seems to be in the way I have created the tables/columns in my dataset."

Forget that, it seems to be how the table/columns has been created in the database itself.

[Jisha Joy]

Hi Markus,

Thank you for posting query to us.


You can change the order of columns by moving them. To move columns in the gridcontrol, you can use the grid.Cols.MoveRange method.
See the code below,

// moves column 1 to 3
this.gridControl1.Cols.MoveRange(1,3);
 
//moves column 1 & 2 to 4
this.gridControl1.Cols.MoveRange(1,2,4); 

Please try this and let me know if this helps.

Regards,
Jisha

[Markus Persson]

Thanks Jisha.

However I noticed that my grid didn't get populated as I thought so I'm back to square 1.

This is what I have done:
I have created one DataSet in VS with two tables that looks like this:

Customers
---------
CustomerID   <-- Primary key
CompanyName


Orders
------
OrderID      <-- Primary key
CustomerID   <-- Foreign key
OrderDate

In this DataSet I have also created a relation between CustomerID in Customers (as parent) and CustomerID in Orders (as child).

I then have created two DataAdapters, one with the commandtext "SELECT * FROM Customers" that I fill the Customers table with in the DataSet. And the other DataAdapter have the commandtext "SELECT * FROM Orders" that I fill the Orders table with in the DataSet.
This works nicely, I have two tables in my DataSet filled with data.

But then I try this:

Me.gc.BeginUpdate()
Dim rows As Integer = Me.ds.Customers.Rows.Count
Me.gc.RowCount = rows
Me.gc.PopulateValues(GridRangeInfo.Cells(1, 1, rows, 5), Me.ds.Customers)
Me.gc.EndUpdate()
Me.gc.Refresh()

But the GridControl only gets filled with data from Customers. I want it to get filled with data from Customers and it's relation with Orders.

So, how do I populate my GridControl with a DataSet that has a relation?

[Jisha Joy]

Hi Markus,


GridControl is not a bounded control.I recommend you to use GridGroupingControl to display nested tables in a hiearchical display. Please refer the following browser samples under this category:
..\\MyDocuments\Syncfusion\EssentialStudio\6.3.0.30Windows\Grid.Grouping.Windows\Samples\2.0\RelationsAndHierarchy

Regards,
Jisha

[Markus Persson]

Hi Jisha.

I don't believe GridGroupingControl has the things I want that GridControl has.

For example:
- Be able to change ActivateCurrentCellBehavior to DblClickOnCell
- GridToExcel
- Copy one cell, then select multiple cells and do a paste
- Change the backcolor of a cell when the user edits it

[Jisha Joy]

Hi Markus,

This can be achieved by GridGroupingcontrol also.

Be able to change ActivateCurrentCellBehavior to DblClickOnCell

Please refer the code:

this.gridGroupingControl1.TableControl.Model.Options.ActivateCurrentCellBehavior = Syncfusion.Windows.Forms.Grid.GridCellActivateAction.DblClickOnCell;

GridToExcel

Please refer the following browser sample:

..\\MyDocuments\Syncfusion\EssentialStudio\6.3.0.30\Windows\Grid.Grouping.Windows\Samples\2.0\Serialization\Excel Export\cs

Copy one cell, then select multiple cells and do a paste

This can be achieved by TableModel.ClipboardPaste event.


this.gridGroupingControl2.TableModel.ClipboardPaste += new GridCutPasteEventHandler(TableModel_ClipboardPaste);
this.gridGroupingControl2.TableOptions.AllowSelection = GridSelectionFlags.Any;


void TableModel_ClipboardPaste(object sender, GridCutPasteEventArgs e)
{
IDataObject data = Clipboard.GetDataObject();
if (data.GetDataPresent(typeof(string)))
{
string s = (string)data.GetData(typeof(string));
GridRangeInfoList rangeList;
if (this.gridGroupingControl2.TableModel.Selections.GetSelectedRanges(out rangeList, true))
{
this.gridGroupingControl2.TableModel.ChangeCells(rangeList.ActiveRange, s);
e.Result = true;
e.Handled = true;
}
}
}


Change color while editing

This can be achieved by handling the QueryCellStyleInfo event.

this.gridGroupingControl2.QueryCellStyleInfo += new GridTableCellStyleInfoEventHandler(gridGroupingControl2_QueryCellStyleInfo);


void gridGroupingControl2_QueryCellStyleInfo(object sender, GridTableCellStyleInfoEventArgs e)
{ GridCurrentCell currentCell = this.gridGroupingControl2.TableControl.CurrentCell;
if (e.TableCellIdentity.ColIndex == currentCell.ColIndex && e.TableCellIdentity.RowIndex == currentCell.RowIndex )//&& flag==1)
{
e.Style.TextColor = Color.Fuchsia;
}
}

Please let me know if this helps.

Regards,
Jisha

[Markus Persson]

Thanks Jisha, however I feel that I wasn't clear enough with "- Change the backcolor of a cell when the user edits it". Sorry about that.

What I want is: when a user have made an edit to a cell, that cell with that edit will have another backcolor. How do I achieve that?

Also, it seems like the grid gets auto sorted directly after I edit a cell. How do I disable that? I want to control when to sort my grid myself.

[Markus Persson]

Another thing, how do I change the text for the column header?

[Jisha Joy]

Hi Markus,

Change color on Editing



This can be achieved by handling TableControlPrepareViewStyleInfo and TableControlCellClick events.

Please see the code:



this.gridGroupingControl1.TableControlPrepareViewStyleInfo += new GridTableControlPrepareViewStyleInfoEventHandler(gridGroupingControl1_TableControlPrepareViewStyleInfo);

this.gridGroupingControl1.TableControlCellClick +=new GridTableControlCellClickEventHandler(gridGroupingControl1_TableControlCellClick);


//
ArrayList doubleClickedCells = new ArrayList();

private int GetLookUpCode(int row, int col)
{ return 10000 * col + row; }
void gridGroupingControl1_TableControlPrepareViewStyleInfo(object sender, GridTableControlPrepareViewStyleInfoEventArgs e)
{
if (doubleClickedCells.IndexOf(GetLookUpCode(e.Inner.RowIndex, e.Inner.ColIndex)) > -1)
{
e.Inner.Style.BackColor = Color.Blue;
}
}


void gridGroupingControl1_TableControlCellClick(object sender, Syncfusion.Windows.Forms.Grid.Grouping.GridTableControlCellClickEventArgs e)
{
int row, col;
Point pt = this.gridGroupingControl1.PointToClient(Cursor.Position);
if (this.gridGroupingControl1.TableControl.PointToRowCol(pt, out row, out col))
{
int code = GetLookUpCode(row, col);
if (doubleClickedCells.IndexOf(code) == -1)
doubleClickedCells.Add(code);
else
doubleClickedCells.Remove(code);
this.gridGroupingControl1.TableControl.RefreshRange(GridRangeInfo.Cell(row, col), true);
}
}




Prevent dynamic sorting


You need to implement a ListWrapper class to preserve the sorted rows and avoid the grid from further sorting. Having done this, you can wrap the datasource of the grid to a ListWrapper object and wire the grid to it. Please refer the below sample for more details.

http://websamples.syncfusion.com/samples/Grid.Windows/Grid_WF_GGCPreventDynamicSort/main.htm

Change HeaderText

This can be achieved by setting the HeaderText property.


this.gridGroupingControl1.TableDescriptor.Columns[2].HeaderText = "Mytext";



Regards,
Jisha