Please help with RadGrid paging

[Robert]

I have a RadGrid that populates its data via a Subprocedure, which calls a SQL Stored Procedure to bind the data.

Private Sub PopulateMyData()
    ' initialize variables
    Dim sqlConn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("SQLConn2010").ToString)
    Dim sqlCmd As New SqlCommand("", sqlConn)

    ' open sql connection
    sqlConn.Open()

    With sqlCmd
        .CommandType = CommandType.StoredProcedure

        If ThisVariable.Length > 0 Then
            ' passed array search
            .CommandText = "prRunThisProcedure"
            .Parameters.AddWithValue("@VariableList", ThisVariable)
        Else
            ' standard search
            .CommandText = "prRunThatProcedureInstead"
            .Parameters.AddWithValue("@someVariable", NullString(SomeParameter))
            .Parameters.AddWithValue("@anotherVariable", NullString(AnotherParameter))
        End If
    End With

    ' execute query and read into sql data adapter
    Dim daTestObject As New SqlDataAdapter(sqlCmd)

    ' fill datatable with results from sql adapter
    Dim dtTestObject As New DataTable()
    dtTestObject.Columns.Add("Name", Type.GetType("System.String"))
    dtTestObject.Columns.Add("ReportInfoID", Type.GetType("System.String"))
    daTestObject.Fill(dtTestObject)

    ' retrieve rowcount
    Dim RowCount As Integer = dtTestObject.Rows.Count

    ' set datasource and bind if records were returned from stored procedure
    If (RowCount > 0) Then
        ' bind datasource
        rgTestGrid.DataSource = dtTestObject
        rgTestGrid.DataBind()
    End If
End Sub

I want to enable paging on this RadGrid but for some reason as soon as I attempt to move to the next page it seems to post back and do nothing.

<telerik:RadGrid ID="rgTestGrid" runat="server" GridLines="None" Width="700px" AllowPaging="true" PageSize="25" EnableViewState="true">
  <MasterTableView Caption="This is the data" CommandItemDisplay="Top" CommandItemSettings-ShowAddNewRecordButton="false" DataKeyNames="ReportInfoID" AllowPaging="true" NoDetailRecordsText="Nothing Found.">
    <PagerStyle Mode="NumericPages"  />
    <CommandItemSettings ShowExportToExcelButton="true" ShowExportToPdfButton="true" />
    <Columns>
    <telerik:GridTemplateColumn>
        <ItemTemplate>
        <asp:Label ID="lblRowCount" runat="server" Width="30px" />
        </ItemTemplate>
        <HeaderStyle Width="30px" />
    </telerik:GridTemplateColumn>
    <telerik:GridButtonColumn UniqueName="ReportURL" DataTextField="Name" CommandName="Select"></telerik:GridButtonColumn>
    </Columns>
  </MasterTableView>
</telerik:RadGrid>

If I return to my initial search page and essentially force it to rerun the Sub that calls the SQL Stored Proc (which forces a databind) then I can see it has the correct page selected, but this is not practical at all.

I was under the impression the Radgrid could query the data only once and then preserve the information in the RadGrid for use in offline paging?

I do not want to rebind the data each time they page through the Data-set since it never changes (it is statistical data nothing is input), and I do not understand from the examples I've read why I need to specify the paging information using the NeedDataSource since the data never changes, and I do not want it requerying the database (It already has grabbed all the records once on the initial page-load)

I am not defining a DataSource in the markup since I am doing that programmatically during the Subprocedure that references the SQL Stored Procedure (Which takes many parameters that need to be altered before used as parameters, hence why I have a Subprocedure to populate the initial Grid)

I have stripped out some of my excess code and tried  to put some illustration above in case it helps.  I would basically like this to function all client side so after the server intiially populates the RadGrid (which works fine), that further paging requests will simply page through that populated RadGrid and not require a post-back or rebind.

Hopefully there is something I am missing here that can be explained.

Thanks in advance

[Jason]

Create a NeedDataSource event and call PopulateMyData in it, I would remove the RowCount > 0 logic and replace with just rgTestGrid.DataSource = dtTestObject.  At a minimum you will have to remove the DataBind().  You do not need to call PopulateMyData in the OnInit/OnLoad, the OnNeedDataSource event will get fired.

[Robert]

Hi Jason,

Thanks for the reply.

If I populate the data source in the NeedDataSource event, won't that re-query the database every single time I make a paging request?  This is exactly what I was wanting to avoid as in my original post.  The Stored procedure that initially populates the RadGrid is using parameters that are no longer stored after the page is loaded, so I am trying to avoid having to respect/maintain their states which it seems I would have to do if the NeedDataSource event fired every time I try to utilize the RadGrid paging feature.  Or am I missing something here?  I want to fill the RadGrid with data, and page through it "without" having to re-query the SQL database each time, especially since I can see it has fully populated the RadGrid with all the results.

When I attempt the solution you recommended my page does not operate correctly, due to the fact it tries to perform the databinding upon page load. (before the parameters for the stored procedure have even been defined/specified by the user) 

I have a RadMultiPage setup so these data population events normally do not fire until 'after' the user has specified search criteria and submitted the form.  It then moves to another RadPageView to show result data, and prepares another RadPageView with a different set of data.  All of this is based on the parameters from the initial request.  I simply want the data-sets that have been populated to be paginated without the need to re-query the database.  I currently have everything working except this pagination, which has been a headache.

Any insight is appreciated

[Jason]

Yes you would have to hit the database every time.  I believe the RadGrid dumps any rows not rendered otherwise large grids would bloat your page even if you were only display 50 rows.  If you want paging you need some type of persistence, you could serialize your DataTable to XML and store it on the server harddrive in App_Data, put it in a Session variable or in the ViewState and bind your grid to that.  I can't think of any other way around it.

[Robert]

Hi Jason -

That really seems inefficient to run my stored procedure each time the user wants to make a paging request for the grid.  Essentially what will happen there is a return of a giant set of data, only to have the RadGrid throw out 98% of it at a time and present the other 2%? 

What does the RadGrid do with the 5000 total records returned by the stored procedure when paging is enabled, just throw them out and ask for the same 5000 each time I hit the next button just so it can keep track of which 25 it displayed?  Surely this must not be?  I got the impression it would perform the same query I had it set to do with paging off, and once it populated the RadGrid it would then keep the state persisted so that paging requests could all be handled via AJAX and not post-back to the page until I manually bind the data source again. (like when the user picks new search parameters and an event is fired this way)

I am fine with querying for this data once and then persisting the RadGrid with the ViewState. I have the Viewstate enabled to persist but it seems to do nothing.  Do I need to take additional steps in this NeedDataSource event with ViewState enabled in order for it to persist?

I appreciate all the help and it seems things are slowly moving in the right direction.  I have it paging now using the method you described but I am very concerned with how much extraneous data is being passed around here just for the sake of paging. (Even if RadTools is just throwing it away).  Do I need to be rewriting all my stored procedures in a special way to accomodate this?  I think that defeats the purpose of this process (which I thought was to be not requiring a bunch of changes to the stored procedures in order to support paging internally) but perhaps I am just misunderstanding how it should be.

Thanks again

[Robert]

Can anyone from Telerik please shed some light on how this operates based on the above questions, such as how the RadGrid is handling the extra records on every paging request?  I hate to think on a record-set with 5000 records it simply throws out the majority on each paging request as this can very quickly bog down a database server with excessive calls and exchange of data.

[Tsvetina]

Hello John,

Indeed in the default case RadGrid takes all items from the datasource that is passed to it and uses only the ones requested for the current page. However, it is up to you to decide what datasource you pass to the grid. As you said, you can store the records in the Session or ViewState and assign them from there instead of querying the database each time. Furthermore, you can implement custom paging and page the data as per your requirements instead of using the default paging mechanism of the grid.

You can see an example of binding the grid to a DataSet kept in the ViewState in this online demo's code behind:
http://demos.telerik.com/aspnet-ajax/grid/examples/dataediting/extractvalues/defaultcs.aspx
And you can read more and see a demonstration of custom paging at the following locations:
http://www.telerik.com/help/aspnet-ajax/grid-custom-paging.html
http://demos.telerik.com/aspnet-ajax/grid/examples/programming/custompaging/defaultcs.aspx

All the best,
Tsvetina
the Telerik team