ankitkanojia/Common.cs

## Common.cs
//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;
}

## index.cshtml
@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>

## LoginController.cs
//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));
}

## Model.cs
//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; }
}
	//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; }
	}