I’ve been successfully using manual load on demand in WebHierarchicalDataGrid for a while now, but recently noticed strange thing. The deeper in grid’s hierarchy I expanded the children – the slower it went.
In my case every time user clicks [+] to expand a row, VB.NET code calls an SQL Server Stored procedure to bring in child rows. I grew suspicious and fired up SQL Profiler. What I saw surprised me. Number of calls to the stored procedure increased the deeper in grid’s hierarchy I expanded the children. When I clicked [+] on the root level it resulted in 1 SP call. Clicking [+] on the child to expand grandchild – 2 calls. Expanding grandchild to see grand-grandchild rows – 3 calls, etc.
I went back to the VB.NET code that calls the SP, it’s the handler for RowIslandsPopulating event:
Protected Sub xmyGrid_RowIslandsPopulating(ByVal sender As Object, _ ByVal e As ContainerRowCancelEventArgs) Handles xmyGrid.RowIslandsPopulating e.Cancel = True CreateRowIslands(e.Row) End Sub
Where CreateRowIslands is a VB sub that calls SQL Server Stored procedure to create child grid. Setting a breakpoint here I confirmed my suspicions: RowIslandsPopulating event is called for rows that already have been expanded. E.g. if I expand grand-child to display grand-grand-child rows, the event will fire for root, child and grand-child – 3 times, in turn calling stored procedure 3 times.
How to avoid this? While comparing the multiple calls I realized that rows already expanded had their Expanded property set to True. And while we can prevent RowIslandsPopulating event from firing multiple times, we can at least tell it not to do anything if RowIslands are already populated. So, the very slight change:
Protected Sub xmyGrid_RowIslandsPopulating(ByVal sender As Object, _ ByVal e As ContainerRowCancelEventArgs) Handles xmyGrid.RowIslandsPopulating e.Cancel = True If e.Row.Expanded = False CreateRowIslands(e.Row) End Sub
Solves the issue.