Custome Paging

aspx page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="paging.aspx.cs" Inherits="paging" %>




   


   

   

       
       
       
           
               
                    <%# Eval("TESTID") %>.
                <%# Eval("TEST_TOPPIC") %>

           
       
       
       
           
           
           
           
       
   

   

cs file

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;

public partial class paging : System.Web.UI.Page
{
    string connStr = ConfigurationManager.AppSettings["ArtSQLConnStr"].ToString();
    int _startIndex = 0;
    int _thisPage = 1;
    int _pageSize = 1;
    int _totalNumberOfRows = 0;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (Request["start"] != null && Request["page"] != null)
            {
                int.TryParse(Request["start"].ToString(), out _startIndex);
                int.TryParse(Request["page"].ToString(), out _thisPage);
            }
            BindGridViewArticels();
        }
    }

    private void BindGridViewArticels()
    {
        DataTable dTable = new DataTable();
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            using (SqlCommand dCmd = new SqlCommand())
            {
                 _pageSize = Convert.ToInt32(ddlindex.SelectedValue);
                SqlParameter[] prms = new SqlParameter[4];
                prms[0] = new SqlParameter("@startRowIndex", SqlDbType.Int);
                prms[0].Value = _startIndex;
                prms[1] = new SqlParameter("@pageSize", SqlDbType.Int);
                prms[1].Value = _pageSize;
                prms[2] = new SqlParameter("@categoryParentID", SqlDbType.Int);
                prms[2].Value = 2;
                prms[3] = new SqlParameter("@totalCount", SqlDbType.Int);
                prms[3].Direction = ParameterDirection.Output;

                dCmd.CommandText = "LoadArticles";
                dCmd.CommandType = CommandType.StoredProcedure;
                dCmd.Parameters.AddRange(prms);
                dCmd.Connection = conn;
                using (SqlDataAdapter dAd = new SqlDataAdapter())
                {
                    dAd.SelectCommand = dCmd;
                    conn.Open();
                    dAd.Fill(dTable);
                    conn.Close();
                }
                _totalNumberOfRows = int.Parse(prms[3].Value.ToString());
                _totalNumberOfRows =Convert.ToInt32( Math.Ceiling(_totalNumberOfRows /Convert.ToDouble( ddlindex.SelectedValue)));
            }

        }
       
        Repeater1.DataSource = dTable;
        Repeater1.DataBind();
        litPaging.Text = GetPagingDone(_thisPage, _totalNumberOfRows, _pageSize, "paging.aspx", "");
    }

    private string GetPagingDone(int thisPageNo, int totalCount, int pageSize, string pageName, string extraQstringToAdd)
    {
        int pageno = 0;
        int loop =totalCount<10?totalCount:10;
        int i = 0;
        int remainder = totalCount % pageSize;
        StringBuilder strB = new StringBuilder("
Page: ", 500);

        if (thisPageNo > 6)
        {
            if (thisPageNo <= totalCount - 4)
            {
                i = thisPageNo - 6; loop = thisPageNo + 4;
            }
            else
            {
                i = totalCount-10; loop =totalCount  ;
            }
        }
        for (; i < loop; i++)
        {
            pageno = i + 1;
            if (pageno.Equals(thisPageNo))
                strB.Append(pageno + " | ");
            else
                if (i == loop - 1 && loop<=totalCount-1)
                    strB.Append("" + "..." + " ");
                else
                strB.Append("" + pageno + " | ");
        }
        return strB.ToString() + "";

    }
    protected void ddlindex_SelectedIndexChanged(object sender, EventArgs e)
    {
        BindGridViewArticels();
    }
}


Database file

/*
DECLARE @COUNT INT
EXEC LoadArticles 0,5,2,@COUNT OUTPUT
PRINT @COUNT
*/

ALTER PROC LoadArticles
(
    @startRowIndex int,
    @pageSize int,
    @categoryParentID INT,
    @totalCount int output
)
AS
    BEGIN
        SET NOCOUNT ON;
        SET @totalCount = 0
        SET @startRowIndex = @startRowIndex + 1
        BEGIN
            SELECT * FROM (
            Select STUDENT_TEST.*, ROW_NUMBER() OVER (ORDER BY TESTID DESC) as RowNum
            FROM STUDENT_TEST WHERE    STUDENT_ID = 2) as ArticleList
            WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
            ORDER BY TESTID DESC      
            SELECT @totalCount = Count(TESTID) FROM STUDENT_TEST WHERE STUDENT_ID = 2

        END

END