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();
}
}
}
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