Custom paging in Gridview With ObjectDataSource

Many times we use paging with GridView with AllowPaging = "True" and binding the GridView with some ObjectDataSource. It actually fetches all row from the database and depending on the no of records it fetched, it generates the Pager section of the page and according to the current page index, it shows the set of record.
This approach is good for when you have limited no of record in the database. But there are many situations where there is thousands of rows in the database and you have to display only 25 records(PageSize) out of that thousands of records. So it is not good practice to fetch all record through object datasource to display only 25 records.
Here we should use custom paging to fetch only the 25 rows starting from the startindex which is
CurrentPageIndex*PageSize (As Index is 0 Based).
Calculated automatically by objectdatasource.
Here is the change you have to do

• Set EnablePaging="True" for ObjectDataSource


• Set The SelectMethod Property


• Set value for following Properties of ObjecDataSource StartRowIndexParameterName, SortParameterName, MaximumRowsParameterName
  These parameters needs to be present in the method attached for selecting the result for objectdatasource.


• Set SelectCountMethod to the name of method returning the total no of rows.

Here is my ObjectDataSourceCode

<asp:objectdatasource id="objDS" runat="server" enablepaging="True" selectmethod="SearchResult" typename="PRP.BusinessLogic.TpilTblNsefaoscripmaster" startrowindexparametername="paramStartRowIndex" sortparametername="paramSortExpression" maximumrowsparametername="paramPageSize" selectcountmethod="GetNoOfRows">
          <selectparameters>
              <asp:parameter name="paramSymbol" type="String">
                 <asp:parameter direction="Output" name="paramTotalNoOfRows" type="Int32">
                 </asp:parameter>
             </asp:parameter>
         </selectparameters>
</asp:objectdatasource>

The SearchResult & GetNoOfRows Function

public DataTable SearchResult(string paramSymbol,
       int paramStartRowIndex, int paramPageSize, string paramSortExpression, out int paramTotalNoOfRows)
{
  TpilTblNsefaoscripmasterHelper.SearchResult(paramSymbol, paramStartRowIndex, paramStartRowIndex + paramPageSize, paramSortExpression, ref paramTotalNoOfRows);
  this.totalNoOfRows = paramTotalNoOfRows;
}
int totalNoOfRows = 0;
public int GetNoOfRows(string paramSymbol,
       int paramStartRowIndex, int paramPageSize, string paramSortExpression, out int paramTotalNoOfRows)
{
   paramTotalNoOfRows = totalNoOfRows;
   return totalNoOfRows;
}

The parameter list for both the functions are same but the second function retuns int no of rows. But i don't have used two function call for getting the total noofrows. Inside select function i have set the totalNoOfRows parameter which is accessed inside GetNoOfRows method to get the no of rows. This works as objectdatasource calls both the method on same object instance of the class and none of the methods and the class variable is static and the select methos is called first and then the row count method is called.
Now check the stored procedure used for this purpose

Create PROCEDURE [dbo].[USP_TPIL_tbl_NSEFAOScripSelectForSearch]
@Symbol varchar(50),
@StartIndex int = 0,
@EndIndex int =25,
@SortExpression varchar(50),
@TotalNoOfRows int OUTPUT
AS
SET NOCOUNT ON
Select * from
(
SELECT m.Instrument,m.Symbol,m.SymbolDescription,ExpiryDate,
Row_Number() OVER (Order By
     CASE WHEN @SortExpression='EXPIRYDATE' OR @SortExpression='EXPIRYDATE ASC' THEN m.EXPIRYDATE END DESC,
     CASE WHEN @SortExpression='EXPIRYDATE DESC' THEN m.EXPIRYDATE END ASC ,
     CASE WHEN @SortExpression='SYMBOL' OR @SortExpression='SYMBOL ASC' THEN m.SYMBOL END DESC,
     CASE WHEN @SortExpression='SYMBOL DESC' THEN m.SYMBOL END ASC
    ) as RowNumber 
FROM
[dbo].[TPILl_NSEFAOScripMaster] m
WHERE 
(m.[Symbol] LIKE @Symbol OR @Symbol = '')
)t
Where RowNumber>@StartIndex  AND RowNumber<=@EndIndex   --return SELECT @TotalNoOfRows = Count(*)  FROM   [dbo].[TPILl_NSEFAOScripMaster] m  WHERE    (m.[Symbol] LIKE @Symbol OR @Symbol = '') 

As i am using Sql Server 2005, i used ROW_Number() function. The value of output parameter "TotalNoOfRows " is set to the variable "totalNoOfRows" inside "SearchResult" function.
To know about how to get Row Number in Sql Server 2000 please check the bottom of this link.

Paging in Datalist and Repeater

Datalist and Repeater doesn't provide any paging by default like gridview or datagrid. So we avoid using datagrid/repeater as much as possible. But there comes situation where using datalist or repeater is easier to maintain the design for the page. Like some product is displayed like row by row with 4 producs in a single w. There its tough to achieve (but not impossible) this design output wit datagrid/gridview. So we choose datalist making its RepeatColumns="4" RepeatDirection="Horizontal".
But paging???
So i created a control named as COUSPaging (Cous is my project name). Which contains two link buttons as "Next" and "Previous" and a textbox for goto page as below



In my control i used a public event "PageIndexChanging" which is of type GridViewPageEventHandler, so that it sends the same parameter as GridView. The event is handled from the Parent page containg the DataList.
The Paging.aspx Page Code:
<table>
<tr>
<td>
<asp:LinkButton ID="linkPrevious" CssClass="button" runat="server" Text="Previous" CommandName="Previous" OnClick="linkPrevious_Click"></asp:LinkButton>
</td>
<td>
<asp:Literal ID="litPageCount" runat="server"></asp:Literal>
<span id="spanGotpPage" runat="server">Goto Page:
<asp:TextBox Width="15" ID="txtPageNo" CssClass="inputfld" runat="server" MaxLength="3"></asp:TextBox>
<asp:Button ID="btnGo" runat="server" Text="Go" CssClass="button" OnClick="btnGo_Click" />
</span>
</td>
<td>
<asp:LinkButton ID="linkNext" CssClass="button" runat="server" Text="Next" CommandName="Next" OnClick="linkNext_Click"></asp:LinkButton>
</td>
</tr>
</table>

.Cs Page Coding:

void Controls_Paging_PreRender(object sender, EventArgs e)
{
    litPageCount.Text = string.Format("{0} of {1} Pages  ", SelectedPageIndex + 1, TotalNoOfPages);
    if (SelectedPageIndex == TotalNoOfPages - 1)
    {
        linkNext.Enabled = false;
    }
    else
    {
        linkNext.Enabled = true;
    }
    if (SelectedPageIndex == 0)
    {
        linkPrevious.Enabled = false;
    }
    else
    {
        linkPrevious.Enabled = true;
    }
    txtPageNo.Text = (SelectedPageIndex + 1).ToString();
}
protected void btnGo_Click(object sender, EventArgs e)
    {
        int newPageIndex = 0;
        try
        {
            newPageIndex = Convert.ToInt32(txtPageNo.Text) - 1;
            if (newPageIndex > TotalNoOfPages - 1)
            {
                newPageIndex = TotalNoOfPages - 1;
            }
            if (newPageIndex < 0)
            {
                newPageIndex = 0;
            }
            SelectedPageIndex = newPageIndex;
            PageIndexChanging(btnGo, new GridViewPageEventArgs(newPageIndex));
        }
        catch { }
    }
public event GridViewPageEventHandler PageIndexChanging;
    public int PageSize
    {
        set
        {
            ViewState["PageSize"] = value;
        }
        get
        {
            if (ViewState["PageSize"] is int)
                return (int)ViewState["PageSize"];
            else return 0;
        }
    }

    public int CurrentPageIndex
    {
        set
        {
            ViewState["CurrentPageIndex"] = value;
        }
        private get
        {
            if (ViewState["CurrentPageIndex"] is int)
                return (int)ViewState["CurrentPageIndex"];
            else return 0;
        }
    }
    public int StartIndex
    {
        get
        {
            return SelectedPageIndex * PageSize;
        }
    }

    public int TotalNoOfPages
    {
        get
        {
            return (int)Math.Ceiling(TotalNoOfRows / (float)PageSize);
        }
    }
    public long TotalNoOfRows
    {
        set
        {
            ViewState["TotalNoOfRows"] = value;
        }
        get
        {
            if (ViewState["TotalNoOfRows"] is long)
                return (long)ViewState["TotalNoOfRows"];
            else return 0;
        }
    }
    public int SelectedPageIndex
    {
        set
        {
            ViewState["SelectedPageIndex"] = value;
        }
        get
        {
            if (ViewState["SelectedPageIndex"] is int)
                return (int)ViewState["SelectedPageIndex"];
            else return 0;
        }
    }
    protected void linkNext_Click(object sender, EventArgs e)
    {
        if (SelectedPageIndex < TotalNoOfPages)
        {
            SelectedPageIndex = SelectedPageIndex + 1;
            PageIndexChanging(sender, new GridViewPageEventArgs(SelectedPageIndex));
        }
    }
    protected void linkPrevious_Click(object sender, EventArgs e)
    {
        if (SelectedPageIndex > 0)
        {
            SelectedPageIndex = SelectedPageIndex - 1;
            PageIndexChanging(sender, new GridViewPageEventArgs(SelectedPageIndex));
        }
    }

The page where the control is used

Here the page size is initialized. From the cs page call the bind datalist function as

void bindDataList()
{
    long totalNoOfRows;
    ImageTblMasterTable dtImage = ImageTblMaster.SelectAll(-1, 1, pagingDL.StartIndex, pagingDL.PageSize, out totalNoOfRows);
    pagingDL.TotalNoOfRows = totalNoOfRows;
    dlImage.DataSource = dtImage;
    dlImage.DataBind();
}

Here i am using custom paging. So i am sending the page start index and page size which is converted to ennIndex before the call to SP. Here is code from SP

Select @TotalNoOfRows = Count(*) FROM [dbo].[Image_tbl_Master]

Select * from
(
SELECT ROW_NUMBER() OVER (ORDER BY ImageId) as RowNumber, m.* FROM [dbo].[Image_tbl_Master] as m
)t Where
RowNumber >= @StartIndex
AND
RowNumber <= @EndIndex As i am using SQL Server 2005 i get the facility of ROW_Number() but in SQL Server 2000 or before, there is no row number. So you have to change the query like Select * from ( Select (Select Count(*) from Image_tbl_Master where ImageId<=img.ImageId )as RowNumber, img.* from Image_tbl_Master as img )t Where RowNumber >= @StartIndex
AND
RowNumber <= @EndIndex
Here row number is calculated on the fly through a subquery. See the where clause "ImageId<=img.ImageId ", it is responsible for the sorting. As i was sorting with ImageId i have used it like that.