ASP.Net C# Bootstrap 4 Pagination

• Qayyum
• November 22, 2017
• 3,768

Create database table

Create your table from where you want to show data. (If you dont have database ready)

CREATE TABLE IF NOT EXISTS `users` (	`id` int(6) unsigned NOT NULL,	`name` varchar(50) NOT NULL,	`country` varchar(50) NOT NULL,	PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

Insert some records

INSERT INTO `users` (`id`, `name`, `country`) VALUES
('1', 'Qayyum', 'PK'),
('2', 'Adnan', 'PK'),
('3', 'Naqeeb', 'PK'),
('4', 'Tabeer', 'PK'),
('5', 'Shiv', 'IN'),
('6', 'Song', 'CN'),
('7', 'Saqib', 'PK');

Create procedures

Procedure to get total number of users

Create Procedure GetUsersCount_SPas
SELECT COUNT(*) FROM users

Procedure to get records from users table

Create Procedure GetUsers_SP(
@PageNo int,
@PageSize int
)
as
SELECT ID, Name, Country
FROM(	SELECT ROW_NUMBER() OVER (ORDER by id) as ID,	name as Name,	country as Country,	FROM users
) T
WHERE ID BETWEEN (((@PageNo - 1) * @PageSize) + 1) AND (@PageNo*@PageSize)

Create Asp.Net webpage

Declare variables:

int page = 0;
int total = 0;
int limit = 5;//Records per page
int adjacents = 2;
int start = 0;
int prev = 0;
int next = 0;
int lastpage = 0;
int lpm1 = 0;
int counter = 0;
string connection_string = "YOUR_CONNECTION_STRING_HERE";

Open database connection

SqlConnection con = new SqlConnection(connection_string);
con.Open();

Get total records by using first procedure

using (SqlCommand command = new SqlCommand(
"GetUsersCount_SP", con))
{ command.CommandType = CommandType.StoredProcedure; object count = command.ExecuteScalar(); total = int.Parse(count.ToString());
}

Check if we have page number in query string

if (!string.IsNullOrEmpty(Request.QueryString["page"]))
{ page = Convert.ToInt32(Request.QueryString["page"]);
}
prev = page - 1;
next = page + 1;
//Recheck next line of code if you are getting correct number of pages from it.
//I checked it with above 5000 rows, it was working fine for me.
lastpage = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(total / limit)));
lpm1 = lastpage - 1;

Getting records from users table by using second procedure

DataTable dt = new DataTable();
using (SqlCommand command = new SqlCommand(
"GetUsers_SP", con))
{ command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@PageNo", SqlDbType.Int).Value = page; command.Parameters.Add("@PageSize", SqlDbType.Int).Value = limit; SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(dt);
}
con.Close();

Use above data table to show records somehere

foreach (DataRow row in dt.Rows)
{	string ID = row["ID"].ToString();	string Name = row["Name"].ToString();	string Country = row["Country"].ToString();	string addRow = string.Format("{0}{1}{2}{3}", ID, Name, Country);	Records.Text += addRow; //Show the data
}

Now pagination stuff

string pagination = "";
if (lastpage > 1)
{ pagination += "<ul class='pagination'>"; //previous button if (page > 1) pagination = pagination + "<li class='page-item'><a class='page-link' href='Users.aspx?page=" + prev + "'>←</a></li>"; else pagination += "<li class='page-item disabled'><a class='page-link'>←</a></li>"; //pages if (lastpage < 7 + (adjacents * 2)) //not enough pages to bother breaking it up { for (counter = 1; counter <= lastpage; counter++) { if (counter == page) pagination += "<li class='page-item disabled'><a class='page-link'>" + counter + "</a></li>"; else pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=" + counter + "'>" + counter + "</a></li>"; } } else if (lastpage > 5 + (adjacents * 2)) //enough pages to hide some { //close to beginning; only hide later pages if (page < 1 + (adjacents * 2)) { for (counter = 1; counter < 4 + (adjacents * 2); counter++) { if (counter == page) pagination += "<li class='page-item active'><a class='page-link'>" + counter + "</a></li>"; else pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=" + counter + "'>" + counter + "</a></li>"; } pagination += "<li class='page-item disabled'><a class='page-link'>...</a></li>"; pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=" + lpm1 + "'>" + lpm1 + "</a></li>"; pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=" + lastpage + "'>" + lastpage + "</a></li>"; } //in middle; hide some front and some back else if (lastpage - (adjacents * 2) > page && page > (adjacents * 2)) { pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=1'>1</a></li>"; pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=2'>2</a></li>"; pagination += "<li class='page-item disabled'><a class='page-link'>...</a></li>"; for (counter = page - adjacents; counter <= page + adjacents; counter++) { if (counter == page) pagination += "<li class='page-item disabled'><a class='page-link'>" + counter + "</a></li>"; else pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=" + counter + "'>" + counter + "</a></li>"; } pagination += "<li class='page-item disabled'><a class='page-link'>...</a></li>"; pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=" + lpm1 + "'>" + lpm1 + "</a></li>"; pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=" + lastpage + "'>" + lastpage + "</a></li>"; } //close to end; only hide early pages else { pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=1'>1</a></li>"; pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=2'>2</a></li>"; pagination += "<li class='page-item disabled'><a class='page-link'>...</a></li>"; for (counter = lastpage - (2 + (adjacents * 2)); counter <= lastpage; counter++) { if (counter == page) pagination += "<li class='page-item active'><a class='page-link'>" + counter + "</a></li>"; else pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=" + counter + "'>" + counter + "</a></li>"; } } } //next button if (page < counter - 1) pagination += "<li class='page-item'><a class='page-link' href='Users.aspx?page=" + next + "'>→</a></li>"; else pagination += "<li class='page-item'><a class='page-link'>→</a></li>"; pagination += "</ul>";
}
Pagination.Text = pagination;//Show the data