Hi RichardSa ,
Please refer below sample code.
Stored Procedure
CREATE PROCEDURE [dbo].[MyTableData]
@Name NVARCHAR(MAX)
,@PageIndex INT
,@PageSize INT
,@RecordCount INT OUT
AS
BEGIN
SELECT ROW_NUMBER() OVER(ORDER BY Name) RowNumber
,Id
,email
,Recipient
,DateCreated
,Name
INTO #Temp
FROM myTable
WHERE Name = @Name
SELECT @RecordCount = COUNT(*) FROM #Temp
SELECT * FROM #Temp
WHERE (RowNumber BETWEEN ((@PageIndex-1) * @PageSize) + 1 AND (@PageIndex * @PageSize)) OR @PageIndex = - 1
DROP TABLE #Temp
END
HTML
<asp:TextBox ID="txtName" runat="server" AutoPostBack="true" OnTextChanged="OnNameChange"></asp:TextBox>
<hr />
<asp:Label ID="labelName" runat="server" /><br />
<asp:Label ID="labelMail" runat="server" />
<br />
<asp:GridView ID="GridView1" runat="server" GridLines="None" DataKeyNames="Name"
AutoGenerateColumns="false" OnPageIndexChanging="OnPageIndexChanging" CssClass="table"
Width="100%">
<Columns>
<asp:BoundField DataField="email" HeaderText="Email" HeaderStyle-Font-Bold="false" />
<asp:BoundField DataField="Recipient" HeaderText="Receiver" HeaderStyle-Font-Bold="false" />
<asp:BoundField DataField="DateCreated" HeaderText="Date created" HeaderStyle-Font-Bold="false" />
</Columns>
</asp:GridView>
<div style="float: right; font-size: 10pt; margin-right: 1%;">
Page
<asp:Label ID="lblPageIndex" runat="server" Text="Label" />
of
<asp:Label ID="lblTotalPage" runat="server" />
(<asp:Label ID="lblTotal" runat="server" />
Records)
<div class="dvPager">
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
<asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
OnClick="Change_Page" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</div>
</div>
Namespaces
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Code
C#
protected void OnNameChange(object sender, EventArgs e)
{
Session["userName"] = txtName.Text;
ShowMyData();
this.GetTableData(1);
}
public void ShowMyData()
{
try
{
if (Session["userName"] != null)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM DemoUser WHERE userName = @userName", con))
{
cmd.Parameters.AddWithValue("@userName", Session["userName"]);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
labelName.Text = dr["Name"].ToString();
labelMail.Text = dr["email"].ToString();
}
con.Close();
}
}
}
else
{
Response.Redirect("Login.aspx");
}
}
catch (SqlException ex)
{
string msg = "Error:";
msg += ex.Message;
throw new Exception(msg);
}
}
int PageSize = 1;
private void GetTableData(int pageIndex)
{
try
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("MyTableData", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", labelName.Text.Trim());
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
con.Open();
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
using (SqlDataReader dr = cmd.ExecuteReader())
if (dr.HasRows)
{
//Template.Visible = true;
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
///Template.Visible = false;
}
con.Close();
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
}
}
con.Close();
}
}
}
catch (SqlException ex)
{
string msg = "Error:";
msg += ex.Message;
throw new Exception(msg);
}
}
protected void Change_Page(object sender, EventArgs e)
{
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
this.GetTableData(pageIndex);
}
private void PopulatePager(int recordCount, int currentPage)
{
double dblPageCount = (double)((decimal)recordCount / (decimal)PageSize);
int pageCount = (int)Math.Ceiling(dblPageCount);
List<ListItem> pages = new List<ListItem>();
if (pageCount > 0)
{
if (currentPage != 1)
{
pages.Add(new ListItem("Prev", (currentPage - 1).ToString()));
}
if (pageCount < 4)
{
for (int i = 1; i <= pageCount; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
}
else if (currentPage < 4)
{
for (int i = 1; i <= 4; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
pages.Add(new ListItem("...", (currentPage).ToString(), false));
}
else if (currentPage > pageCount - 4)
{
pages.Add(new ListItem("...", (currentPage).ToString(), false));
for (int i = currentPage - 1; i <= pageCount; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
}
else
{
pages.Add(new ListItem("...", (currentPage).ToString(), false));
for (int i = currentPage - 2; i <= currentPage + 2; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
pages.Add(new ListItem("...", (currentPage).ToString(), false));
}
if (currentPage != pageCount)
{
pages.Add(new ListItem("Next", (currentPage + 1).ToString()));
}
}
rptPager.DataSource = pages;
rptPager.DataBind();
lblPageIndex.Text = currentPage.ToString();
lblTotalPage.Text = ((recordCount / PageSize) + ((recordCount % PageSize) > 0 ? 1 : 0)).ToString();
lblTotal.Text = recordCount.ToString();
}
protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
this.GetTableData(1);
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
End Sub
Protected Sub OnNameChange(ByVal sender As Object, ByVal e As EventArgs)
Session("userName") = txtName.Text
ShowMyData()
Me.GetTableData(1)
End Sub
Public Sub ShowMyData()
Try
If Session("userName") IsNot Nothing Then
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("SELECT * FROM DemoUser WHERE userName = @userName", con)
cmd.Parameters.AddWithValue("@userName", Session("userName"))
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.Read() Then
labelName.Text = dr("Name").ToString()
labelMail.Text = dr("email").ToString()
End If
con.Close()
End Using
End Using
Else
Response.Redirect("Login.aspx")
End If
Catch ex As SqlException
Dim msg As String = "Error:"
msg += ex.Message
Throw New Exception(msg)
End Try
End Sub
Private PageSize As Integer = 1
Private Sub GetTableData(ByVal pageIndex As Integer)
Try
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("MyTableData", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Name", labelName.Text.Trim())
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", PageSize)
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4)
cmd.Parameters("@RecordCount").Direction = ParameterDirection.Output
con.Open()
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As DataTable = New DataTable()
sda.Fill(dt)
Using dr As SqlDataReader = cmd.ExecuteReader()
If dr.HasRows Then
GridView1.DataSource = dt
GridView1.DataBind()
Else
End If
End Using
con.Close()
Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("@RecordCount").Value)
Me.PopulatePager(recordCount, pageIndex)
End Using
End Using
con.Close()
End Using
End Using
Catch ex As SqlException
Dim msg As String = "Error:"
msg += ex.Message
Throw New Exception(msg)
End Try
End Sub
Protected Sub Change_Page(ByVal sender As Object, ByVal e As EventArgs)
Dim pageIndex As Integer = Integer.Parse((TryCast(sender, LinkButton)).CommandArgument)
Me.GetTableData(pageIndex)
End Sub
Private Sub PopulatePager(ByVal recordCount As Integer, ByVal currentPage As Integer)
Dim dblPageCount As Double = CDbl((CDec(recordCount) / CDec(PageSize)))
Dim pageCount As Integer = CInt(Math.Ceiling(dblPageCount))
Dim pages As List(Of ListItem) = New List(Of ListItem)()
If pageCount > 0 Then
If currentPage <> 1 Then
pages.Add(New ListItem("Prev", (currentPage - 1).ToString()))
End If
If pageCount < 4 Then
For i As Integer = 1 To pageCount
pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
Next
ElseIf currentPage < 4 Then
For i As Integer = 1 To 4
pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
Next
pages.Add(New ListItem("...", (currentPage).ToString(), False))
ElseIf currentPage > pageCount - 4 Then
pages.Add(New ListItem("...", (currentPage).ToString(), False))
For i As Integer = currentPage - 1 To pageCount
pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
Next
Else
pages.Add(New ListItem("...", (currentPage).ToString(), False))
For i As Integer = currentPage - 2 To currentPage + 2
pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
Next
pages.Add(New ListItem("...", (currentPage).ToString(), False))
End If
If currentPage <> pageCount Then
pages.Add(New ListItem("Next", (currentPage + 1).ToString()))
End If
End If
rptPager.DataSource = pages
rptPager.DataBind()
lblPageIndex.Text = currentPage.ToString()
lblTotalPage.Text = ((recordCount / PageSize) + (If((recordCount Mod PageSize) > 0, 1, 0))).ToString()
lblTotal.Text = recordCount.ToString()
End Sub
Protected Sub OnPageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
Me.GetTableData(1)
End Sub
Screenshot
