Programmatically Creating A RadGridView Hierarchy

[Martin Hamilton]

Hello Forum Users

This is still a work in progress, but I thought that I'd share my routines with those of you that are interested and while there is room for optimization and correction - it does work.

Feel free to build upon this code for your own purposes - and share your improvements if you see fit to share back.

Preface:  I have a DAL (Data Access Layer) that returns datasets, datatables and so forth - so you are going to see a reference to oSQL --- which is my DAL.  You'll need to replace that with your own DAL or GetDataSet routines.

Scenario:  I have to hit three separate databases to build the grid and subgrids.  Not my choice.  So I use my DAL which is capable of hitting multiple databases rather than the Visual Studio method that creates bound datasets.  For example, if I did that I'd have so many bindingsource, dataset, etc objects on my screen I'd go crazy trying to manage them all... so the DAL is easier and less intrusive from that aspect.

As I hit the three SQL Databases I then needed to create a grid, sub-grid and another grid under one of the sub-grids.

This code shows how I did this - unbound.

I created a module file called: modRadGridViewRoutines which contains subroutines and also a class.  Shown below:

Imports Telerik.WinControls.UI

Module modRadGridViewRoutines
#Region " RadGridView Routines "
Friend Sub CreateMasterTemplate(ByVal oRadGridView As RadGridView, ByVal oMasterTemplateBindingSource As BindingSource, ByVal sMasterTemplateCaption As String, Optional ByVal bEnableGrouping As Boolean = True)
oRadGridView.EnableGrouping = bEnableGrouping
oRadGridView.DataSource = oMasterTemplateBindingSource
oRadGridView.GroupExpandAnimationType = GridExpandAnimationType.Slide
oRadGridView.EnableAlternatingRowColor = True
SetTemplateDefaults(oRadGridView.MasterTemplate)
oRadGridView.MasterTemplate.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill
oRadGridView.MasterTemplate.Caption = sMasterTemplateCaption
oRadGridView.MasterTemplate.ShowChildViewCaptions = True
End Sub

Friend Sub AddChildTemplateToMasterTemplate(ByVal oRadGridView As RadGridView, ByVal oChildTemplate As GridViewTemplate, ByVal oChildTemplateBindingSource As BindingSource, ByVal sChildTemplateCaption As String)
oChildTemplate.DataSource = oChildTemplateBindingSource
oChildTemplate.Caption = sChildTemplateCaption
oRadGridView.MasterTemplate.Templates.Add(oChildTemplate)
End Sub

Friend Sub CreateRadGridViewMasterTemplateRelations(ByVal oRadGridView As RadGridView, ByVal oChildTemplate As GridViewTemplate, ByVal sRelationName As String, ByVal sParentColumnNames As String, ByVal sChildColumnNames As String)
Dim oRelation As New GridViewRelation(oRadGridView.MasterTemplate)
oRelation.ChildTemplate = oChildTemplate
oRelation.RelationName = sRelationName
oRelation.ParentColumnNames.Add(sParentColumnNames)
oRelation.ChildColumnNames.Add(sChildColumnNames)
oRadGridView.Relations.Add(oRelation)
End Sub

Friend Sub AddChildTemplateToChildTemplate(ByVal oChildTemplate As GridViewTemplate, ByVal oChildSubTemplate As GridViewTemplate, ByVal oChildSubTemplateBindingSource As BindingSource, ByVal sChildSubTemplateCaption As String)
oChildSubTemplate.DataSource = oChildSubTemplateBindingSource
oChildSubTemplate.Caption = sChildSubTemplateCaption
oChildTemplate.Templates.Add(oChildSubTemplate)
End Sub

Friend Sub CreateRadGridViewChildTemplateRelations(ByVal oRadGridView As RadGridView, ByVal oChildTemplate As GridViewTemplate, ByVal oChildSubTemplate As GridViewTemplate, ByVal oRadGridViewRelationsClass As List(Of RadGridViewRelationsClass))
Dim oRelation As New GridViewRelation(oChildTemplate)
oRelation.ChildTemplate = oChildSubTemplate
For i = 0 To oRadGridViewRelationsClass.Count - 1
oRelation.RelationName = oRadGridViewRelationsClass.Item(i).RelationName
oRelation.ParentColumnNames.Add(oRadGridViewRelationsClass.Item(i).ParentFieldName)
oRelation.ChildColumnNames.Add(oRadGridViewRelationsClass.Item(i).ChildFieldName)
Next
oRadGridView.Relations.Add(oRelation)
End Sub

Friend Sub SetTemplateDefaults(ByVal oGridViewTemplate As GridViewTemplate)
oGridViewTemplate.ReadOnly = True
oGridViewTemplate.AllowRowResize = True
oGridViewTemplate.EnableAlternatingRowColor = True
oGridViewTemplate.AllowAddNewRow = False
oGridViewTemplate.AllowDeleteRow = False
oGridViewTemplate.AllowEditRow = False
End Sub
#End Region
End Module

Public Class RadGridViewRelationsClass
Private _RelationName As String
Private _ParentFieldName As String
Private _ChildFieldName As String

Public Sub New(ByVal sRelationName As String, ByVal sParentFieldName As String, ByVal sChildFieldName As String)
_RelationName = sRelationName
_ParentFieldName = sParentFieldName
_ChildFieldName = sChildFieldName
End Sub

Public Property RelationName As String
Get
Return _RelationName
End Get
Set(value As String)
_RelationName = value
End Set
End Property

Public Property ParentFieldName As String
Get
Return _ParentFieldName
End Get
Set(value As String)
_ParentFieldName = value
End Set
End Property

Public Property ChildFieldName As String
Get
Return _ChildFieldName
End Get
Set(value As String)
_ChildFieldName = value
End Set
End Property
End Class

Here is the example of how the class is used:

Private Sub GetOrders
SQL = "Select * From View_Orders ORDER BY TxnDate ASC"
Dim dtOrders As DataTable = oSQL.GetDataTable(SQL) : dtOrders.TableName = "Orders"

SQL = "Select * From View_OrderItems ORDER BY OrderID, TxnDate ASC"
Dim dtOrderItems As New DataTable : dtOrderItems = oSQL.GetDataTable(SQL) : dtOrderItems.TableName = "OrderItems"

SQL = "Select id, CustomerRefListID, ShipAddressFormatted From Orders"
Dim dtCustomerAddress As New DataTable : dtCustomerAddress = oSQL.GetDataTable(SQL) : dtCustomerAddress.TableName = "CustomerAddress"

SQL = "Select * From View_OrderItemComponentsToProductComponents"
Dim dtProductComponents As New DataTable : dtProductComponents = oSQL.GetDataTable(SQL) : dtProductComponents.TableName = "ProductComponents"

'programmatically create a new dataset only adding those products having sub components.
'Add the dataset to the orderitems template and connect it via the product code...

Dim dsDataCombined = New DataSet
dsDataCombined.Tables.Add(dtOrders.Copy)
dsDataCombined.Tables.Add(dtOrderItems.Copy)
dsDataCombined.Tables.Add(dtCustomerAddress.Copy)
dsDataCombined.Tables.Add(dtProductComponents.Copy)
rgvOrders.DataSource = dsDataCombined

dtOrders = Nothing
dtOrderItems = Nothing
dtCustomerAddress = Nothing
dtProductComponents = Nothing

Dim OrdersBindingSource As New BindingSource
OrdersBindingSource.DataSource = dsDataCombined : OrdersBindingSource.DataMember = dsDataCombined.Tables(0).TableName

Dim OrderItemsBindingSource As New BindingSource
OrderItemsBindingSource.DataSource = dsDataCombined : OrderItemsBindingSource.DataMember = dsDataCombined.Tables(1).TableName

Dim CustomerAddressBindingSource As New BindingSource
CustomerAddressBindingSource.DataSource = dsDataCombined : CustomerAddressBindingSource.DataMember = dsDataCombined.Tables(2).TableName

Dim ProductComponentsBindingSource As New BindingSource
ProductComponentsBindingSource.DataSource = dsDataCombined : ProductComponentsBindingSource.DataMember = dsDataCombined.Tables(3).TableName

CreateMasterTemplate(rgvOrders, OrdersBindingSource, "Customer Order")

Dim OrderItemsTemplate As New GridViewTemplate()
AddChildTemplateToMasterTemplate(rgvOrders, OrderItemsTemplate, OrderItemsBindingSource, "Order Items/Details")
CreateRadGridViewMasterTemplateRelations(rgvOrders, OrderItemsTemplate, "OrderToOrderItems", "id", "OrderID")
SetTemplateDefaults(OrderItemsTemplate)

Dim CustomerAddressTemplate As New GridViewTemplate()
AddChildTemplateToMasterTemplate(rgvOrders, CustomerAddressTemplate, CustomerAddressBindingSource, "Customer Address Information")
CreateRadGridViewMasterTemplateRelations(rgvOrders, CustomerAddressTemplate, "CustomerAddress", "id", "id")
SetTemplateDefaults(CustomerAddressTemplate)

Dim ProductComponentsTemplate As New GridViewTemplate()
AddChildTemplateToChildTemplate(OrderItemsTemplate, ProductComponentsTemplate, ProductComponentsBindingSource, "Product Unbuild")

ProductNumberToParentProductNumberRelations = New List(Of RadGridViewRelationsClass)()
ProductNumberToParentProductNumberRelations.Add(New RadGridViewRelationsClass("ComponentsToOrderItems", "ProductNumber", "ParentProductNumber"))
ProductNumberToParentProductNumberRelations.Add(New RadGridViewRelationsClass("ComponentsToOrderItemsOrderID", "OrderID", "OrderItemID"))

CreateRadGridViewChildTemplateRelations(rgvOrders, OrderItemsTemplate, ProductComponentsTemplate, ProductNumberToParentProductNumberRelations)
SetTemplateDefaults(ProductComponentsTemplate)
End Sub


This portion of the code:

#Region " List(of) Variables "
Private ProductNumberToParentProductNumberRelations As List(Of RadGridViewRelationsClass)
#End Region

SQL = "Select * From View_Orders ORDER BY TxnDate ASC"
Dim dtOrders As DataTable = oSQL.GetDataTable(SQL) : dtOrders.TableName = "Orders"

SQL = "Select * From View_OrderItems ORDER BY OrderID, TxnDate ASC"
Dim dtOrderItems As New DataTable : dtOrderItems = oSQL.GetDataTable(SQL) : dtOrderItems.TableName = "OrderItems"

SQL = "Select id, CustomerRefListID, ShipAddressFormatted From Orders"
Dim dtCustomerAddress As New DataTable : dtCustomerAddress = oSQL.GetDataTable(SQL) : dtCustomerAddress.TableName = "CustomerAddress"

SQL = "Select * From View_OrderItemComponentsToProductComponents"
Dim dtProductComponents As New DataTable : dtProductComponents = oSQL.GetDataTable(SQL) : dtProductComponents.TableName = "ProductComponents"

'programmatically create a new dataset only adding those products having sub components.
'Add the dataset to the orderitems template and connect it via the product code...

Dim dsDataCombined = New DataSet
dsDataCombined.Tables.Add(dtOrders.Copy)
dsDataCombined.Tables.Add(dtOrderItems.Copy)
dsDataCombined.Tables.Add(dtCustomerAddress.Copy)
dsDataCombined.Tables.Add(dtProductComponents.Copy)
rgvOrders.DataSource = dsDataCombined

dtOrders = Nothing
dtOrderItems = Nothing
dtCustomerAddress = Nothing
dtProductComponents = Nothing

Dim OrdersBindingSource As New BindingSource
OrdersBindingSource.DataSource = dsDataCombined : OrdersBindingSource.DataMember = dsDataCombined.Tables(0).TableName

Dim OrderItemsBindingSource As New BindingSource
OrderItemsBindingSource.DataSource = dsDataCombined : OrderItemsBindingSource.DataMember = dsDataCombined.Tables(1).TableName

Dim CustomerAddressBindingSource As New BindingSource
CustomerAddressBindingSource.DataSource = dsDataCombined : CustomerAddressBindingSource.DataMember = dsDataCombined.Tables(2).TableName

Dim ProductComponentsBindingSource As New BindingSource
ProductComponentsBindingSource.DataSource = dsDataCombined : ProductComponentsBindingSource.DataMember = dsDataCombined.Tables(3).TableName

CreateMasterTemplate(rgvOrders, OrdersBindingSource, "Customer Order")

Dim OrderItemsTemplate As New GridViewTemplate()
AddChildTemplateToMasterTemplate(rgvOrders, OrderItemsTemplate, OrderItemsBindingSource, "Order Items/Details")
CreateRadGridViewMasterTemplateRelations(rgvOrders, OrderItemsTemplate, "OrderToOrderItems", "id", "OrderID")
SetTemplateDefaults(OrderItemsTemplate)

Dim CustomerAddressTemplate As New GridViewTemplate()
AddChildTemplateToMasterTemplate(rgvOrders, CustomerAddressTemplate, CustomerAddressBindingSource, "Customer Address Information")
CreateRadGridViewMasterTemplateRelations(rgvOrders, CustomerAddressTemplate, "CustomerAddress", "id", "id")
SetTemplateDefaults(CustomerAddressTemplate)

Dim ProductComponentsTemplate As New GridViewTemplate()
AddChildTemplateToChildTemplate(OrderItemsTemplate, ProductComponentsTemplate, ProductComponentsBindingSource, "Product Unbuild")

ProductNumberToParentProductNumberRelations = New List(Of RadGridViewRelationsClass)()
ProductNumberToParentProductNumberRelations.Add(New RadGridViewRelationsClass("ComponentsToOrderItems", "ProductNumber", "ParentProductNumber"))
ProductNumberToParentProductNumberRelations.Add(New RadGridViewRelationsClass("ComponentsToOrderItemsOrderID", "OrderID", "OrderItemID"))

CreateRadGridViewChildTemplateRelations(rgvOrders, OrderItemsTemplate, ProductComponentsTemplate, ProductNumberToParentProductNumberRelations)
       
Uses the class found within modRadGridViewRoutines to create a multiple filter for the final (3rd Tier) grid.

Hopefully you'll find this usefull as a base for your own routines and I'll say once again that it's a work in progress even for me.

Please share your optimizations and upgrades to this code if you find it helpful so that all of us can benefit from it. :)

[George]

Hello Martin,

Thank you for sharing your experience with us, we appreciate your efforts.

Regards,
George
Telerik