WPF Radgridview heirarchichical Binding from Dataset

[Renju Panicker]

Cheers to Telerik,

I've just started using the telerik WPF and started with radgridview only.

Am using a Dataset to populate the Grid.This is not a typed dataset, but a dataset generated programmatically from a SQLServer 2005 DB.Am using VB.net 3.5 as well.To populate the Dataset am using a StoredProcedure

I was able to populate the grid using my dataset.Now I want to make a child for that grid with another dataset and the dataset uses another SP.So how can I attain this.I tried for help but majority is using LINQ.Am not much aware of LINQ.

Please find my code below.( I havent tried with the hierarchy here)

XAML

<Window x:Class="Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="Window1" Height="515" Width="933" xmlns:telerik="http://schemas.telerik.com/2008/xaml/presentation">
    <Grid>

        <telerik:RadGridView Margin="0,-12,22,163" Name="RGVW" AutoGenerateColumns="False"  ItemsSource="{Binding}" AlternateRowBackground="Bisque">
            <telerik:RadGridView.Columns>
                <telerik:GridViewDataColumn  Name="CDNO" UniqueName="CDNO" Header="CDNO"  />
                <telerik:GridViewDataColumn Name="Truck" UniqueName="Truck No" Header="Truck No"  />
                <telerik:GridViewDataColumn Name="Status" UniqueName="Status" Header="Status"  />
                <telerik:GridViewImageColumn Name="Flag" UniqueName="Flag" Header="Truck Flag"  />
            </telerik:RadGridView.Columns>
        </telerik:RadGridView>
    </Grid>
</Window>

VBCode (VS2008)

Private Sub Window1_Loaded(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Me.Loaded
        Dim ds As DataSet
        Dim dbConn As String = "Data Source=SQLSERVER;Initial Catalog=DB;_
Persist Security Info=True;User ID=!@#;pwd=!@#"
        ds = DBA.ExecuteDataset(dbConn, "SP_DockForWPF")
        RGVW.DataContext = ds
    End Sub

Here the uniquenames am using is the field from the SP.
To connect to the DB am using, the Microsoft.StandardLib.DBAccess dll. (DBA is the instance of this DLL

I got a sample in C# from the following post http://www.telerik.com/ClientsFiles/164971_hierarchywithdataset.zip .Its the one which uses LINQ

Many thanks in advance

Renju

[Vlad]

Hello Renju,

You can define you own custom HIerarchyChildTemplate and set desired ItemsSource to the child grid. Please check this demo for more info:
http://demos.telerik.com/wpf/?GridView/Hierarchy/HierarchyChildTemplate

Greetings,
Vlad
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Renju Panicker]

Vlad,

Will check that and come back to you.Currently assigned with another stuff ..so will take some more time to check.

Thanks a lot for the reply..

Renju

[Renju Panicker]

Vlad,

I just tested it and am sorry, its not working as per the desired manner..Am not even able to see the plus sign in the grid :(

Need more samples.

Can you please explain one with using stored procedures.

My scenario goes like this

In the parent grid I select a row and has to pass the ID from that row to my new SP which populates the dataset and which inturn will populates the grid.Hope you got my point.

Regards

Renju

[Vlad]

Hello Renju,

You can insert empty GridViewTableDefinition in ChildTableDefinitions or set desired row IsExpandable to true - please refer again to our examples. Furthermore you can use Loaded event of the child table to request your child data - you can get your parent item if you cast sender object of the event to GridViewDataControl and get DataContext. In case you want the same in MVVM style you can check this blog post which illustrates how to bind hierarchy using custom model - the example is for Silverlight however the same approach can be used with WPF.

Best wishes,
Vlad
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Renju Panicker]

Vlad..

Tried everything with the information you have provided, but unfortunately, nothing is coming in my way.:(

Renju

[Vlad]

Hello,

Can you post more info about the exact problem? Do you have any errors?

Greetings,
Vlad
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Renju Panicker]

Hi Vlad,

Greetings..

Sorry for the delay..

Here is the my example scenario..

I have an Order Table  which has Order_ID (PK) and Order_Date in it.
the second table is Order Details table with Order_Detail_ID as PK, Order_ID as FK, Item_Name,Item_Qty fields in it.
I have two SQLStoredProcedures

1) to Select all the Orders
2) Select all the OrderDetails based on the Order ID.So am passing a parameter value here

So Initially am populating the dataset  with SP1 and that ds is binded  with the gridview.

Then when i select the plus sign I have to get the orders for that Order ID.

This is what am trying to achieve

Here everything is using code only.Am not using the typed dataset.

hope you are clear now.

Since our aim is to migrate an old project into WPF with more enhanced features like heirarchical grid, we are using SP's .Otherwise we cant use the current stored procedures, if we use the typed dataset.

Many Thanks in advance
Renju

[Vlad]

Hello Renju,

Can you post your code (XAML and VB.NET) to see how some of my suggestions are applied?

Sincerely yours,
Vlad
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Renju Panicker]

Vlad..

Again sorry for the delay.Held up in a meeting and u know how the meetings will go up ;)

So here is my Code. To start with, the XAML first

Window1.XAML

<Window x:Class="Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:telerikData="clr-namespace:Telerik.Windows.Data;assembly=Telerik.Windows.Data"
    xmlns:telerik="http://schemas.telerik.com/2008/xaml/presentation"
    xmlns:data="clr-namespace:Telerik.Windows.Data;assembly=Telerik.Windows.Data"
    Title="Window1" Height="515" Width="933" >

    <Grid>
        <telerik:RadGridView x:Name="RadGridView1" CanUserFreezeColumns="False" ItemsSource="{Binding}" RowLoaded="RadGridView1_RowLoaded" DataLoading="RadGridView1_DataLoading"  IsReadOnly="True" AutoGenerateColumns="False" ShowGroupPanel="False" Margin="10,0,0,0">
            <telerik:RadGridView.ChildTableDefinitions>
                <telerik:GridViewTableDefinition>
                    <telerik:GridViewTableDefinition.Relation>
                        <telerikData:PropertyRelation ParentPropertyName="Order_Details"/>
                    </telerik:GridViewTableDefinition.Relation>
                </telerik:GridViewTableDefinition>
            </telerik:RadGridView.ChildTableDefinitions>
            <telerik:RadGridView.Columns>
                <telerik:GridViewDataColumn UniqueName="Order_ID" Header="Order ID" />
                <telerik:GridViewDataColumn UniqueName="Order_Date" Header="Order Date" />
            </telerik:RadGridView.Columns>
        </telerik:RadGridView>
    </Grid>
</Window>

Ok Now the VB.net

Window1.XAML.vb

Imports Microsoft.SqlServer
Imports System.Data.SqlClient
Imports System.Data
Imports Microsoft.StandardLib.DBAccess
Imports Telerik.Windows.Data
Imports Telerik.Windows.Controls
Imports Telerik.Windows.Controls.GridView

Partial Public Class Window1
    Public ds2 As DataSet
    Private Sub Window1_Loaded(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Me.Loaded
        Dim ds As DataSet
        ds = DBA.ExecuteDataset(dbConn, "SP_SelectOrders")
        RadGridView1.ItemsSource = ds
        Dim definition As New GridViewTableDefinition()
        definition.Relation = New PropertyRelation("Order_Details")
        RadGridView1.ChildTableDefinitions.Add(definition)
    End Sub
    Private Sub RadGridView1_RowLoaded(ByVal sender As Object, ByVal e As Telerik.Windows.Controls.GridView.RowLoadedEventArgs)
        Dim row As GridViewRow = TryCast(e.Row, GridViewRow)
        If row IsNot Nothing Then
            'Dim ds2 As DataSet
            Dim objParams As Object() = {DirectCast(DirectCast(row.DataContext(), System.Object), System.Data.DataRow).ItemArray(0)}
            ds2 = DBA.ExecuteDataset(dbConn, "SP_SelectOrderDetails", objParams)
            If ds2.Tables(0).Rows.Count > 0 Then
                row.IsExpandable = True
            Else
                row.IsExpandable = False
            End If
            'Dim order As Order = TryCast(row.DataContext, Order)
            '    If row IsNot Nothing AndAlso order IsNot Nothing Then
            '        row.IsExpandable = order.ShipCountry = "France" OrElse order.ShipCountry = "Brazil"
            '    Else
            '        row.IsExpandable = False
            '    End If
        End If
    End Sub

    Private Sub RadGridView1_DataLoading(ByVal sender As Object, ByVal e As GridViewDataLoadingEventArgs)
        Dim dataControl As GridViewDataControl = DirectCast(sender, GridViewDataControl)
        'If dataControl.ParentRow IsNot Nothing Then
        dataControl.CanUserFreezeColumns = False
        dataControl.ShowGroupPanel = False
        dataControl.AutoGenerateColumns = False

        Dim column As New GridViewDataColumn()
        column.Header = "Order Detail ID"
        column.UniqueName = ds2.Tables(0).Columns("Order_Detail_ID").ToString
        'column.DataMemberBinding = New System.Windows.Data.Binding("Order_Detail_ID")
        dataControl.Columns.Add(column)

        'column = New GridViewDataColumn()
        'column.Header = "Item Name"
        'column.DataMemberBinding = New System.Windows.Data.Binding("Item_Name")
        'dataControl.Columns.Add(column)

        'column = New GridViewDataColumn()
        'column.Header = "Item Quantity"
        'column.DataMemberBinding = New System.Windows.Data.Binding("Item_Qty")
        'dataControl.Columns.Add(column)

        ' End If
    End Sub
End Class

 
Here in the VB code I've commented the if condition in dataloading Sub for checking only.so please uncomment that part and check.

The SQL Procedures

1. SP_SelectOrders

SETANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE SP_SelectOrders
AS
SELECT     Order_ID, Order_Date
FROM       Table_Orders
GROUP BY   Order_ID, Order_Date

2.SP_SelectOrderDetails

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE SP_SelectOrderDetails
@OrderID int
AS
SELECT     Order_Details.Order_Detail_ID, Order_Details.Item_Name, Order_Details.Item_Qty
FROM         Table_Orders INNER JOIN
             Order_Details ON Table_Orders.Order_ID = Order_Details.Order_ID
WHERE     (Order_Details.Order_ID = @OrderID)

Ok so I have posted everything for you..

Also am using a DLL to execute the datasets.
Imports Microsoft.StandardLib.DBAccess This is the reference for that.(Its from MS only)

The dataset 2 is also populating ( SP_OrderDetails)..

I can see the plus sign as well.. But clicking on the plus sign reminds me of today ( April fools Day) ..:)  It simply smile @ me ..

Please find the attachment of the output

Cheers..

Renju
 

[Vlad]

Hello Renju,

You may need to set only desired field to the stored procedure instead of the entire row DataContext. Please cast DataContext to your type and get desired property/field. Additionally you may need to have some view-model if you want to use the grid auto-generated hierarchy - do you have property called Order_Details for your parent items? I'm not sure also why you have two definitions - one in XAML and one in your code behind - can you explain?

As I said if you do not want to use MVVM the easiest way yo achieve your goal is:

1. Create empty child table definition without any relations:

<telerik:RadGridView>
            <telerik:RadGridView.ChildTableDefinitions>
                <telerik:GridViewTableDefinition />
             </telerik:RadGridView.ChildTableDefinitions>

2. Declare your child grid using HierarchyChildTemplate and use this child grid Loaded event to execute your stored procedure and assign ItemsSource for the child grid.

Best wishes,
Vlad
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Renju Panicker]

Vlad,

That property order_details was a mistake from my part because I forgot to comment in the XAML

Now I have Created empty child table definition without any relations

and created the heirarchy template as well.again its not working.In the child table am not able to give a name for that so I cant set the itemsource as well

Am not familiar with MVVM tats we are using the conventional Dataset method
Can u explain with an example.

Thanks in advance

Renju

[Renju Panicker]

Hi Vlad,

Do we have any updates on this issue?

Thanks

Renju

[Vlad]

Hi Renju,

Since you have HierarchyChildTemplate you can use Loaded event of the grid declared in this template to get your DataSet and assign ItemsSource to this child grid. Sender argument of the Loaded event is your child grid and DataContext of your child grid is your parent data item.

Regards,
Vlad
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Renju Panicker]

Hi Vlad,
Thanks for the reply..

Please check the code below.No errors..but the childgrid is not populating.The dataset2 is loaded..

I think we are close.but some very minor issue.

Imports Microsoft.SqlServer
Imports System.Data.SqlClient
Imports System.Data
Imports Microsoft.StandardLib.DBAccess
Imports Telerik.Windows.Data
Imports Telerik.Windows.Controls
Imports Telerik.Windows.Controls.GridView

Partial Public Class Window1
    Public ds2 As DataSet
    Private Sub Window1_Loaded(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Me.Loaded
        Dim ds As DataSet
        ds = DBA.ExecuteDataset(dbConn, "SP_SelectOrders")
        RadGridView1.ItemsSource = ds
        Dim definition As New GridViewTableDefinition()
        definition.Relation = New PropertyRelation("Order_Details")
        RadGridView1.ChildTableDefinitions.Add(definition)
    End Sub
    Private Sub RadGridView1_RowLoaded(ByVal sender As Object, ByVal e As Telerik.Windows.Controls.GridView.RowLoadedEventArgs)
        Dim row As GridViewRow = TryCast(e.Row, GridViewRow)
        If row IsNot Nothing Then
            Dim objParams As Object() = {DirectCast(DirectCast(row.DataContext(), System.Object), System.Data.DataRow).ItemArray(0)}
            ds2 = DBA.ExecuteDataset(dbConn, "SP_SelectOrderDetails", objParams)
            If ds2.Tables(0).Rows.Count > 0 Then
                row.IsExpandable = True
            Else
                row.IsExpandable = False
            End If
        End If
    End Sub

    Private Sub RadGridView_DataLoaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
        Dim row As New GridViewRow
        Dim objParams As Object() = {DirectCast(DirectCast(DirectCast(sender, Telerik.Windows.Controls.RadGridView).DataContext, System.Object), System.Data.DataRow).Item(0)}
        ds2 = DBA.ExecuteDataset(dbConn, "SP_SelectOrderDetails", objParams)
        RadGridView1.ChildTableDefinitions.Item(0).DataSource = ds2
    End Sub
End Class

<Window x:Class="Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:telerikData="clr-namespace:Telerik.Windows.Data;assembly=Telerik.Windows.Data"
    xmlns:telerik="http://schemas.telerik.com/2008/xaml/presentation"
    xmlns:data="clr-namespace:Telerik.Windows.Data;assembly=Telerik.Windows.Data"
    Title="Window1" Height="515" Width="933" >

    <Grid>
        <telerik:RadGridView x:Name="RadGridView1" CanUserFreezeColumns="False" ItemsSource="{Binding}" RowLoaded="RadGridView1_RowLoaded"  IsReadOnly="True" AutoGenerateColumns="False" ShowGroupPanel="False" Margin="10,0,0,0">
            <telerik:RadGridView.ChildTableDefinitions>
                <telerik:GridViewTableDefinition>
                    <!--<telerik:GridViewTableDefinition.Relation>
                        <telerikData:PropertyRelation ParentPropertyName="Order_Details"/>
                    </telerik:GridViewTableDefinition.Relation>-->
                </telerik:GridViewTableDefinition>
            </telerik:RadGridView.ChildTableDefinitions>
            <telerik:RadGridView.Columns>
                <telerik:GridViewDataColumn UniqueName="Order_ID" Header="Order ID" />
                <telerik:GridViewDataColumn UniqueName="Order_Date" Header="Order Date" />
            </telerik:RadGridView.Columns>
            <telerik:RadGridView.HierarchyChildTemplate >
                <DataTemplate>
                    <telerik:RadGridView IsReadOnly="True" Name="ChildGrid1"
                                AutoGenerateColumns="False" ItemsSource="{Binding}"  Loaded="RadGridView_DataLoaded"
                                RowIndicatorVisibility="Collapsed" CanUserFreezeColumns="False" IsFilteringAllowed="False" ShowGroupPanel="False">
                        <telerik:RadGridView.Columns>
                            <telerik:GridViewDataColumn Header="Detail ID" Width="60" UniqueName="Order_Detail_ID" IsVisible="True" />
                            <telerik:GridViewDataColumn Header="Item Name" Width="60"  UniqueName="Item_Name" IsVisible="True" />
                            <telerik:GridViewDataColumn Header="Item Quantity" Width="60"  UniqueName="Item_Qty" IsVisible="True" />
                        </telerik:RadGridView.Columns>
                    </telerik:RadGridView>
                </DataTemplate>
            </telerik:RadGridView.HierarchyChildTemplate>
        </telerik:RadGridView>
    </Grid>
</Window>

Am not able to bind the dataset2 to the the child grid in a proper manner

Thanks and Regards

Renju

[Vlad]

Hello,

Please assign ItemsSource for the child grid:

DirectCast(sender, Telerik.Windows.Controls.RadGridView).ItemsSource = ds2

Best wishes,
Vlad
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Renju Panicker]

Vlad.............

Благодаря

Hope its Thanks in Bulgarian....


It works like a charm and thanks a lot once more

Regards

Renju

[Renju Panicker]

So the final code is as below ( Might be useful for anyone using..)

<Window x:Class="Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:telerikData="clr-namespace:Telerik.Windows.Data;assembly=Telerik.Windows.Data"
    xmlns:telerik="http://schemas.telerik.com/2008/xaml/presentation"
    xmlns:data="clr-namespace:Telerik.Windows.Data;assembly=Telerik.Windows.Data"
    Title="Window1" Height="515" Width="933" >

    <Grid>
        <telerik:RadGridView x:Name="RadGridView1" CanUserFreezeColumns="False"
                             ItemsSource="{Binding}" RowLoaded="RadGridView1_RowLoaded"
                             IsReadOnly="True" AutoGenerateColumns="False" ShowGroupPanel="False" Margin="10,0,0,0">
            <telerik:RadGridView.ChildTableDefinitions>
                <telerik:GridViewTableDefinition>
                    <!--<telerik:GridViewTableDefinition.Relation>
                        <telerikData:PropertyRelation ParentPropertyName="Order_Details"/>
                    </telerik:GridViewTableDefinition.Relation>-->
                </telerik:GridViewTableDefinition>
            </telerik:RadGridView.ChildTableDefinitions>
            <telerik:RadGridView.Columns>
                <telerik:GridViewDataColumn UniqueName="Order_ID" Header="Order ID" />
                <telerik:GridViewDataColumn UniqueName="Order_Date" Header="Order Date" />
            </telerik:RadGridView.Columns>
            <telerik:RadGridView.HierarchyChildTemplate >
                <DataTemplate>
                    <telerik:RadGridView IsReadOnly="True" Name="ChildGrid1"
                                AutoGenerateColumns="False" ItemsSource="{Binding}"  Loaded="RadGridView_DataLoaded"
                                RowIndicatorVisibility="Collapsed" CanUserFreezeColumns="False" IsFilteringAllowed="False" ShowGroupPanel="False">
                        <telerik:RadGridView.Columns>
                            <telerik:GridViewDataColumn Header="Detail ID" Width="60" UniqueName="Order_Detail_ID" IsVisible="True" />
                            <telerik:GridViewDataColumn Header="Item Name" Width="60"  UniqueName="Item_Name" IsVisible="True" />
                            <telerik:GridViewDataColumn Header="Item Quantity" Width="60"  UniqueName="Item_Qty" IsVisible="True" />
                        </telerik:RadGridView.Columns>
                    </telerik:RadGridView>
                </DataTemplate>
            </telerik:RadGridView.HierarchyChildTemplate>
        </telerik:RadGridView>
    </Grid>
</Window>

VB Code

Imports Microsoft.SqlServer
Imports System.Data.SqlClient
Imports System.Data
Imports Microsoft.StandardLib.DBAccess
Imports Telerik.Windows.Data
Imports Telerik.Windows.Controls
Imports Telerik.Windows.Controls.GridView

Partial Public Class Window1
    Public ds2 As DataSet
    Private Sub Window1_Loaded(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Me.Loaded
        Dim ds As DataSet
        ds = DBA.ExecuteDataset(dbConn, "SP_SelectOrders")
        RadGridView1.ItemsSource = ds
        Dim definition As New GridViewTableDefinition()
        definition.Relation = New PropertyRelation("Order_Details")
        RadGridView1.ChildTableDefinitions.Add(definition)
    End Sub
    Private Sub RadGridView1_RowLoaded(ByVal sender As Object, ByVal e As Telerik.Windows.Controls.GridView.RowLoadedEventArgs)
        Dim row As GridViewRow = TryCast(e.Row, GridViewRow)
        If row IsNot Nothing Then
            Dim objParams As Object() = {DirectCast(DirectCast(row.DataContext(), System.Object), System.Data.DataRow).ItemArray(0)}
            ds2 = DBA.ExecuteDataset(dbConn, "SP_SelectOrderDetails", objParams)
            If ds2.Tables(0).Rows.Count > 0 Then
                row.IsExpandable = True
            Else
                row.IsExpandable = False
            End If
        End If
    End Sub

    Private Sub RadGridView_DataLoaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
        Dim row As New GridViewRow
        Dim objParams As Object() = {DirectCast(DirectCast(DirectCast(sender, Telerik.Windows.Controls.RadGridView).DataContext, System.Object), System.Data.DataRow).Item(0)}
        ds2 = DBA.ExecuteDataset(dbConn, "SP_SelectOrderDetails", objParams)
        DirectCast(sender, Telerik.Windows.Controls.RadGridView).ItemsSource = ds2
        'RadGridView1.ChildTableDefinitions.Item(0).DataSource = ds2
    End Sub
End Class

Also please keep in mind that am using the DLL for connecting to DB and the dataset is also populated using methods of that DLL

Many Thanks

Renju