In one of the previous article, I have explained how you can achieve jquery datatable server side processing in MVC using Web-API, in this post, we will be using similar technique but not using Web-API, instead we will be see example datatable server side processing in MVC with pagination, sorting and filtering using basic C# MVC controller.
Step 1: Open your Visual Studio, and create new project by navigating to "File"-> "New"-> "Project"-> Select "Web" from left pane and "ASP.NET Web-Application" from left pane -> Name your project and Click "Ok"
Select "MVC" template to add folder and references by Visual Studio, automatically, after creating project.
Now, we will be using below SQl server database table to show it in jQuery datatable in MVC C#.
You can use below scripts to create this table with data in database "Students"
USE [Students]
GO
/****** Object: Table [dbo].[Student_details] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_details](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Class] [varchar](50) NULL,
CONSTRAINT [PK_Student_details] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Student_details] ON
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (1, N'Suresh Wahi', N'suresh_wahi@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (2, N'Ramesh taurani', N'ramesh.taurani@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (3, N'Preetam', N'preeetam@gmail.com', N'Class XI')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (4, N'Surendra', N'Surendra@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (5, N'Martin ', N'Martin@gmail.com', N'Class XI')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (6, N'Maria', N'maria@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (7, N'Preeti', N'preeti@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (1004, N'Joseph', N'Joseph@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (1005, N'Santa', N'santa@gmail.com', N'Class XI')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (1006, N'Will', N'will@gmail.com', N'Class XI')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (1007, N'Martin', N'martin@gmail.com', N'Class XI')
SET IDENTITY_INSERT [dbo].[Student_details] OFF
Step 2: Connect your project with SQL server database using Entity framework, we will be using ADO.NET (.edmx) approach to connect to database quickly.
Right Click on Models Folders Inside your solution -> Add -> New item -> Select "ADO.net Entity Data Model" under "Data" -> Enter model name > Add.
From the pop-Up Select "EF Designer from database" -> Click "New Connection string" -> In next screen, Connect it with yout database by entering your SQL server username/password and select "Students" database, click "ok".
Click "Next"-> Select "Entity Framework" version -> Click "Next" -> Select "Tables" -> Click "Finish".
You will see .edmx file is generated and your project is connected with database.
Step 3: Create Model class (SysDataTablePager) for handling pagination and data of jQuery datatable, this model will accpet JSON data of jQuery datatables
Right-click on Models folder, Select "Add"-> "New Item"-> Class -> "SysDataTablePager.cs"
public class SysDataTablePager<T>
{
public SysDataTablePager(IEnumerable<T> items,
int totalRecords,
int totalDisplayRecords,
string sEcho)
{
aaData = items;
iTotalRecords = totalRecords;
iTotalDisplayRecords = totalDisplayRecords;
this.sEcho = sEcho;
}
public IEnumerable<T> aaData { get; set; }
public int iTotalRecords { get; set; }
public int iTotalDisplayRecords { get; set; }
public string sEcho { get; set; }
}Step 3: Go to pre-generated Controller -> HomeController.cs and the new method to fetch data from database and show it you user, using jQuery datatable, so we will be adding "GetData" method for it, so complete HomeController code would as below:
public class HomeController : Controller
{
StudentsEntities context = new StudentsEntities();
public ActionResult Index()
{
return View();
}
public JsonResult GetData()
{
NameValueCollection nvc = HttpUtility.ParseQueryString(Request.Url.Query);
string sEcho = nvc["sEcho"].ToString();
string sSearch = nvc["sSearch"].ToString();
int iDisplayStart = Convert.ToInt32(nvc["iDisplayStart"]);
int iDisplayLength = Convert.ToInt32(nvc["iDisplayLength"]);
//get total value count
var Count = context.Student_details.Count();
//get data from database
var Students = context.Student_details. //specify conditions if there is any using .Where(Condition)
OrderBy(d => d.Name).Skip(iDisplayStart).Take(10);
//Create a model for datatable paging and sending data & enter all the required values
var StudentsPaged = new SysDataTablePager<Student_details>(Students, Count, Count, sEcho);
return Json(StudentsPaged ,JsonRequestBehavior.AllowGet);
}
}Step 4: Go to "Views"-> "Home"-> "Index.cshtml" razor view, clear the pre-generated code, and use the code below to create datatable and get data from above created action method using server side processing
@{
ViewBag.Title = "Home Page";
}
<link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet" />
<table id="TableStudents">
<thead>
<tr>
<th>Name</th>
<th>Email </th>
<th>Class</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
@section Scripts{
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script>
$(function () {
$("#TableStudents").dataTable({
"bServerSide": true, //make server side processing to true
"sAjaxSource": '/Home/GetData', //url of the Ajax source,i.e. web api method
"sAjaxDataProp": "aaData", // data property of the returned ajax which contains table data
"bProcessing": true,
"bLengthChange": false,
"sPaginationType": "full_numbers",//pagination type
"aoColumns": [
{ "mData": "Name" },
{ "mData": "Email" },
{ "mData": "Class" }
]
});
});
</script>
}
in the above js code you can see we are processing jquery Data table on the server side with Ajax & have provided its URL as "/Home/GetData"
Once you are done, you can build and run the project, you will see output as below
But in the above output, pagination is only working. We still need to work on sorting and filtering.
Step 5: Now we can move on to implement, sorting and searching in the above code, let's go back to HomeController.cs -> GetData method & do the necessary changes, i am commenting code to make it understandable
public JsonResult GetData()
{
NameValueCollection nvc = HttpUtility.ParseQueryString(Request.Url.Query);
string sEcho = nvc["sEcho"].ToString();
string sSearch = nvc["sSearch"].ToString();
int iDisplayStart = Convert.ToInt32(nvc["iDisplayStart"]);
int iDisplayLength = Convert.ToInt32(nvc["iDisplayLength"]);
//iSortCol gives your Column numebr of for which sorting is required
int iSortCol = Convert.ToInt32(nvc["iSortCol_0"]);
//provides your sort order (asc/desc)
string sortOrder = nvc["sSortDir_0"].ToString();
//get total value count
var Count = context.Student_details.Count();
var Students = new List<Student_details>();
//Search query when sSearch is not empty
if (sSearch != "" && sSearch != null) //If there is search query
{
Students = context.Student_details.Where(a => a.Name.ToLower().Contains(sSearch.ToLower())
|| a.Email.ToLower().Contains(sSearch.ToLower())
|| a.Class.ToLower().Contains(sSearch.ToLower())
)
.ToList();
Count = Students.Count();
// Call SortFunction to provide sorted Data, then Skip using iDisplayStart
Students = SortFunction(iSortCol, sortOrder, Students).Skip(iDisplayStart).Take(iDisplayLength).ToList();
}
else
{
//get data from database
Students = context.Student_details //speficiy conditions if there is any using .Where(Condition)
.ToList();
// Call SortFunction to provide sorted Data, then Skip using iDisplayStart
Students = SortFunction(iSortCol, sortOrder, Students).Skip(iDisplayStart).Take(iDisplayLength).ToList();
}
var StudentsPaged = new SysDataTablePager<Student_details>(Students, Count, Count, sEcho);
return Json(StudentsPaged, JsonRequestBehavior.AllowGet);
}
//Sorting Function
private List<Student_details> SortFunction(int iSortCol, string sortOrder, List<Student_details> list)
{
//Sorting for String columns
if (iSortCol == 1 || iSortCol == 0 || iSortCol==2)
{
Func<Student_details, string> orderingFunction = (c => iSortCol == 0 ? c.Name : iSortCol == 1 ? c.Email : iSortCol == 2 ? c.Class: c.Name); // compare the sorting column
if (sortOrder == "desc")
{
list = list.OrderByDescending(orderingFunction).ToList();
}
else
{
list = list.OrderBy(orderingFunction).ToList();
}
}
return list;
}In the above code, I have changed method "Getdata" and also added another method "SortFunction"
GetData has following changes:
- We will now check is searchString exists when datatable query is passed, using "
string sSearch = nvc["sSearch"].ToString();", if yes, filter data and pass. - We will also check Sort column and Sort direction using "
int iSortCol = Convert.ToInt32(nvc["iSortCol_0"]);" and "string sortOrder = nvc["sSortDir_0"].ToString();" and perform sorting accordingly using "SortFunction"
Using SortFunction method, we are checking Column name and sorting direction passed to it, and perform sorting usign .OrderBy or .OrderByDescending as needed.
That's it, we are done.
Now build and run your project in browser, server side sorting and filtering is also enabled for jQuery datatable.
You can download the sample project.
You may also like to read:
jQuery datatable server side processing with Web API in ASP.NET MVC
Buy us a coffee
Become a Patron