Tag Archives: Data

WebHierarchicalDataGrid: Not ready for prime-time

I had high hopes for Infragistics Web Hierarchical Data Grid control. Especially in NetAdvantage 10.3 release (current as of this post). Control from Aikido Framework, build on AJAX from ground up, lightweight and fast – I was really excited.

Unfortunately it didn’t turned out exactly as I expected.

First – WHDG cannot bind to a flat ADO.NET data table. Why, you ask, I need to bind flat data to an hierarchical control? Well, it’s the only grid in Aikido that supports OutlookGroupBy mode and I need to be able to group my flat data. But attempt to bind WHDG to DataTable throws an enigmatic InvalidOperationException without giving any clue as why. There’s a workaround – to create a dummy DataSet, add the table to it and bind control to the dataset – but that’s just silly. And that’s not the worst of it. Continue reading →

Locate exact match using UltraWebGrid’s client-side “.find()” method

Infragistics UltraWebGrid offers extensive client-side Object model and objects at several level of hierarchy offer “.find” method to locate cell with specific data. For example to locate a cell with specific text in a specific column – following code can be used:

//Find cell in first grid column with text "67" 
var oGrid = igtbl_getGridById('xmyGrid')
var iLookUpValue = '67'
var oFoundCell =  oGrid.Bands[0].Columns[0].find(iLookUpValue, false)

This works, but unfortunately the “.find” method searches for cell’s text instead of value, thus finding any partial match. In the example above cells with values 567, 671 etc. will be found which is no good in many cases, for example when you’re looking for a numeric ID.

Fortunately the “.find” method accepts regular expression as a search parameter. The solution is to apply “^…$” RegEx expression to perform exact match. So if we change the last line in the code above to:

var oFoundCell =  oGrid.Bands[0].Columns[0].find('^' + iLookUpValue + '$', false)

only exact match will be searched for.

Custom grouping in classic Infragistics UltraWebGrid

Infragistics UltraWebGrid offers a nice grouping feature: when the grid is in OutlookGroupBy mode, you can group similar data with very little coding required, you can go from this view:
Before Grouping
to this:
After Grouping
by just dragging columns to designated area.

But what if you want to group by first letter of a name or a year of a date? New WebHierarchicalDataGrid control offers this functionality after 10.2 release of Infragistics NetAdvantage, but if you invested years of work in classic UltraWebGrid – it’s not easy to move to a brand new control cold turkey. There’re other methods that offer custom grouping for UltraWebGrid, but the ones I found were pretty convoluted (like create a hidden column, populate it with data to group by etc.) Here is a simpler approach. Continue reading →

JavaScript IsDate(): Validate date according browser locale

In one of my recent projects I needed to validate whether user’s input is a valid date, and this needed to be done client-side, in browser prior submission to the server. Lazy as I am, I Googled for a ready-to-use code snippet. There’re plenty of versions out there, but most of them offer incomplete solution and none of them take into account browser locale, you know – the language settings:

So I decided to cook something of my own Continue reading →

“Cannot find column” DataTable error while grouping or sorting Infragistics UltraWebGrid

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 →

Showing ALL filters in UltraWebGrid with paging

If you’re using Infragistics classic UltraWebGrid with LoadOnDemand not set and paging enabled, getting column filters to work can be tricky. By default clicking on Filter icon will display column data from current page only, ignoring other pages. To make it work you have to take matter in your own hands – populate filter data in code.

The best place to do it is in InitializeLayout event. There you can loop thru all the columns, calling function to populate column filters:

Protected Sub xMyGrid_InitializeLayout(ByVal sender As Object, ByVal e As LayoutEventArgs) Handles xMyGrid.InitializeLayout
    For Each ugColumn As UltraGridColumn In e.Layout.Grid.Columns
        GatherFilterDataForColumn(ugColumn)
    Next
End Sub

Continue reading →

Using LINQ to bind flat data to Infragistics UltraWebTree

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 →

T-SQL: Automatically Upgrade NVARCHAR(N) field to NVARCHAR(MAX)

If you’re writing an SQL upgrade script that updates a field in your table from NVARCHAR(n) data type to NVARCHAR(max) and the script is designed to run many times – an easy way to avoid multiple table alteration is to check for max character length for that field, i.e.:

IF (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MY_TABLE' AND COLUMN_NAME = 'MY_COLUMN')<>-1
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ALTER COLUMN [MY_COLUMN] [nvarchar](max) NOT NULL
END

This code checks length of the field which is specific number for standard NVARCHAR and -1 for NVARCHAR(max), and alters column type only if it hasn’t already been altered.

T-SQL: Add column with default values to a table

Let’s say you need to add a column to an existing table that already contains some rows. And this new column needs to be prepopulated with a default value. For example integer nullable column NEW_COLUMN is added to table MY_TABLE. Common approach is to check for column existence, and if it doesn’t exist – create and populate it:

if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL
	UPDATE [dbo].[MY_TABLE] SET [NEW_COLUMN] = 1
END

But this doesn’t work – you get “Invalid column name” error. The trick is – you don’t need a separate UPDATE statement. ALTER TABLE … ADD statement has a DEFAULT parameter. You can modify previous code like this:

if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL DEFAULT 1 WITH VALUES
END

This ALTER statement assigns default value of 1 to the new column. WITH VALUES parameter is important, it populates existing rows with the default value (without it it will be populated by NULLs).

Grouping ADO.NET DataTable using LINQ

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 →