Paging, sorting, filtering at DB level the datasource

[Massimiliano]

I'm trying to implement a "classic" solution where filtering, sorting and paging happens at db level and the retrieved resultset is only the one needed. That way it should be lighter to handle bigger database since if you have a page of 50 items you only set those 50 items as datasource.
I would like to keep a tradeoff between this performance boost and the automatic functionalities the grid offers (mostly in the CRUD scendario, of course I'll have to handle all the selects for paged and filtered datasets)
Anyway... I'm trying to accomplish this with NeededDataSource (I was wondering if ObjectDataSource is better for this.. is it?)
I'm finding my way through paging and this is quite straightforward: 

Protected Sub RadGrid1_NeedDataSource(sender As Object, e As GridNeedDataSourceEventArgs)
    Dim dataSource As New MyUsersManager
    Dim query As New PagedQuery
    Dim result As ResultPage(Of MyUser)

    query.ItemsPerPage = RadGrid1.MasterTableView.PageSize
    query.PageNumber = RadGrid1.MasterTableView.CurrentPageIndex + 1

    result = dataSource.GetPage(query)

    RadGrid1.MasterTableView.VirtualItemCount = result.TotalItems
    RadGrid1.MasterTableView.DataSource = result.Items
End Sub

The problem is that I always get page index 0... even if the pager updates and I'm on page 2 or 3 I always get pagindex 0 in the NeedDataSource events... why?

I wonder how can I factor in the filtering and the sorting... How can I retrieve the filtering parameters and sorting (maybe even multisorting) parameters inside NeedDataSource? Should I create page properties that are updated from filtering and sorting events and then read from NeedDataSource event, or is there a more straightforward way to do it?

What about grouping? Will this work with my custom implementation? I suppose it won't show the summaries for grouped paging anymore (like "you are viewing n records of group x and still more y records on next page" or such) but will it work the same? Or do I have to retrieve the whole 1 milion records dataset to benefit from grouping? Same for footer summaries, will those work on a single page basis or do they only work with the full db?

I think this should be a common scenario for larger implementation for e-commerce sites, or where db grows to a considerable size in general  (like for example in a multiapp SaaS framework wich serves multiple sites from a single app and the DB has data from all the sites)

Thanks in advance

[Massimiliano]

Since I think that integrating RadGrid with a native SQL filtering can be a big performance improvement and of much interest for many other devs, here are some news about my implementation.
My goal is to have RadGrid do its sorting (even multi column), filtering, paging and even grouping, at the DB level.
Just to make an example, I overcome the paging issue (more on that later) implemented the multi-sorting functionality already at db level, and I'm testing on a 4 millions row table with about 30 columns and integer primary key.

With this implementation paging over the 4 millions record on a medium home-pc (intel i5 3.20Ghz - 12GB ram), running visual studio, mp3 player, antivirus and all the other dozens stuff that usually runs on home pc (plus some more I'm sure), takes less than a second(!).
I didn't do milleseconds measuring (but I will eventually do) but paging a 4 millions table in less than a second per page with RadGrid is a funny experience, and that's why I think this deserves further investigation on the other functionalities as well (filtering, sorting grouping).

Now let's look at sorting (wich I already implemented). Sorting an unindexed varchar(256) field takes a bit long BUT if you just add an index on the column and sorting (or paging the sorted results) takes just 4-5 seconds! I will move on filtering and (multi)grouping but I'm sure I will need some help (expecially on grouping if even this is applicable wich I'm not sure of)

About my issues with paging. It seems that the code was correct BUT the NeedDataSource event was fired before the paging events. That's why it always returned 0 as page index. This was related to viewstate being disabled. If viewstate is correctly enabled the NeedDataSource gets fired correctly after the paging, sorting (and others) events.
About this point (I read another topic here on the subject) it is very odd since I have EnableViewste="false" on my MasterPage, ContentPage BUT I have enabled it for the grid (and MasterTableView) with no success.
Then I tryed to enable viewstate on the content page directive also but with no success.
Finally I had to enable viewstate on the master page directive as well. This is very odd to me. Someone has some explanation on the subject?

About the code for server side paging and (multi)sorting here is my implementation so far in the NeedDataSource event. It is still a prototype but it's quite straightforward:

Protected Sub RadGrid1_NeedDataSource(sender As Object, e As GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource

    Dim dataSource As New EvaUsersManager

    Dim query As New PagedQuery

    Dim result As ResultPage(Of EvaUser)

    Dim view As Telerik.Web.UI.GridTableView = DirectCast(sender, RadGrid).MasterTableView

 

    Dim viewSort As Telerik.Web.UI.GridSortExpressionCollection = view.SortExpressions

    Dim viewFilter As String = view.FilterExpression

    Dim viewGrouping As Telerik.Web.UI.GridGroupByExpressionCollection = view.GroupByExpressions

 

    ' Set paging options

    query.ItemsPerPage = view.PageSize

    query.PageNumber = view.CurrentPageIndex + 1

 

    ' Set sorting options

    If viewSort IsNot Nothing AndAlso viewSort.Count > 0 Then

        For Each sort As Telerik.Web.UI.GridSortExpression In viewSort

            Dim field As String = sort.FieldName

            Dim order As String = sort.SortOrderAsString

            query.SqlSortingColumns.Add(field, order)

        Next

    End If

    result = dataSource.GetPage(query)

 

    view.VirtualItemCount = result.TotalItems

    view.DataSource = result.Items

End Sub

My business object "EvaUsersManager" wich is responsible for querting the db accepts a "PagedQuery" object where you can pass page size, page index, search params, order params and so on.
But you can easily adapt it to your business objects. The point here is how to exctract paging, sorting, filtering and grouping data from the grid, in response to an event, to retrieve only the row responding to that criteria instead of the whole table.

I will update with my progress but if you have suggestion on the code so far or on the filtering and grouping implementation, please don't hesitate to write your thoughts here.

[Massimiliano]

Some updates on the advancement of the project.
First a little correction about ordering times in the 4 million rows table. I said it took around 4-5 seconds (on a windows 7 machine Intel i5 3.2 ghz + 12GB ram) but there were some unwanted redundancy in my business objects, so it really just takes <1 sec. for both sorting (on an indexed column wich is not necessarly the primary key) + paging. In <1 sec you can filter, sort an page a 4 million records table with rad grid.

About filtering wich I implemented (in the post above you'll find paging and (multi)sorting).
When I first tried to obtain the filter expression from RadGrid I got a Linq expression (DataColumn.Expression) and this sent me into panic since the microORM (PetaPoco) I use in my business object doesn't handle Linq queries. After about 2 hours of research on how to translate the expression to SQL and similar workarounds, I luckly found out that RadGrid support several modes to render the filter expressions, including native SQL (love you Telerik!).
So the filtering part was really easy at this point, I just had to take the filter expression string and pass it up to my Business Object, alongside paging and sorting options (complete updated code follows).
Now the question is... since this is a string and not a parametrized command, is the string always safe to pass as pure SQL? (please answer to this). I made some fast testing and saw for example that ' is automatically escaped as '' in the string, so I assume some sort of SQL-injection prevention is made before creating the FilterExpression string (is this correct?).

Now I will move on to try RadGrid grouping inside SQL Server, maybe I'll need some hints on this subject so you are more than welcome to join in (both Telerik admins and users).

Here is the code that handles paging, filtering and sorting server-side (at the DB level) with RadGrid:

Protected Sub RadGrid1_NeedDataSource(sender As Object, e As GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource
    Dim dataSource As New EvaUsersManager
    Dim query As New PagedQuery
    Dim result As ResultPage(Of EvaUser)
    Dim view As Telerik.Web.UI.GridTableView = DirectCast(sender, RadGrid).MasterTableView

    Dim viewSort As Telerik.Web.UI.GridSortExpressionCollection = view.SortExpressions
    Dim viewFilter As String = view.FilterExpression
    Dim viewGrouping As Telerik.Web.UI.GridGroupByExpressionCollection = view.GroupByExpressions

    ' Set paging options
    query.ItemsPerPage = view.PageSize
    query.PageNumber = view.CurrentPageIndex + 1

    ' Set filtering options
    If Not String.IsNullOrWhiteSpace(viewFilter) Then
        query.SqlAdditionalSearch = viewFilter
    End If

    ' Set sorting options
    If viewSort IsNot Nothing AndAlso viewSort.Count > 0 Then
        For Each sort As Telerik.Web.UI.GridSortExpression In viewSort
            query.SqlSortingColumns.Add(sort.FieldName, sort.SortOrderAsString)
        Next
    End If

    result = dataSource.GetPage(query)

    view.VirtualItemCount = result.TotalItems
    view.DataSource = result.Items
End Sub

[Massimiliano]

As I supposed I need a bit of help for grouping...
It seems I'm not able to retrieve te GroupByExpressions from the NeedDataSource event.
If I make a grouping in the grid, and check the NeedDataSource event, my MasterTableView.GroupByExpressions count is always zero.
I checked the GroupsChanging event, and it fires correctly BEFORE the NeedDataSource event, and inside the GridGroupsChangingEventArgs I find the grouping expression. 
So why I don't find it in the NeedDataSource, in MasterTableView.GroupByExpressions wich happens right next?

The only solution that comes to my mind right now is to create a local private property wich will contain the GroupByExpressions, populated by the  GroupsChanging, but since I've been able to retrieve filtering, paging and sorting parameters straight from the NeedDataSource I don't think there should be a need for this workaround, I'm sure I'm missing something.
EDIT: also in the GroupsChanging event, from GridGroupsChangingEventArgs I can retrieve only the LAST grouping action (field) so I should handle some sort of persistance (viewtate) myself.. Think this is redundant and shoul be an easier way.

Any help is greatly appreciated at this point.

Test code portion follows but GroupByExpressions.Count is always 0

' Set grouping options



Dim view As Telerik.Web.UI.GridTableView = grid.MasterTableView

Dim viewGrouping As Telerik.Web.UI.GridGroupByExpressionCollection = view.GroupByExpressions

If viewGrouping IsNot Nothing AndAlso viewGrouping.Count > 0 Then

    For Each group As Telerik.Web.UI.GridGroupByExpression In viewGrouping

        Dim a As String = group.Expression

        Dim b As GridGroupByFieldList = group.GroupByFields

        Dim c As GridGroupByFieldList = group.SelectFields

    Next

End If

[Tsvetoslav]

Hello Massimiliano,

Custom grouping is not a supported scenario with RadGrid. You need to leave it to the control to do it for you.
 
Regards,
Tsvetoslav
Telerik

[Massimiliano]

You are right it was my "misunderstanding" of how SQL grouping (with aggregates) was somehow related to RadGrid way of grouping...
Well I think my implementation it's well over then.
You are left with 2 options about grouping. Apply grouping only to the current page you are viewing (you could programmatically disable paging for a small recordset and display all in one page) OR disable server side sorting/paging/filtering and retrieve the whole recordset and have dynamic grouping on the grid and on the whole recordset.

For that purpose I encapsulated my implementation so far in a static helper class that you can reuse in every NeedDataSource event, chosing if you want to retrieve the whole recordset or do filtering/paging/sorting on the server.
Here is the code that I hope others will find useful (since it lets you do all those things on huge tables in no time):

Protected Sub RadGrid1_NeedDataSource(sender As Object, e As GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource
    Dim dataSource As New EvaUsersManager

    DalHelpers.GetNeedDataSource(Of EvaUser)(dataSource, sender, True)
End Sub

This is in the actual aspx page, where you call the helper class passing the BLL manager class that retrieves the recordset and the DTO type to be returned (Of EvaUser). 

And this is the GetNeedDataSource method (if you need this on a DetailTable you could pass the view from the NeedDataSourceEvent.. didn't try this Master/Detail implementation yet). I'm totally new to OOP so I'm sure there is a more "nice" way of doing this (sorry)

''' <summary>

''' Retrieve DB level paged recordest or

''' full recordset based on hasPaging property

''' </summary>

''' <typeparam name="T"></typeparam>

''' <param name="dataSource">Dto/Dtv business object (es. EvaUsersManager)</param>

''' <param name="sender">NeedDataSource event sender object (grid)</param>

''' <param name="hasPaging">Perform paging/sorting/filtering in the db or get the whole recordset and perform them in the grid</param>

Public Shared Sub GetNeedDataSource(Of T)(dataSource As Object, sender As Object, hasPaging As Boolean)

    Dim result As ResultPage(Of T)

    Dim grid As Telerik.Web.UI.RadGrid = DirectCast(sender, RadGrid)

    Dim view As Telerik.Web.UI.GridTableView = grid.MasterTableView

 

    If hasPaging Then

        Dim query As New PagedQuery

 

        Dim viewSort As Telerik.Web.UI.GridSortExpressionCollection = view.SortExpressions

        Dim viewFilter As String = view.FilterExpression

  

        ' Set paging options

        query.ItemsPerPage = view.PageSize

        query.PageNumber = view.CurrentPageIndex + 1

 

        ' Set filtering options

        If Not String.IsNullOrWhiteSpace(viewFilter) Then

            query.SqlAdditionalSearch = viewFilter

        End If

 

        ' Set sorting options

        If viewSort IsNot Nothing AndAlso viewSort.Count > 0 Then

            For Each sort As Telerik.Web.UI.GridSortExpression In viewSort

                query.SqlSortingColumns.Add(sort.FieldName, sort.SortOrderAsString)

            Next

        End If

 
        result = dataSource.GetPage(query)

    Else

        view.AllowCustomPaging = False

        view.AllowCustomSorting = False

 

        If result Is Nothing Then result = New ResultPage(Of T)

        result.Items = dataSource.GetAll()

        result.TotalItems = result.Items.Count

    End If

 

    view.VirtualItemCount = result.TotalItems

    view.DataSource = result.Items

End Sub

Hope this can be useful to someone.

[Massimiliano]

During implementation stage I've stumbled upon a couple of issues (so far) with this implementation.
The issue are about how FilterExpression (SQL version) is generated for datetime and boolean data types, wich seem no to be compatible with SQL Server.
RadGrid FilterExpression for boolean result in something similar to "COLUMN = True" but the correct implementation for SQL Server (don't know about other dbs) is "COLUMN = 'True'" (or just "COLUMN" and "NOT COLUMN" I guess).
The date are converted in a format like '12/07/2013,12:03:83' but this throws an exception in SQL Server and the correct format (wich is also indipendent from regional settings) should be '2013/07/12 12:03:83'

I've been able to fix the first issue with boolean since is quite straightforward and added to the above implementation those lines:

' Set filtering options
If Not String.IsNullOrWhiteSpace(viewFilter) Then
    ' Adjust boolean values
    viewFilter = Replace(viewFilter, " = True", " = 'True'")
    viewFilter = Replace(viewFilter, " = False", " = 'False'")

    query.SqlAdditionalSearch = viewFilter
End If

With dates and multiple filters from various columns with several data types active at once (and maybe even multiple data formats in a multi culture application where users choose their locale) the problem seem more serious to solve.
So I've been trying to find a solution before I reach the NeedDataSource event, where FilterExpression is already prepared with all the fields and filter expressions.
I tryed manipulating things inside the ItemCommand event but now I'm really struck and need some help to normalize dates. The main issue that I'm facing is in those lines:

If Not String.IsNullOrWhiteSpace(newFilter) Then
    e.Canceled = True

    If grid.MasterTableView.FilterExpression = "" Then
        grid.MasterTableView.FilterExpression = newFilter
    Else
        grid.MasterTableView.FilterExpression = "((" & grid.MasterTableView.FilterExpression & ") AND (" & newFilter & "))"
    End If

    RadGrid1.Rebind()
End If

Where newFilter is the normalized filter for the actual date column that is being filtered (we are in the ItemCommand event remember), so newFilter for example contains "(COLUMN >= '2013/07/12 12:03:83')". 
All other filters by other columns are mantained, the problem is that even this column filter are mantained, so if I change the filter again for that colum to say LessThan, I will end up with a FilterExpression like ((COLUMN >= '2013/07/12 12:03:83') AND (COLUMN < '2013/07/12 12:03:83')).
Either I have to chose another path or need some workaround on this to remove the previous filter from the same column, or to recreate the complete FilterExpression without adding that colum (where/when is the FilterExpression compiled btw?) or such.
Here follows the full implementation but really need help on this (though I still think that SQL version of FilterExpression should be fixed natively as suggested at least with those 2 data types).

Protected Sub RadGrid1_ItemCommand(ByVal source As Object, ByVal e As GridCommandEventArgs) Handles RadGrid1.ItemCommand
    If e.CommandName = RadGrid.FilterCommandName Then
        Dim filterPair As Pair = DirectCast(e.CommandArgument, Pair)
        Dim column As String = filterPair.Second.ToString
        Dim filterFunction As String = filterPair.First.ToString

        If column = "LastLoginDate" Then
            FilterDateToDb(e, RadGrid1)
        End If
    End If
End Sub

Private Function FilterDateToDb(e As GridCommandEventArgs, grid As Telerik.Web.UI.RadGrid) As String
    Dim filterPair As Pair = DirectCast(e.CommandArgument, Pair)
    Dim column As String = filterPair.Second.ToString
    Dim filterFunction As String = filterPair.First.ToString
    Dim filterBox As RadDatePicker = CType((CType(e.Item, GridFilteringItem))(column).Controls(0), RadDatePicker)
    Dim filterDate As Date? = filterBox.DateInput.DbSelectedDate
    Dim dbDate As String

    If filterDate IsNot Nothing AndAlso filterFunction <> "NoFilter" AndAlso IsDate(filterDate) Then
        Dim filterItem As GridFilteringItem = CType(e.Item, GridFilteringItem)
        Dim dateColumn As GridDateTimeColumn = CType(e.Item.OwnerTableView.GetColumnSafe(column), GridDateTimeColumn)
        Dim isTimeIndipendent As Boolean = dateColumn.EnableTimeIndependentFiltering

        dbDate = DateToDb(filterDate, isTimeIndipendent)

        Dim newFilter As String

        Select Case filterFunction
            Case "LessThan"
                newFilter = "([" & column & "] < '" & dbDate & "')"
                dateColumn.CurrentFilterFunction = GridKnownFunction.LessThan
            Case "GreaterThan"
                newFilter = "([" & column & "] > '" & dbDate & "')"
                dateColumn.CurrentFilterFunction = GridKnownFunction.GreaterThan
            Case "EqualTo"
                newFilter = "([" & column & "] = '" & dbDate & "')"
                dateColumn.CurrentFilterFunction = GridKnownFunction.EqualTo
            Case "NotEqualTo"
                newFilter = "([" & column & "] <> '" & dbDate & "')"
                dateColumn.CurrentFilterFunction = GridKnownFunction.NotEqualTo
            Case "GreaterThanOrEqualTo"
                newFilter = "([" & column & "] >= '" & dbDate & "')"
                dateColumn.CurrentFilterFunction = GridKnownFunction.GreaterThanOrEqualTo
            Case "LessThanOrEqualTo"
                newFilter = "([" & column & "] <= '" & dbDate & "')"
                dateColumn.CurrentFilterFunction = GridKnownFunction.LessThanOrEqualTo
            Case "IsNull"
                newFilter = "([" & column & "] IS NULL)"
                dateColumn.CurrentFilterFunction = GridKnownFunction.IsNull
            Case "NotIsNull"
                newFilter = "(NOT ([" & column & "] IS NULL))"
                dateColumn.CurrentFilterFunction = GridKnownFunction.NotIsNull
        End Select

        If Not String.IsNullOrWhiteSpace(newFilter) Then
            e.Canceled = True

            If grid.MasterTableView.FilterExpression = "" Then
                grid.MasterTableView.FilterExpression = newFilter
            Else
                grid.MasterTableView.FilterExpression = "((" & grid.MasterTableView.FilterExpression & ") AND (" & newFilter & "))"
            End If

            grid.Rebind()
        End If
    End If
End Function

Private Function DateToDb(originalDate As Date?, isTimeIndipendent As Boolean) As String
    Dim dbDate As String

    If Not String.IsNullOrWhiteSpace(originalDate) AndAlso IsDate(originalDate) Then
        dbDate = Year(originalDate).ToString & DoubleNum(Month(originalDate).ToString) & DoubleNum(Day(originalDate).ToString)
        If Not isTimeIndipendent Then
            dbDate &= " " & DoubleNum(Hour(originalDate).ToString) & ":" & DoubleNum(Minute(originalDate).ToString) & ":" & DoubleNum(Second(originalDate).ToString)
        End If
    End If

    Return dbDate
End Function

Private Function DoubleNum(number As String) As String
    If Not String.IsNullOrWhiteSpace(number) Then
        number = Right("00" & number, 2)
    End If

    Return number
End Function

EDIT: I further fixed the  Select Case filterFunction filtering to comply with the EnableTimeIndependentFiltering setting of GridDateTimeColumn. Also I didn't use SQL Server native DateDiff functions to keep this as much db indipendend as possible

Select Case filterFunction
    Case "LessThan"
        newFilter = "([" & column & "] < '" & dbDate & "')"
        dateColumn.CurrentFilterFunction = GridKnownFunction.LessThan
    Case "GreaterThan"
        If Len(dbDate) = 8 Then
            newFilter = "([" & column & "] > '" & dbDate & " 23:59:59')"
        Else
            newFilter = "([" & column & "] > '" & dbDate & "')"
        End If
        dateColumn.CurrentFilterFunction = GridKnownFunction.GreaterThan
    Case "EqualTo"
        If Len(dbDate) = 8 Then
            newFilter = "([" & column & "] > '" & dbDate & " 00:00:00' OR [" & column & "] < '" & dbDate & " 23:59:59')"
        Else
            newFilter = "([" & column & "] = '" & dbDate & "')"
        End If
        dateColumn.CurrentFilterFunction = GridKnownFunction.EqualTo
    Case "NotEqualTo"
        If Len(dbDate) = 8 Then
            newFilter = "([" & column & "] < '" & dbDate & " 00:00:00' OR [" & column & "] > '" & dbDate & " 23:59:59')"
        Else
            newFilter = "([" & column & "] <> '" & dbDate & "')"
        End If
        dateColumn.CurrentFilterFunction = GridKnownFunction.NotEqualTo
    Case "GreaterThanOrEqualTo"
        newFilter = "([" & column & "] >= '" & dbDate & "')"
        dateColumn.CurrentFilterFunction = GridKnownFunction.GreaterThanOrEqualTo
    Case "LessThanOrEqualTo"
        If Len(dbDate) = 8 Then
            newFilter = "([" & column & "] <= '" & dbDate & " 23:59:59')"
        Else
            newFilter = "([" & column & "] <= '" & dbDate & "')"
        End If
        dateColumn.CurrentFilterFunction = GridKnownFunction.LessThanOrEqualTo
    Case "IsNull"
        newFilter = "([" & column & "] IS NULL)"
        dateColumn.CurrentFilterFunction = GridKnownFunction.IsNull
    Case "NotIsNull"
        newFilter = "(NOT ([" & column & "] IS NULL))"
        dateColumn.CurrentFilterFunction = GridKnownFunction.NotIsNull
End Select

Please I need a bit of help, I'm sure this will turn out to be a great implementation to easily handle huge recordset with RadGrid and all of its (multi)filtering/paging/(multi)sorting functionalities.

One solution that comes to my mind is to add the filter expression for the date columns in the viewstate (I disabled sessions at all) and to check on each item command if a filter expression is already present and eventually replace to "" in the complete MasterTableView.FilterExpression, before adding the new one (then updating the viewstate with the new one). 
But I wonder if there is some more "neat" way of handling this (the neater way would be proper native region indipendent yyyymmdd hh:mm SQL formatting :P )

EDIT: I tryed this last idea implementing the viewstate and it works good BUT a big problem arise when the ItemCommand is fired by another column (let's say a text column) in this case the FilterExpression for the date columns is again generated by the default grid implementation (so '12/07/2013,12:03:83'). I should hook somehow where the whole FilterExpression is generated reading values from each of the filter columns so that my implementation to normalize dates is always fired even when filtering is triggered by another column.
How could I accomplish this? Or any other cleaner way of achieving what I'm trying to?

[Massimiliano]

I solved it in a much cleaner way in the NeedDataSource event exploiting the EvaluateFilterExpression() function, but I'm too tired to post it now. I'll post tomorrow. Any suggestions on polishing the code are welcome since I'm really a newbie both to OOP, .NET and to Telerik Controls.
When this will be finished and (enough) tested I will polish it and post in the code library as a server side paging/filtering/ordering implementation of RadGrid (if you think this can be useful of course)

[Massimiliano]

Ok this is the revised "filtering section" in the NeedDataSource event (wich you can find above).
If there is a neater way of accessing the information needed in this code snippet please jump in and suggest, you are welcome!

' Set filtering options

If Not String.IsNullOrWhiteSpace(viewFilter) Then

    ' Adjust filter values based on data type (or column type)

    For Each filterColumn As Telerik.Web.UI.GridColumn In view.Columns

        ' Adjust date values

        If Not String.IsNullOrWhiteSpace(filterColumn.EvaluateFilterExpression()) Then

            Select Case filterColumn.ColumnType.ToString

                Case "GridDateTimeColumn"

                    Dim dateColumn As GridDateTimeColumn = CType(filterColumn, GridDateTimeColumn)

                    viewFilter = Replace(viewFilter, filterColumn.EvaluateFilterExpression(), FilterDateToDb(dateColumn, grid))

            End Select

        End If

    Next

 

    ' Adjust boolean values

    viewFilter = Replace(viewFilter, " = True", " = 'True'")

    viewFilter = Replace(viewFilter, " = False", " = 'False'")

 

    query.SqlAdditionalSearch = viewFilter

End If

And the FilterDateToDb function is a shared function where the date filter expression are recreate compliant to SQL format and culture indipendent:

Private Shared Function FilterDateToDb(dateColumn As GridDateTimeColumn, grid As Telerik.Web.UI.RadGrid) As String
    Dim isTimeIndipendent As Boolean = dateColumn.EnableTimeIndependentFiltering
    Dim filterFunction As String = dateColumn.CurrentFilterFunction.ToString
    Dim filterItem As GridFilteringItem = CType(grid.MasterTableView.GetItems(GridItemType.FilteringItem)(0), GridFilteringItem)
    Dim filterBox As RadDatePicker = CType(filterItem(dateColumn.UniqueName).Controls(0), RadDatePicker)
    Dim filterDate As Date? = filterBox.DateInput.DbSelectedDate
    Dim dataField As String = dateColumn.DataField
    Dim dbDate As String
    Dim newFilter As String = String.Empty

    If filterDate IsNot Nothing AndAlso IsDate(filterDate) Then
        dbDate = DateToDb(filterDate, isTimeIndipendent)

        Select Case filterFunction
            Case "LessThan"
                newFilter = "[" & dataField & "] < '" & dbDate & "'"
                dateColumn.CurrentFilterFunction = GridKnownFunction.LessThan
            Case "GreaterThan"
                If Len(dbDate) = 8 Then
                    newFilter = "[" & dataField & "] > '" & dbDate & " 23:59:59'"
                Else
                    newFilter = "[" & dataField & "] > '" & dbDate & "'"
                End If
                dateColumn.CurrentFilterFunction = GridKnownFunction.GreaterThan
            Case "EqualTo"
                If Len(dbDate) = 8 Then
                    newFilter = "[" & dataField & "] > '" & dbDate & " 00:00:00' OR [" & dataField & "] < '" & dbDate & " 23:59:59'"
                Else
                    newFilter = "[" & dataField & "] = '" & dbDate & "'"
                End If
                dateColumn.CurrentFilterFunction = GridKnownFunction.EqualTo
            Case "NotEqualTo"
                If Len(dbDate) = 8 Then
                    newFilter = "[" & dataField & "] < '" & dbDate & " 00:00:00' OR [" & dataField & "] > '" & dbDate & " 23:59:59'"
                Else
                    newFilter = "[" & dataField & "] <> '" & dbDate & "'"
                End If
                dateColumn.CurrentFilterFunction = GridKnownFunction.NotEqualTo
            Case "GreaterThanOrEqualTo"
                newFilter = "[" & dataField & "] >= '" & dbDate & "'"
                dateColumn.CurrentFilterFunction = GridKnownFunction.GreaterThanOrEqualTo
            Case "LessThanOrEqualTo"
                If Len(dbDate) = 8 Then
                    newFilter = "[" & dataField & "] <= '" & dbDate & " 23:59:59'"
                Else
                    newFilter = "[" & dataField & "] <= '" & dbDate & "'"
                End If
                dateColumn.CurrentFilterFunction = GridKnownFunction.LessThanOrEqualTo
            Case "IsNull"
                newFilter = "[" & dataField & "] IS NULL"
                dateColumn.CurrentFilterFunction = GridKnownFunction.IsNull
            Case "NotIsNull"
                newFilter = "NOT ([" & dataField & "] IS NULL)"
                dateColumn.CurrentFilterFunction = GridKnownFunction.NotIsNull
        End Select
    End If

    Return newFilter
End Function

Public Shared Function DateToDb(originalDate As Date?, isTimeIndipendent As Boolean) As String
    Dim dbDate As String

    If Not String.IsNullOrWhiteSpace(originalDate) AndAlso IsDate(originalDate) Then
        dbDate = Year(originalDate).ToString & CoreHelpers.DoubleNum(Month(originalDate).ToString) & CoreHelpers.DoubleNum(Day(originalDate).ToString)
        If Not isTimeIndipendent Then
            dbDate &= " " & CoreHelpers.DoubleNum(Hour(originalDate).ToString) & ":" & CoreHelpers.DoubleNum(Minute(originalDate).ToString) & ":" & CoreHelpers.DoubleNum(Second(originalDate).ToString)
        End If
    End If

    Return dbDate
End Function

The DoubleNum function just takes care of transforming any single number day or month in a "0" preceded one

Public Shared Function DoubleNum(number As String) As String
    If Not String.IsNullOrWhiteSpace(number) Then
        number = Right("00" & number, 2)
    End If

    Return number
End Function

Any comment on this last snippets of code are welcome, most of all if you have suggestion to clean it a bit.

[Jon]

Massimiliano, thanks for the code.

You may want to use ISO 8601 standard to specify the date string. It is an international standard which many databases should support.  Format is as follows:

YYYY-MM-DDThh:mm:ss[.mmm] or YYYYMMDDThh:mm:ss[.mmm]

Time is in 24-hour format, and is required. More info at http://technet.microsoft.com/en-us/library/ms187819%28v=sql.100%29.aspx.

[Tonino]

Hi Massimiliano

Does your code work when sorting on grid rows is enabled? I've noticed, that the demo found here (http://demos.telerik.com/aspnet-ajax/grid/examples/programming/custompaging/defaultcs.aspx) retrieves all the data as soon as sorting is requested.

Another thing: How are you manipulation the given sql statement in order to retrieve just the data needed for the actual page?

I'm implemented some splitting and merging of the sql statement for the paging. But the code just works fine as long as just one table is involved (no joins).

Basically in the NeedDataSource-Event I split the given sql statement into parts and then I call GetPagedData():

Private Function GetPagedData() As DataTable
   Dim dt As New DataTable

   Dim sqlBuilder As New StringBuilder()
   sqlBuilder.AppendLine("SET ROWCOUNT @maximumRows")
   sqlBuilder.AppendLine(";WITH OrderedRecords AS")
   sqlBuilder.AppendLine("(")

   Dim sqlOrderBy As String = If(_grd.MasterTableView.SortExpressions.Count > 0, _grd.MasterTableView.SortExpressions(0).ToString, _sqlSplitter.OrderBy)
   sqlBuilder.AppendFormat("SELECT {0}, ROW_NUMBER() OVER (ORDER BY {1}) AS RowNum FROM {2} ", _sqlSplitter.Select, sqlOrderBy, _sqlSplitter.From)
   sqlBuilder.AppendLine("WHERE " & _sqlSplitter.Where)
   If Not String.IsNullOrEmpty(_sqlSplitter.GroupBy) Then
      sqlBuilder.AppendLine(" GROUP BY " & _sqlSplitter.GroupBy)
   End If

   sqlBuilder.AppendLine(")")

   sqlBuilder.AppendLine(" SELECT * FROM OrderedRecords Where RowNum > @startRowIndex")

   Using connection As New SqlConnection(_connectionString)
      Using cmd As New SqlCommand(sqlBuilder.ToString(), connection)
         AddParametersToCommand(cmd)
         cmd.Parameters.AddWithValue("@startRowIndex", _startRowIndex)
         cmd.Parameters.AddWithValue("@maximumRows", _maximumRows)

         Dim adapter As New SqlDataAdapter(cmd)
         adapter.Fill(dt)
      End Using
   End Using

   Return dt

End Function

Regards,
Tonino.

[Massimiliano]

Thank you both for your feedback... I've been back from holydays just yestarday (been away 30 days more or less). Please allow me 3-4 days to reply to this topic and all the others where I'm involved. Thank you for your patience.

[Massimiliano]

@Jon
Thanks for pointing it out! Yes the version posted above is a "first" version, the revised one eventualy is as follows

Public Shared Function DateToDb(originalDate As Date?, isTimeIndipendent As Boolean) As String
    Dim dbDate As String

    If Not String.IsNullOrWhiteSpace(originalDate) AndAlso IsDate(originalDate) Then
        dbDate = Year(originalDate).ToString & CoreHelpers.DoubleNum(Month(originalDate).ToString) & CoreHelpers.DoubleNum(Day(originalDate).ToString)
        If Not isTimeIndipendent Then
            dbDate &= "T" & CoreHelpers.DoubleNum(Hour(originalDate).ToString) & ":" & CoreHelpers.DoubleNum(Minute(originalDate).ToString) & ":" & CoreHelpers.DoubleNum(Second(originalDate).ToString)
        End If
    End If

    Return dbDate
End Function

This is the function that perpares the date in the ISO format.
PS. The CoreHelpers.DoubleNum function just check if a month or day is a single number (ex. 3, 7, 8) adds a leading zero (ex. 03, 07, 08).

@Tonino. Yes the code works great (in tests so far with 4 millions records) for sorting, paging and filtering. Grouping works but of course happens only on the page you are viewing, if you want real grouping you have to bypass this whole stuff and retrieve all records and let the grid control do the work.
If you look at the code you will see I'm not applying any manipulation to the SQL statement other than transforming the dates, I just passe the FilterExpression value (wich is handled by the grid control) to my MicroORM to add it to a simple "SELECT * FROM". I'm using a MicroORM as a support (PetaPoco in my case) for DB access and it takes care of the paging at DB side. Even for this reason I'm leveraging Views in my SQL Server DB to simplify requests and I have DTOs generated from T4 templates in automatic for all my views.
So I can't help you about the "join" thing but if the only problem that "blocks" you somehow is this one, consider having Views to help you, they are really a cool way to simplify your work (and not only that!)