Thursday, 3 April 2014

Grid View Custom Paging..

****************SP***********
CREATE PROCEDURE sp_getAllDataPager
 @PageNo int,
 @NoOfRecord int,
 @TotalRecord int output
AS
 -- Here Get Total No of record
 Select @TotalRecord = Count(*) from tblLab

 Select * from
 (
  Select
   Row_number() over( Order by ID ASC) as RowNo,
  ID,
   Assignment,
   RollNo
  From
   tblLab
 ) as Tab
 Where
  Tab.RowNo between ((@PageNo - 1) * @NoOfRecord) + 1 and (@PageNo * @NoOfRecord)
 Order by ID ASC

RETURN
*******************Design page***********
<body>
    <form id="form1" runat="server">
    <div>
     <div>
     <asp:GridView ID="GridView1" runat="server" CellSpacing="5" AutoGenerateColumns="true" Width="442px">
      
    </asp:GridView>
     <asp:Panel ID="Panel1" runat="server"></asp:Panel>

         
        </div>
    </div>
    </form>
</body>
******************Code Behind**************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace MyTestLearnProject
{
    public partial class GridViewCustomPagerTest : System.Web.UI.Page
    {
        private static SqlConnection GetCon()
        {
            SqlConnection con = new SqlConnection(@"Data Source=LHR-MH-PC79\;Initial Catalog=DBStudentTest;Integrated Security=True");
            return con;
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                populateGrid(1, 5);
            }

            AddpagingButton();
        }





        protected void populateGrid(int pageNo, int noOfRecord)
        {
            SqlConnection con = GetCon();
            SqlCommand cmd = new SqlCommand("sp_getAllDataPager", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageNo", pageNo);
            cmd.Parameters.AddWithValue("@NoOfRecord", noOfRecord);

            SqlParameter TotalRecordSP = new SqlParameter("@TotalRecord", System.Data.SqlDbType.Int);
            TotalRecordSP.Direction = System.Data.ParameterDirection.Output;
            cmd.Parameters.Add(TotalRecordSP);

            DataTable dt = new DataTable();
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
            dt.Load(cmd.ExecuteReader());

            int totalRecord = 0;
            if (TotalRecordSP.Value != null)
            {
                int.TryParse(TotalRecordSP.Value.ToString(), out totalRecord);
            }
            GridView1.DataSource = dt;
            GridView1.DataBind();

            // Store Total Record & No of record per page into view state for use in Generate Paging button method
            ViewState["TotalRecord"] = totalRecord;
            ViewState["NoOfRecord"] = noOfRecord;
       
        }

        private void AddpagingButton()
        {
            // this method for generate custom button for Custom paging in Gridview
            int totalRecord = 0;
            int noofRecord = 0;
            totalRecord = ViewState["TotalRecord"] != null ? (int)ViewState["TotalRecord"] : 0;
            noofRecord = ViewState["NoOfRecord"] != null ? (int)ViewState["NoOfRecord"] : 0;
            int pages = 0;
            if (totalRecord > 0 && noofRecord > 0)
            {
                // Count no of pages
                pages = (totalRecord / noofRecord) + ((totalRecord % noofRecord) > 0 ? 1 : 0);
                for (int i = 0; i < pages; i++)
                {
                    Button b = new Button();
                    b.Text = (i + 1).ToString();
                    b.CommandArgument = (i + 1).ToString();
                    b.ID = "Button_" + (i + 1).ToString();
                    b.Click += new EventHandler(this.b_click);
                    Panel1.Controls.Add(b);
                }
            }

        }
        protected void b_click(object sender, EventArgs e)
        {
            // this is for Get data from Database on button (paging button) click
            string pageNo = ((Button)sender).CommandArgument;
            populateGrid(Convert.ToInt32(pageNo), 5);
        }  
       

    


      
    }
}