Binding to table with recursive join

Hi there,

I searched the forums and knowledge base but did not see anything on binding to a table in VB.Net that has a recursive join. The join creates a hierarchy structure and it would be very nice if the grid control would support this.

Thanks,

Wes

10 Replies

RA Ramu Syncfusion Team August 18, 2006 01:15 AM UTC

Hi Wes,

We are looking into it and will update you as soon as possible.

Thanks,
Ramu.K


AD Administrator Syncfusion Team August 18, 2006 03:48 PM UTC

Hi Ramu,

Any luck yet? I can get it to load the data, but it''s all messed up and not in the format I was expecting. It''s not nested at all.

Thanks,

Wes

>Hi Wes,

We are looking into it and will update you as soon as possible.

Thanks,
Ramu.K


RA Ramu Syncfusion Team August 18, 2006 05:32 PM UTC

Hi Wes,

This is possible in our grid. Here is the sample that shows the Grid in a hierarchical manner.

Our grid will not automatically display in hierarchy structure by using the join query. To display grid in hierarchical form you have to define the “GridRelation” for parent and child table.

Steps to define hierarchical grid:

1) In the properties window choose “GridRelation” under Table Descriptor.

2) “GridRelationsDescriptor Collection Editor” opens up.

3) Add a new member and then choose the childTableName to the member.

4) Choose “RelatedMasterDetails” value for Relationkind property.

5) Choose the “RelationKeys” property. “RelationKeyDescriptor Collection Editor” opens up.

6) Add a new member and choose “ChildKeyFieldName” and “ParentKeyFieldName” to the member.

7) click “ok” button. Displays the grid in hierarchical form.

In this sample I have recursively joined with “OrdereID” column.I have used Orders table from NWIND Database. Please have a look at the sample and let me know if you have any queries.


recursive_Jion_in_vb


Thanks,
Ramu.K


AD Administrator Syncfusion Team August 18, 2006 06:22 PM UTC

Hi Ramu,

After reading your reply I realized that I had not correctly described what I want to do. I apologize for wasting your time with the previous request.

I have a table with two columns (there''s more, but this is all we''re concerned with right now): CompanyBusinessUnitID (CBUID) and ParentCompanyBusinessUnitID (PCBUID). If the PCBUID is null then the row is a top level row in the hierarchy. If it is not null than the current row has a parent. This is how the hierarchy is built.

This table represents a company employee hierarchy.

Is it possible to represent this with your grid? I think it is, but I don''t understand your grid well enough yet to get it to work.

Thanks for any help you can give me and again I apologize for misleading you previously.

Thanks,

Wes

>Hi Wes,

This is possible in our grid. Here is the sample that shows the Grid in a hierarchical manner.

Our grid will not automatically display in hierarchy structure by using the join query. To display grid in hierarchical form you have to define the “GridRelation” for parent and child table.

Steps to define hierarchical grid:

1) In the properties window choose “GridRelation” under Table Descriptor.

2) “GridRelationsDescriptor Collection Editor” opens up.

3) Add a new member and then choose the childTableName to the member.

4) Choose “RelatedMasterDetails” value for Relationkind property.

5) Choose the “RelationKeys” property. “RelationKeyDescriptor Collection Editor” opens up.

6) Add a new member and choose “ChildKeyFieldName” and “ParentKeyFieldName” to the member.

7) click “ok” button. Displays the grid in hierarchical form.

In this sample I have recursively joined with “OrdereID” column.I have used Orders table from NWIND Database. Please have a look at the sample and let me know if you have any queries.


recursive_Jion_in_vb


Thanks,
Ramu.K


RA Ramu Syncfusion Team August 18, 2006 07:48 PM UTC

Hi Wes,

Could you please tell me UI design and the structure of the Data Table? So that it will be helpful for us to solve the issue as soon as possible. (if possible can you please provide a screen shot on the final UI that would be great). So we can try creating a sample for your requirements.

Thanks,
Ramu.K


AD Administrator Syncfusion Team August 18, 2006 08:09 PM UTC

Hi Ramu,

I have attached a couple images of the table in question. One image is design time and the other is runtime.

The UI design needs to be a "tree" look and feel but with editing capabilities. That''s why we''re looking at a grid instead of a tree. I have reviewed many, many of the tree products out there and they either, don''t look good, don''t do what they say they will, lack something we need or are way too expensive.

Thanks,

Wes

CompanyBU.zip


RA Ramu Syncfusion Team August 18, 2006 11:54 PM UTC

Hi Wes,

We have started creating the sample. We assume this behavior can be achieved by the following.

Trying to group by “ParentCompanyBusinessID”. If this field contains NULL value we will be removing the “GroupCptionCell” and the +/- cell and display only it’s rows or we will leave the records as grouped ( that is with the +/- cell). Please let me know if we are getting deviated from your requirements. If it is so could you give me the final UI of the grid to be displayed?

Sorry for the inconvenience caused.

Thanks,
Ramu.K



AD Administrator Syncfusion Team August 21, 2006 02:18 PM UTC

Hi Ramu,

I''ve included a screen shot of what we are displaying now using the MS Treeview control. Using your grid control the layout of the data should still be the same.

As you can see, Bob''s Jobs is the top level node so it doesn''t have a ParentCompanyBusinessUnitID. Everything else should have a parent ID as they are all children of Bob''s Jobs or a child of a child.

The text in italics represent users which I add after creating the tree. You don''t need to worry about them as I can add them afterwards or just include them in with the query that populates the tree/grid.

Hopefully the screenshot gives you the information you were looking for.

Thanks,

Wes

CompanyBUGUI.zip


SI Sivakumar Syncfusion Team August 21, 2006 07:13 PM UTC

Hi Wes,

Thanks for your screenshot. We are looking for the possiblities of implementing this. We will update you as soon as possible.

Sorry for the inconvenience caused and thanks for your patience.

Thanks,
A.Sivakumar


SI Sivakumar Syncfusion Team August 22, 2006 09:32 AM UTC

Hi Wes,

I herewith attached the sample as per your requirements. I used Datatable as datasource for the grid. Let me know if you want a sample which shows the same for dataset.

click to download Self join in grid Sample


Configuring the Grid:
---------------------

Grid is connected to the datatable which has a ''CBUID'' column and ''PCBUID'' column and
once we set the datasource we set the hierarchy relations for the grid.

We set the relation between ''CBUID'' and ''PCBUID'' columns where if the ''PCBUID'' is null then the row is a top level row in the hierarchy. If it is not null than the current row has a parent. This is how the hierarchy is built.

It is important to set the primary key column for each table descriptor else, the grid cannot be in editable mode.


''QueryCellStyleInfo'' Event is handled so that when the NestedTables record count is null we will hide the +/- column and make it as a static cell.

''RecordFilterDescriptor'' is used to restrict the grids auto population code to detect when a self-relation occurs. In such case the grid will not add any further levels.


The look and feel of the grid is pretty basic in this sample, but can be customized in any way you want.

Please let me know if you need furthur assistance.


Thanks,
A.Sivakumar

Loader.
Up arrow icon