[Solved] Paging not working with advanced databinding, session use, custom filter

[Sam Straub]

Sorry, the subject is not right, the paging is working just not quickly.

Hello, does anyone have suggestions on a better way to do the following?  I'm not sure if I'm using the most efficient approach.

Here's my datagrid:

<div style="background: url(/images/gridbg.jpg) no-repeat; width: 740px">
    <div style="float: right; margin: 10px 10px 5px 0px; color: #2a6ca3; font-size: 11px;
        vertical-align: middle;">
        <table cellpadding="0" cellspacing="0">
            <tr valign="middle">
                <td>
                    <asp:Label ID="lblFilter" runat="server" Text="Filter:"></asp:Label></td>
                <td style="padding-left: 5px;">
                    <telerik:RadComboBox ID="rcbFieldName" Skin="Telerik" runat="server">
                        <Items>
                            <telerik:RadComboBoxItem Text="Branch" Value="Branch" runat="server" />
                            <telerik:RadComboBoxItem Text="Employee Name" Value="Employee Name" runat="server" />
                        </Items>
                        <CollapseAnimation Duration="200" Type="OutQuint" />
                    </telerik:RadComboBox>
                </td>
                <td style="padding-left: 5px;">
                    <asp:TextBox ID="txtSearch" runat="server" Style="vertical-align: middle"></asp:TextBox></td>
                <td style="padding-left: 5px;">
                    <asp:ImageButton ID="btnSearch" runat="server" ImageUrl="/images/FilterGo.gif" /></td>
                <td style="padding-left: 5px;">
                    <asp:ImageButton ID="btnShowAll" runat="server" ImageUrl="/images/FilterCancel.gif"
                        AlternateText="Cancel Filter / Show All" /></td>
            </tr>
        </table>
    </div>
    <div class="buttonTop" style="margin-top: 10px; margin-left: 10px;">
        <table cellpadding="0" cellspacing="0">
            <tr valign="middle">
                <td>
                    <asp:ImageButton runat="server" ID="ImageExport" AlternateText="Export to Excel"
                        ImageUrl="/images/FileManager/Icons/xls.gif" />
                    <asp:ImageButton OnClientClick="CustomAction(); return false;" runat="server" ID="ImageButton1"
                        AlternateText="Export to Excel" ImageUrl="/images/FileManager/Icons/xls.gif"
                        Visible="false" /></td>
                <td style="padding-left: 5px;">
                    Export to Excel</td>
            </tr>
        </table>
    </div>
    <div style="clear: both;">
    </div>
    <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server" DefaultLoadingPanelID="RadAjaxLoadingPanel1">
        <AjaxSettings>
            <telerik:AjaxSetting AjaxControlID="RadGrid1">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadGrid1" />
                </UpdatedControls>
            </telerik:AjaxSetting>
        </AjaxSettings>
    </telerik:RadAjaxManager>
    <telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server" Transparency="10">
        <img alt="Loading..." src='<%= RadAjaxLoadingPanel.GetWebResourceUrl(Page, "Telerik.Web.UI.Skins.Default.Ajax.loading.gif") %>'
            style="border: 0px;" />
    </telerik:RadAjaxLoadingPanel>
    <telerik:RadGrid ID="RadGrid1" runat="server" OnPreRender="RadGrid1_PreRender" AllowPaging="True"
        AllowSorting="true" AutoGenerateColumns="false" GridLines="None" Skin="Outlook">
        <MasterTableView DataKeyNames="CuId,BarcodeID">
            <RowIndicatorColumn>
                <HeaderStyle Width="20px" />
            </RowIndicatorColumn>
            <ExpandCollapseColumn>
                <HeaderStyle Width="20px" />
            </ExpandCollapseColumn>
            <NestedViewTemplate>
                <asp:Panel runat="server" ID="InnerContainer" CssClass="viewWrap" Visible="false">
                    <asp:Label ID="Label1" Font-Bold="true" Font-Italic="true" Text='<%# Eval("BarcodeID") %>'
                        Visible="false" runat="server" />
                    <telerik:RadGrid runat="server" ID="ResponseGrid" DataSourceID="SqlDataSource2" AllowSorting="true">
                        <MasterTableView ShowHeader="true" AutoGenerateColumns="False" AllowPaging="true"
                            DataKeyNames="BarcodeID" PageSize="5">
                            <Columns>
                                <telerik:GridBoundColumn SortExpression="BarcodeID" HeaderText="ID" HeaderButtonType="TextButton"
                                    DataField="BarcodeID" UniqueName="BarcodeID">
                                    <HeaderStyle />
                                </telerik:GridBoundColumn>
                                <telerik:GridBoundColumn SortExpression="MemberName" HeaderText="Member Name" HeaderButtonType="TextButton"
                                    DataField="MemberName" UniqueName="MemberName">
                                    <HeaderStyle />
                                </telerik:GridBoundColumn>
                                <telerik:GridBoundColumn SortExpression="EmailAddress" HeaderText="Email Address"
                                    HeaderButtonType="TextButton" DataField="EmailAddress" UniqueName="EmailAddress">
                                    <HeaderStyle />
                                </telerik:GridBoundColumn>
                                <telerik:GridBoundColumn SortExpression="EmployeeName" HeaderText="Employee Name"
                                    HeaderButtonType="TextButton" DataField="EmployeeName" UniqueName="EmployeeName">
                                    <HeaderStyle />
                                </telerik:GridBoundColumn>
                                <telerik:GridBoundColumn SortExpression="BranchName" UniqueName="BranchName" HeaderButtonType="TextButton"
                                    DataField="BranchName" HeaderText="Branch Name">
                                    <HeaderStyle />
                                </telerik:GridBoundColumn>
                            </Columns>
                        </MasterTableView>
                    </telerik:RadGrid>
                    <asp:SqlDataSource ID="SqlDataSource2" ConnectionString="<%$ ConnectionStrings:SiteSqlServer %>"
                        ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM CE_SurveyResponseCuId WHERE BarcodeID = @BarcodeID"
                        runat="server">
                        <SelectParameters>
                            <asp:ControlParameter ControlID="Label1" PropertyName="Text" Type="String" Name="BarcodeID" />
                        </SelectParameters>
                    </asp:SqlDataSource>
                </asp:Panel>
            </NestedViewTemplate>
            <Columns>
                <telerik:GridDateTimeColumn UniqueName="Receipt" HeaderText="Date<br>Received" DataField="Receipt"
                    DataFormatString="{0:MM/dd/yyyy}">
                    <HeaderStyle Width="100px" HorizontalAlign="center" />
                </telerik:GridDateTimeColumn>
                <telerik:GridDateTimeColumn UniqueName="TransDate" HeaderText="Transaction<br>Date"
                    DataField="TransDate" DataFormatString="{0:MM/dd/yyyy}">
                    <HeaderStyle Width="100px" HorizontalAlign="center" />
                </telerik:GridDateTimeColumn>
                <telerik:GridBoundColumn DataField="Question1" DataType="System.Double" HeaderText="Qu<br />1"
                    UniqueName="Question1">
                    <HeaderStyle HorizontalAlign="center" Width="30px" />
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Question2" DataType="System.Double" HeaderText="Qu<br />2"
                    SortExpression="Question2" UniqueName="Question2">
                    <HeaderStyle HorizontalAlign="center" Width="30px" />
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Question3" DataType="System.Double" HeaderText="Qu<br />3"
                    SortExpression="Question3" UniqueName="Question3">
                    <HeaderStyle HorizontalAlign="center" Width="30px" />
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Question4" DataType="System.Double" HeaderText="Qu<br />4"
                    SortExpression="Question4" UniqueName="Question4">
                    <HeaderStyle HorizontalAlign="center" Width="30px" />
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Question5" DataType="System.Double" HeaderText="Qu<br />5"
                    SortExpression="Question5" UniqueName="Question5">
                    <HeaderStyle HorizontalAlign="center" Width="30px" />
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Question6" DataType="System.Double" HeaderText="Qu<br />6"
                    SortExpression="Question6" UniqueName="Question6">
                    <HeaderStyle HorizontalAlign="center" Width="30px" />
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Question7" DataType="System.Double" HeaderText="Qu<br />7"
                    SortExpression="Question7" UniqueName="Question7">
                    <HeaderStyle HorizontalAlign="center" Width="30px" />
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Question8" DataType="System.Double" HeaderText="Qu<br />8"
                    SortExpression="Question8" UniqueName="Question8">
                    <HeaderStyle HorizontalAlign="center" Width="30px" />
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Question9" DataType="System.Double" HeaderText="Qu<br />9"
                    SortExpression="Question9" UniqueName="Question9">
                    <HeaderStyle HorizontalAlign="center" Width="30px" />
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Question10" DataType="System.Double" HeaderText="Qu<br />10"
                    SortExpression="Question10" UniqueName="Question10">
                    <HeaderStyle HorizontalAlign="center" Width="30px" />
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Comments" HeaderText="Comments" SortExpression="Comments"
                    UniqueName="Comments">
                    <HeaderStyle HorizontalAlign="center" />
                </telerik:GridBoundColumn>
            </Columns>
        </MasterTableView>
        <ClientSettings>
            <Scrolling AllowScroll="True" UseStaticHeaders="True" />
        </ClientSettings>
    </telerik:RadGrid>
</div>

Here's my code:

        Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnSearch.Click
            Dim searchtext As String = Trim(txtSearch.Text.ToString)
            Literal1.Text &= "searchtext=" & searchtext & "<br>"

            If Len(searchtext) > 0 Then
                Select Case Me.rcbFieldName.SelectedValue
                    Case "Branch"
                        Literal1.Text &= "branch selected<br>"
                        RadGrid1.DataSource = GetDataTable("SELECT * FROM [tablex] WHERE ([CuId] = '" & ViewState("CuId") & "') AND BranchName = '" & searchtext & "'")
                        Literal1.Text &= "SELECT * FROM [tablex] WHERE ([CuId] = '" & ViewState("CuId") & "') AND BranchName = '" & searchtext & "'<br>"
                        RadGrid1.Rebind()
                        Session("GridSource") = RadGrid1.DataSource
                    Case "Employee Name"
                        Literal1.Text &= "employee name selected<br>"
                        RadGrid1.DataSource = GetDataTable("SELECT * FROM [tablex] WHERE ([CuId] = '" & ViewState("CuId") & "') AND EmployeeName LIKE '%" & searchtext & "%'")
                        Literal1.Text &= "SELECT * FROM [tablex] WHERE ([CuId] = '" & ViewState("CuId") & "') AND EmployeeName LIKE '%" & searchtext & "%'<br>"
                        RadGrid1.Rebind()
                        Session("GridSource") = RadGrid1.DataSource
                End Select
            End If
        End Sub

        Protected Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource
            If Session("GridSource") Is Nothing Then
                RadGrid1.DataSource = GetDataTable("SELECT * FROM [tablex] WHERE ([CuId] = '" & ViewState("CuId") & "')")
                Session("GridSource") = RadGrid1.DataSource
            Else
                RadGrid1.DataSource = Session("GridSource")
            End If
        End Sub

       Public Function GetDataTable(ByVal query As String) As DataTable
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString
            Dim conn As SqlConnection = New SqlConnection(ConnString)
            Dim adapter As SqlDataAdapter = New SqlDataAdapter
            adapter.SelectCommand = New SqlCommand(query, conn)
            Dim table1 As New DataTable
            conn.Open()
            Try
                adapter.Fill(table1)
            Finally
                conn.Close()
            End Try
            Return table1
        End Function

[Veli]

Hello Sam,

Your code seems fine and your are properly binding your grid in NeedDataSource to the filtered or total set of data. Try binding your grid to some dummy data in the same scenario and test the paging performance.

Best wishes,
Veli
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.