Paging, sorting, filtering at DB level the datasource

13 posts, 0 answers
  1. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 03 Jul 2013 Link to this post

    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
  2. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 04 Jul 2013 Link to this post

    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.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 05 Jul 2013 Link to this post

    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

  5. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 05 Jul 2013 Link to this post

    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


  6. Tsvetoslav
    Admin
    Tsvetoslav avatar
    1823 posts

    Posted 08 Jul 2013 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  7. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 08 Jul 2013 Link to this post

    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.
  8. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 12 Jul 2013 Link to this post

    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?
  9. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 12 Jul 2013 Link to this post

    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)
  10. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 13 Jul 2013 Link to this post

    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.
  11. Jon
    Jon avatar
    4 posts
    Member since:
    Jul 2013

    Posted 12 Aug 2013 Link to this post

    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.
  12. Tonino
    Tonino avatar
    107 posts
    Member since:
    Nov 2007

    Posted 26 Aug 2013 Link to this post

    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.

  13. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 01 Sep 2013 Link to this post

    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.
  14. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 13 Sep 2013 Link to this post

    @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!)
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017