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. Continue reading 'WHDG: RowIslandsPopulating event fires multiple times'»
This has probably been discussed a lot before, but just in case here it is again, possible solution for following scenario:
You’re calling SQL Server stored procedure from your .NET code and it runs extremely slow. When you run same SP with exactly the same parameters (as captured by SQL Server Profiler) directly in SQL Server Management Studio, it runs very fast. What gives?
Chances are – that SP was executed before and query plan was cached for the specific parameters. To avoid this add WITH RECOMPILE option to your CREATE PROCEDURE or ALTER PROCEDURE statement. This will force SQL Server to create a new query plan every time SP runs, perhaps adding slight overhead, but creating an optimized path that will cover that overhead tenfold.
Ordinary when you create a parent-child relationship between DataTables in a DataSet – there is a requirement that all values of the parent columns need to be unique. If they’re not – you will get an error: “These columns don’t currently have unique values“.
But there’re times when you need to make the relationship work even if those values are not unique. The solution is not to create constrain when creating the relationship. This can be done for example by passing FALSE as value for createConstrains parameter of Dataset.Relations.Add method:
oMyDataSet.Relations.Add("MyRel", _
oMyDataSet.Tables("TheParent").Columns("ParentColumn"), _
dtSet.Tables("TheChild").Columns("ChildColumn"), _
False)
Even though I have my issues with Infragistics WebHierarchicalDataGrid control, it has some neat features and I found that with some tweaks you can make it work.
Case in point: Manual Load on Demand. If you have hierarchical data structure, it allows you to retrieve only root level data and then when user clicks “Expand” arrow – get additional data on as needed basis:
This is achieved by handling RowIslandsPopulating grid’s event in which you can run a DB query based on parent row data, then manually create a ContainerGrid object bind it to the data and add it to parent row RowIslands collection:
Protected Sub myGrid_RowIslandsPopulating(ByVal sender As Object, ByVal e As ContainerRowCancelEventArgs) Handles myGrid_.RowIslandsPopulating
e.Cancel = True
Dim oData as SomeDataType = GetData()
Dim oChildGrid As New ContainerGrid()
e.Row.RowIslands.Add(oChildGrid)
oChildGrid.DataKeyFields = "SOME_ID"
oChildGrid.Level = e.Row.Level + 1
oChildGrid.DataSource = oData
oChildGrid.DataBind()
End Sub
For this approach to work top-level rows need to display “Expand” arrows that user can click. Continue reading 'WebHierarchicalDataGrid: Manual Load on Demand when bound to DataSet'»
If you’re binding an ADO.NET DataTable to Infragistics UltraWebGrid and then programmaticaly sort the grid (e.g. add a column to a band’s SortedColumns collection) you may get an error:
Cannot find column My Column Name.
with stack trace starting from grid databinding and finishing in datatable’s sorting:
at System.Data.DataTable.ParseSortString(String sortString)
at System.Data.DataView.CheckSort(String sort)
at System.Data.DataView.set_Sort(String value)
at Infragistics.WebUI.UltraWebGrid.DBBinding.ProcessDataViewForFillRows(DataView dataView, RowsCollection rows)
at Infragistics.WebUI.UltraWebGrid.DBBinding.FillRows(UltraWebGrid grid, RowsCollection rows, IEnumerable datasource)
at Infragistics.WebUI.UltraWebGrid.DBBinding.BindList(IEnumerable datasource)
at Infragistics.WebUI.UltraWebGrid.DBBinding.DataBind(Object dataSource, String dataMember)
at Infragistics.WebUI.UltraWebGrid.UltraWebGrid.DataBind()
If the grid binds OK without sorting and grouping, but fails with either – most likely the culprit is one of the columns in data table. Continue reading '“Cannot find column” DataTable error while grouping or sorting Infragistics UltraWebGrid'»
Often you have to operate with flattened data that in reality contains multiple levels of hierarchy. For example it can come as a result of several SQL JOIN statement and look like this:
In this example data consist of static root column, region, site, type and state. And the data has clearly defined hierarchy (e.g. Region “India” has site “Bangalore”, site “Bangalore” has types “Application” and “Area”, type “Application” has states “N/A” and “Testing”).
To load this data into Infragistics UltraWebTree I put together a small procedure: Continue reading 'Using LINQ to bind flat data to Infragistics UltraWebTree'»
LINQ is truly integrated into VB.NET. This allows not only to use LINQ-specific query language in a standard VB.NET code, but use VB.NET code in a LINQ query. Consider function from the previous post. To make it universal we can pass one more parameter
“Aggregate Type” and based on that parameter return Min, Max, Avg, Sum or Count
Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable, i_iAggregateType as Integer) As DataTable
dim aQuery = From row In i_dSourceTable Group By Group1 = row(i_sGroupByColumn) Into Group Select Group1, Aggr = Choose(i_iAggregateType, Group.Min(Function(row) row(i_sAggregateColumn)), Group.Max(Function(row) row(i_sAggregateColumn)), Group.Sum(Function(row) row(i_sAggregateColumn)), Group.Avg(Function(row) row(i_sAggregateColumn)), Group.Count(Function(row) row(i_sAggregateColumn)))
return aQuery.toDataTable
End Function
In this example VB.NET function Choose is used inside of a LINQ query’s Select Statement. If i_iAggregateType parameter is equal 1 – Minimum value, will be calculated, 2 – Maximum etc.
I’ve described before how to group and aggregate data in ADO.NET data table using standard .NET 2.0 features. But if you happen to use .NET 3.5 or above and Visual Studio 2008 or above – that entire block of code can be replaced with a single LINQ query:
Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable
dim aQuery = From row In i_dSourceTable Group By Group1 = row(i_sGroupByColumn) Into Group Select Group1, Aggr = Group.Count(Function(row) row(i_sAggregateColumn))
return aQuery.toDataTable
End Function
The query at line 3 is a LINQ to DataSet query, so reference to System.Data.Linq has to be added to your project. The Group.Count aggregate can be replaced with Group.Max, Group.Min, Group.Sum or Group.Avg to perform respectful function.
There is one caveat though. Continue reading 'Grouping ADO.NET DataTable using LINQ'»
Often there is a need to perform group by operations on in-memory .NET data tables. You can’t run a standard SQL statement and LINQ is not available prior version 3.0 of .NET. To compensate for this shortcoming I put together a small function that works in .NET 2.0. As input parameters it accepts source DataTable, column name to group by and a column name to perform aggregate operation on. It returns a grouped data as data table:
Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable
Dim dv As New DataView(i_dSourceTable)
'getting distinct values for group column
Dim dtGroup As DataTable = dv.ToTable(True, New String() {i_sGroupByColumn})
'adding column for the row count
dtGroup.Columns.Add("Count", GetType(Integer))
'looping thru distinct values for the group, counting
For Each dr As DataRow In dtGroup.Rows
dr("Count") = i_dSourceTable.Compute("Count(" & i_sAggregateColumn & ")", i_sGroupByColumn & " = '" & dr(i_sGroupByColumn) & "'")
Next
'returning grouped/counted result
Return dtGroup
End Function
The function first gets distinct values for group-by column, by creating a data view from source data table and using DataView’s “ToTable” method. It then loops thru these distinct values performing aggregate function on the source table using DataTable’s “Compute” method – Count in this case, but it can easily be replaced with other aggregates.
Comments (0) 
Themocracy