Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Custom filter with search and previous/next functionality in MVC C#
//commoncode
public static CustomerModel GetData(int currentPage, string keyword)
{
int maxRows = 10;
var customerModel = new CustomerModel();
customerModel.Customers = new List<UserVm>();
var counter = 0;
var sqlQuery = string.Empty;
if(string.IsNullOrEmpty(keyword))
{
sqlQuery = "select * from [dbo].[Users] order by UserId offset " + (currentPage - 1) * maxRows + " rows FETCH NEXT 10 rows only";
}
else
{
sqlQuery = "select * from [dbo].[Users] where UserName like '%"+ keyword + "%' or Email like '%"+ keyword + "%' order by UserId offset " + (currentPage - 1) * maxRows + " rows FETCH NEXT 10 rows only";
}
var countQuery = string.Empty;
if (string.IsNullOrEmpty(keyword))
{
countQuery = "select count(*) from [dbo].[Users]";
}
else
{
countQuery = "select count(*) from [dbo].[Users] where UserName like '%" + keyword + "%' or Email like '%" + keyword + "%'";
}
string oledbConnectString = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=hphdealer;Integrated Security=SSPI";
using (OleDbConnection connection = new OleDbConnection(oledbConnectString))
{
OleDbCommand command = new OleDbCommand(sqlQuery, connection);
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
counter = counter + 1;
var _Email = reader["Email"].ToString();
var _UserId = reader["UserId"].ToString();
var _UserName = reader["UserName"].ToString();
var newUserVm = new UserVm
{
Email = _Email,
strUserId = _UserId,
UserName = _UserName
};
customerModel.Customers.Add(newUserVm);
}
}
var countCommand = new OleDbCommand(countQuery, connection);
var res = countCommand.ExecuteScalar();
var totalResult = Convert.ToDecimal(res);
var pageCount = (double)(totalResult / Convert.ToDecimal(maxRows));
if ((int)Math.Ceiling(pageCount) == currentPage)
{
customerModel.isLast = true;
}
}
customerModel.CurrentPageIndex = currentPage;
return customerModel;
}
@model GeneralApp.Models.ViewModels.CustomerModel
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<style type="text/css">
body {
font-family: Arial;
font-size: 10pt;
}
table {
border: 1px solid #ccc;
border-collapse: collapse;
background-color: #fff;
}
table th {
background-color: #B8DBFD;
color: #333;
font-weight: bold;
}
table th, table td {
padding: 5px;
border: 1px solid #ccc;
}
table, table table td {
border: 0px solid# ccc;
}
</style>
</head>
<body>
<div class="wrapper wrapper-content animated fadeInRight">
<div class="row">
<div class="col-lg-12">
<div class="ibox ">
<div class="ibox-title">
<h5>ITC Reco</h5>
</div>
<div class="ibox-content">
<div class="row">
<form class="form-inline">
<input class="form-control" type="text" id="txtSearch" /> <button class="btn btn-sm btn-success" onclick="SearchClick()" type="button">Search</button> <button class="btn btn-sm btn-white" onclick="ResetSearch()" type="button">Clear</button><br /><br />
</form>
<br />
<table id="tblCustom" cellpadding="0" cellspacing="0">
<thead>
<tr>
<th>CustomerID</th>
<th>ContactName</th>
<th>City</th>
</tr>
</thead>
<tbody>
@foreach (var customer in Model.Customers)
{
<tr>
<td>@customer.strUserId</td>
<td>@customer.UserName</td>
<td>@customer.Email</td>
</tr>
}
</tbody>
</table>
<br />
@{
var previousPageIndex = (Model.CurrentPageIndex - 1);
var nextPageIndex = (Model.CurrentPageIndex + 1);
<div class="form-inline row">
<button class="btn btn-sm btn-success btnPrevious" type="button" onclick="PagerClick(@previousPageIndex)">Previous</button>
<button class="btn btn-sm btn-success btnNext" type="button" onclick="PagerClick(@nextPageIndex)">Next</button>
</div>
}
</div>
</div>
</div>
</div>
</div>
<script src="~/Scripts/jquery-2.1.1.min.js"></script>
<script type="text/javascript">
function SearchData(index, keyword) {
$.ajax({
url: "@Url.Action("Login", "Auth")",
type: "POST",
async: false,
data: { currentPage: index , keyword : keyword },
success: function (data) {
if (data.data.Customers.length === 0) {
$(".btnNext").hide();
$(".btnPrevious").hide();
$("#tblCustom").find("tbody").html('');
$("#tblCustom").find("tbody").append("<tr class='text-center'><td colspan='3'>No Record Found.</td></tr>");
} else {
$(".btnNext").show();
$(".btnPrevious").show();
var nextIndex = parseInt(data.data.CurrentPageIndex) + 1;
var previousIndex = parseInt(data.data.CurrentPageIndex) - 1;
$(".btnNext").attr("onclick", "PagerClick(" + nextIndex + ")");
$(".btnPrevious").attr("onclick", "PagerClick(" + previousIndex + ")");
if (data.data.isLast) {
$(".btnNext").hide();
}
if (data.data.CurrentPageIndex === 1) {
$(".btnPrevious").hide();
}
if (data.data.Customers) {
$("#tblCustom").find("tbody").html('');
$.each(data.data.Customers, function (e, element) {
$("#tblCustom").find("tbody").append("<tr><td>" + element.strUserId + "</td><td>" + element.UserName + "</td><td>" + element.Email + "</td></tr>");
});
} else {
console.log("Something Wrong....!!!");
}
}
}
});
}
function PagerClick(index) {
SearchData(index, "");
}
function SearchClick() {
if ($("#txtSearch").val().length > 0) {
var keyword = $("#txtSearch").val().trim();
SearchData(1, keyword);
} else {
alert("enter search keyword.");
}
}
function ResetSearch(index) {
$("#txtSearch").val("");
SearchData(1, "");
}
</script>
</div>
</body>
</html>
//controller
[HttpPost]
public ActionResult Login(int currentPage, string keyword)
{
var returnData = StaticValues.GetData(currentPage, keyword);
return Json(new { data = returnData }, JsonRequestBehavior.AllowGet);
}
// GET: Login
public ActionResult Login()
{
return View(StaticValues.GetData(1, string.Empty));
}
//modelClass
public class CustomerModel
{
public List<UserVm> Customers { get; set; }
public int CurrentPageIndex { get; set; }
public int PageCount { get; set; }
public bool isLast { get; set; }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment