****************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);
}
}
}
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);
}
}
}
No comments:
Post a Comment