Wednesday 2 January 2013

pagination using stored procedure

first create stored procedure:

alter PROCEDURE GetCustomersPageWise
    @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY salesorderid ASC
      )AS RowNumber
      ,salesorderid
      ,new_vendername
      ,new_couponcode
    INTO #Results
      FROM salesorder
     
      SELECT @RecordCount = COUNT(salesorderid)
      FROM #Results where ((RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1)) and (new_vendername='Cyberplat')
     
      SELECT * FROM #Results
      WHERE ((RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1)) and (new_vendername='Cyberplat')
    
      DROP TABLE #Results
END
GO

give the connect string in web.config

<connectionStrings>
    <add name="dbconnection" connectionString ="Data Source=crmdb1;Integrated Security=true;Initial Catalog= CRM_MSCRM" providerName="System.Data.SqlClient"/>
  </connectionStrings>


aspx code:

<body>
    <form id="form1" runat="server">
    <div>
     <%--PageSize:
<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
    <asp:ListItem Text="10" Value="10" />
    <asp:ListItem Text="25" Value="25" />
    <asp:ListItem Text="50" Value="50" />
</asp:DropDownList>
<hr />--%>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderText="CustomerId" DataField="salesorderid" />
        <asp:BoundField HeaderText="ContactName" DataField="new_vendername" />
        <asp:BoundField HeaderText="CompanyName" DataField="new_couponcode" />
    </Columns>
</asp:GridView>
<br />
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
    <asp:LinkButton ID="lnkPage" runat="server" Text = '<%#Eval("Text") %>' CommandArgument = '<%# Eval("Value") %>' Enabled = '<%# Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
   
    </div>
    </form>
</body>


c sharp code ;

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.Configuration;
using System.Data;

namespace testingpopup
{
    public partial class paginationtest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.GetCustomersPageWise(1);
            }

        }
        protected void PageSize_Changed(object sender, EventArgs e)
        {
            this.GetCustomersPageWise(1);
        }


        private void GetCustomersPageWise(int pageIndex)
        {
            string constring = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constring))
            {
                using (SqlCommand cmd = new SqlCommand("GetCustomersPageWise", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                    cmd.Parameters.AddWithValue("@PageSize", 50);
                    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
                    cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                    con.Open();
                    IDataReader idr = cmd.ExecuteReader();
                    GridView1.DataSource = idr;
                    GridView1.DataBind();
                    idr.Close();
                    con.Close();
                    int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                    this.PopulatePager(recordCount, pageIndex);
                }
            }
        }

        protected void Page_Changed(object sender, EventArgs e)
        {
            int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
            this.GetCustomersPageWise(pageIndex);
        }

        private void PopulatePager(int recordCount, int currentPage)
        {
            double dblPageCount = (double)((decimal)recordCount / 10);
            int pageCount = (int)Math.Ceiling(dblPageCount);
            List<ListItem> pages = new List<ListItem>();
            if (pageCount > 0)
            {
                pages.Add(new ListItem("First", "1", currentPage > 1));
                for (int i = 1; i <= pageCount; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
                pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
            }
            rptPager.DataSource = pages;
            rptPager.DataBind();
        }

    }
}

No comments:

Post a Comment