Here, we will learn about implementing the custom search functionality in the server-side data table with pagination. Earlier, we have implemented the server side pagination with data table in ASP.NET MVC. If you have not seen that, then you can see it from here.
We will be using the Code First Approach for it. If you have no idea of it, then you can see it from here.
Prerequisite:
- Basic knowledge of ASP.NET MVC
- Basic knowledge of EntityFramework
- Basic knowledge of Datatable
Create a new project in ASP.NET MVC. Then open the NuGet package manager console and add the EntityFramework package.
Install-Package EntityFramework
Now, create a Context.cs file and DemoTable.cs file in Model folder.
Code for Context.cs file
public class Context: DbContext
{
public Context() : base("StringDBContext")
{
}
public DbSet<DemoTables> DemoTables { get; set; }
}
Code for DemoTables.cs file
public class DemoTables
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
public string Quantity { get; set; }
public string Price { get; set; }
public string Status { get; set; }
}
Open the Web. Config file presents at root folder and adds the connection string in it.
<connectionStrings>
<add name="StringDBContext" connectionString="Server=DESKTOP-CGB025P;Initial Catalog=DemoDB;Persist Security Info=False;User ID=sa;Password=******;MultipleActiveResultSets=True;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient" />
</connectionStrings>
Open the Package manager console and type the following commands
Enable-Migrations Add-Migration Initial Update-Database
Open the _Layout.cshtml file and add the code in it.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title - My ASP.NET Application</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
<link href="//cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
</head>
<body>
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
@Html.ActionLink("Custom Datatable", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
</ul>
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<p>© @DateTime.Now.Year - My ASP.NET Application</p>
</footer>
</div>
@RenderSection("scripts", required: false)
</body>
</html>
Open the Index.cshtml file from Home folder and add the code in it.
@{
ViewBag.Title = "Home Page";
}
<div style="background-color:#f5f5f5; padding:20px">
<h2>Search Panel</h2>
<table>
<tbody>
<tr>
<td>Name</td>
<td><input type="text" id="txtName" /></td>
<td>
<input type="button" value="Search" id="btnSearch" />
</td>
</tr>
</tbody>
</table>
</div>
<table id="demodata" class="table table-bordered">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Quantity</th>
<th>Price</th>
<th>Status</th>
</tr>
</thead>
<tbody></tbody>
</table>
<script>
DemoDatatable();
function DemoDatatable() {
$("#demodata").DataTable({
"processing": true,
"serverSide": true,
"filter": true,
"orderMulti": false,
"destroy": true,
"ordering": true,
"ajax": {
"url": '/Home/GetTableData',
"type": "POST",
"datatype": "json"
},
"columns": [
{ "data": "Id", "name": "Id", "autoWidth": true }
, { "data": "Name", "Name": "Name", "autoWidth": true }
, { "data": "Quantity", "name": "Quantity", "autoWidth": true }
, { "data": "Price", "name": "Price", "autoWidth": true }
, { "data": "Status", "name": "Status", "autoWidth": true }
]
});
}
oTable = $('#demodata').DataTable();
$('#btnSearch').click(function () {
oTable.columns(1).search($('#txtName').val().trim());
oTable.draw();
});
</script>
Finally, code for HomeController.cs file
public class HomeController : Controller
{
private readonly Context _context = new Context();
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult GetTableData()
{
JsonResult result = new JsonResult();
try
{
string search = Request.Form.GetValues("search[value]")[0];
string draw = Request.Form.GetValues("draw")[0];
string order = Request.Form.GetValues("order[0][column]")[0];
string orderDir = Request.Form.GetValues("order[0][dir]")[0];
var empName = Request.Form.GetValues("columns[1][search][value]")[0];
int startRec = Convert.ToInt32(Request.Form.GetValues("start")[0]);
int pageSize = Convert.ToInt32(Request.Form.GetValues("length")[0]);
List<DemoTables> data = _context.DemoTables.ToList();
int totalRecords = data.Count;
if (!string.IsNullOrEmpty(search) &&
!string.IsNullOrWhiteSpace(search))
{
data = data.Where(p => p.Id.ToString().ToLower().Contains(search.ToLower()) ||
p.Name.ToString().Contains(search.ToLower()) ||
p.Quantity.ToString().Contains(search.ToLower()) ||
p.Price.ToString().Contains(search.ToLower()) ||
p.Status.ToString().Contains(search.ToLower())
).ToList();
}
if (!string.IsNullOrEmpty(empName))
{
data = data.Where(a => a.Name.ToString().ToLower().Contains(empName.ToLower())).ToList();
}
data = SortTableData(order, orderDir, data);
int recFilter = data.Count;
data = data.Skip(startRec).Take(pageSize).ToList();
var modifiedData = data.Select(d =>
new
{
d.Id,
d.Name,
d.Quantity,
d.Price,
d.Status
}
);
result = this.Json(new
{
draw = Convert.ToInt32(draw),
recordsTotal = totalRecords,
recordsFiltered = recFilter,
data = modifiedData
}, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
Console.Write(ex);
}
return result;
}
private List<DemoTables> SortTableData(string order, string orderDir, List<DemoTables> data)
{
List<DemoTables> lst = new List<DemoTables>();
try
{
switch (order)
{
case "0":
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Id).ToList()
: data.OrderBy(p => p.Id).ToList();
break;
case "1":
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Name).ToList()
: data.OrderBy(p => p.Name).ToList();
break;
case "2":
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Quantity).ToList()
: data.OrderBy(p => p.Quantity).ToList();
break;
case "3":
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Price).ToList()
: data.OrderBy(p => p.Price).ToList();
break;
case "4":
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Status).ToList()
: data.OrderBy(p => p.Status).ToList();
break;
default:
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Id).ToList()
: data.OrderBy(p => p.Id).ToList();
break;
}
}
catch (Exception ex)
{
Console.Write(ex);
}
return lst;
}
}
Output:
First time visiting your website, I like your site!
How can I have a form with a field for each column and one search button to draw the data in the table?