Gridview Paging with Stored Procedure

Question

Hi I am having an issue with getting my gridview paging. I've already read a bunch of articles about passing in page numbers and records per page to my stored procedure but the problem is my stored procedure is completely dynamic, uses some temp tables that join on each other so I've tried what was suggested. I'm just not sure about how else to do it. Shouldn't I be able to just use the default paging for a SQLDataSource.

<asp:GridView ID="gvFileResults" DataSourceID="SqlDataSource2" runat="server" AutoGenerateColumns="False" EmptyDataText="Nothing Found" AllowSorting="True" AllowPaging="True" PageSize="50" OnPageIndexChanging="gvFileResults_PageIndexChanging">
    <Columns>
        <asp:HyperLinkField DataTextField="DisplayName" HeaderText="File Name" DataNavigateUrlFields="Drawer_Id, ParentFolders_Id, FolderId, Id" Target="_blank" SortExpression="DisplayName" DataNavigateUrlFormatString="~/Cabinet/FolderView.aspx?did={0}&amp;fid={1}&amp;sid={2}&amp;fileid={3}">
            <ControlStyle CssClass="gridlink" />
        </asp:HyperLinkField>
        <asp:BoundField DataField="FileType" HeaderText="File Type" ItemStyle-Width="100px" SortExpression="FileType" >
        <ItemStyle Width="100px" />
        </asp:BoundField>
        <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
        <asp:TemplateField HeaderText="Location" ItemStyle-Width="200px">
            <ItemTemplate>
                <div class="drawername"><%#Eval("DrawerName")%></div>
                <div class="foldername"><%#Eval("ParentFolderName")%></div>
                <div class="subfoldername"><%#Eval("FolderName")%></div>
            </ItemTemplate>
            <ItemStyle Width="200px" />
        </asp:TemplateField>
    </Columns>
</asp:GridView>

And I have tried using stored procedure in SQLDataSource from code behind which returns results but I can't get the paging to work. And I have tried using a SQLDataSource directly on the page with parameters which I can't get to return any results

Here is the code for the datasource on the page that doesn't ever return results

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:FileCabConnection %>" SelectCommand="spSearchFiles" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:ControlParameter ControlID="tbFileName" DefaultValue="''" Name="FileName" PropertyName="Text" Type="String" />
        <asp:ControlParameter ControlID="tbFileDescription" Name="FileDescription" PropertyName="Text" Type="String" />
        <asp:ControlParameter ControlID="ddlFileType" Name="FileTypeId" PropertyName="SelectedValue" Type="Int32" />
        <asp:ControlParameter ControlID="ddlDrawer" Name="DrawerId" PropertyName="SelectedValue" Type="Int32" />
        <asp:ControlParameter ControlID="ddlFolder" Name="FolderId" PropertyName="SelectedValue" Type="Int32" />
        <asp:ControlParameter ControlID="ddlFolder" Name="SubFolderId" PropertyName="SelectedValue" Type="Int32" />
        <asp:SessionParameter Name="UserId" SessionField="UserId" Type="Int32" />
        <asp:SessionParameter Name="SecurityLevelId" SessionField="SecurityLevelId" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

And here is the code behind for the datasource that returns results but paging doesn't work

SqlDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings["FileCabConnection"].ConnectionString;
SqlDataSource1.SelectCommand = "spSearchFiles";
SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
SqlDataSource1.SelectParameters.Add(new Parameter("FileName", DbType.String, tbFileName.Text));
SqlDataSource1.SelectParameters.Add(new Parameter("UserId", DbType.Int32, user.Id.ToString())); 

if (tbFileDescription.Text != "")
{
    SqlDataSource1.SelectParameters.Add(new Parameter("FileDescription", DbType.String, tbFileDescription.Text));
}

if (Convert.ToInt32(ddlFileType.SelectedValue) > 0)
{
    SqlDataSource1.SelectParameters.Add(new Parameter("FileTypeId", DbType.Int32, ddlFileType.SelectedValue));
}

if (Convert.ToInt32(ddlDrawer.SelectedValue) > 0 && Convert.ToInt32(ddlFolder.SelectedValue) == 0 && Convert.ToInt32(ddlSubfolder.SelectedValue) == 0)
{
    SqlDataSource1.SelectParameters.Add(new Parameter("DrawerId", DbType.Int32, ddlDrawer.SelectedValue));
}

if (Convert.ToInt32(ddlFolder.SelectedValue) > 0 && Convert.ToInt32(ddlSubfolder.SelectedValue) == 0)
{
    SqlDataSource1.SelectParameters.Add(new Parameter("FolderId", DbType.Int32, ddlFolder.SelectedValue));
}

if (Convert.ToInt32(ddlSubfolder.SelectedValue) > 0)
{
    SqlDataSource1.SelectParameters.Add(new Parameter("SubFolderId", DbType.Int32, ddlSubfolder.SelectedValue));
}

gvFileResults.DataSource = SqlDataSource1;
gvFileResults.DataBind();

Answer 1

Add bellow two properties to Grid, that enable pagination within the grid. AllowPaging="true" PageSize="5"

Include bellow two parameters to SQL Data source, it will pass the current row index and page size.

Add both the page size and page index parameters to SP and add following code to end of yours select statement within the SP:

ORDER BY [ColumnName] OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;