<asp:gridview id="GridView1" runat="server" autogeneratecolumns="false" cellpadding="4"
width="100%" datakeynames="AutoId" allowpaging="false" enableviewstate="false">
<Columns>
<asp:BoundField HeaderText="Auto Id" DataField="AutoId" />
<asp:BoundField HeaderText="First Name" DataField="FirstName" />
<asp:BoundField HeaderText="Last Name" DataField="LastName" />
<asp:BoundField HeaderText="Age" DataField="Age" />
<asp:BoundField HeaderText="Active" DataField="Active" />
</Columns>
</asp:gridview>
<hr />
<b>Navigate to: </b>
<asp:literal id="litPaging" runat="server" enableviewstate="false" />
string _ConnStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
int _startRowIndex = 0;
int _pageSize = 2;
int _thisPage = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (!string.IsNullOrWhiteSpace(Request.QueryString["startIndex"]))
{
_startRowIndex = int.Parse(Request.QueryString["startIndex"]);
_thisPage = int.Parse(Request.QueryString["page"]);
}
this.BindTheGrid();
}
}
private void BindTheGrid()
{
var totalCount = 0;
DataTable table = new DataTable();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = _ConnStr;
using (SqlCommand cmd = new SqlCommand("LoadPersonalDetails", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@startRowIndex", _startRowIndex);
cmd.Parameters.AddWithValue("@pageSize", _pageSize);
SqlParameter prm = new SqlParameter("@totalCount", SqlDbType.Int);
prm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm);
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
ad.Fill(table);
}
totalCount = int.Parse(prm.Value.ToString());
}
}
GridView1.DataSource = table;
GridView1.DataBind();
litPaging.Text = DoPagiation(totalCount);
}
private string DoPagiation(int totalCount)
{
var pages = totalCount / _pageSize;
int holder = 0;
StringBuilder strB = new StringBuilder();
for (int i = 0; i < pages; i++)
{
if (!holder.Equals(_thisPage))
{
strB.Append("<a href=\"CustomPagination.aspx?startIndex=" + (holder *
_pageSize) + "&page=" + holder + "\">Page - " + (holder + 1) + "</a> | ");
}
else
{
strB.Append("Page " + (holder + 1) + " | ");
}
holder++;
}
return strB.ToString();
}
SQL SERVER STORED PROCEDURE
-- EXEC LoadPagedArticles 10, 5CREATE PROCEDURE [dbo].[LoadPersonalDetails]
-- Add the parameters for the stored procedure here@startRowIndex int,
@pageSize int,
@totalCount int out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.SET NOCOUNT ON;
-- increase the startRowIndex by 1 to avoid returning the last record againSET @startRowIndex = @startRowIndex + 1
BEGIN
SELECT * FROM (
Select *,
ROW_NUMBER() OVER (ORDER BY AutoID ASC) as RowNum
FROM PersonalDetail -- put where clause here if any
) as People
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize)
- 1
ORDER BY AutoID ASC
SELECT @totalCount = COUNT(AutoId) FROM PersonalDetail
-- put where clause here if any
END
END
