We have already discussed server-side pagination in ASP.NET MVC 5. Here we are going to use Stored Procedure for it and we will also discuss customizing the DataTable like adding buttons and customizing the data table columns based on our requirement.
If you have not seen the previous article of data table then you can see it from here
HTML
jQuery
AJAX
Bootstrap
CSS
ASP.NET MVC 5
C# Programming
Now Lets Begin:
Create a new project and add the following JS and CSS in it.
First, we will create the database. We are going to use the Database First Approach.
Let’s begin our coding.
We will design our stored procedure and table on which we are going to work
Take table name as Users
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Users]( [UserId] [int] IDENTITY(1,1) NOT NULL, [First_Name] [nvarchar](50) NULL, [Last_Name] [nvarchar](50) NULL, [Email_Address] [nvarchar](50) NULL, [Description] [nvarchar](250) NULL, [Password] [nvarchar](300) NULL, [Created_Date] [datetime] NULL, [RoleId] [int] NULL, [Address] [nvarchar](500) NULL, [City] [nvarchar](50) NULL, [State] [nvarchar](10) NULL, [Zip] [nvarchar](10) NULL, [Company] [int] NULL, [Image_Path] [varchar](200) NULL, [Is_Locked] [bit] NULL, [Is_Active] [bit] NULL, [Edit_Date] [datetime] NULL, CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [UserId] 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
Add the stored procedure as GetUsersDetail in the database.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[GetUserDetails] @Pageno INT=1, @filter VARCHAR(500)='', @pagesize INT=20, @Sorting VARCHAR(500)='UserId', @SortOrder VARCHAR(500)='desc' AS BEGIN SET NOCOUNT ON; DECLARE @SqlCount INT DECLARE @From INT = @pageno DECLARE @SQLQuery VARCHAR(5000) IF(@filter !='') BEGIN SET @SqlCount= ( SELECT COUNT(*) FROM Users as USR WHERE USR.Is_Active=1 AND (USR.First_Name LIKE '%'+@filter+'%' OR USR.Last_Name LIKE '%'+ @filter+'%' OR USR.Email_Address LIKE '%'+@filter+'%' OR USR.Created_Date LIKE '%' + @filter+'%')) SET @SQLQuery ='select usr.UserId as UserId,usr.First_Name,usr.Last_Name,usr.Email_Address,usr.Created_Date ,'+CONVERT(VARCHAR,@SqlCount)+' as TotalRecords from Users as usr where usr.Is_Active=1 and(usr.First_Name like ''%'+CONVERT(VARCHAR,@filter)+'%'' or usr.Last_Name like ''%'+CONVERT(VARCHAR,@filter)+'%'' or usr.Email_Address like ''%'+CONVERT(VARCHAR,@filter)+'%'') order by usr.'+ CONVERT(VARCHAR,@Sorting) +' '+ @SortOrder +' OFFSET '+CONVERT(varchar,@From)+' ROWS FETCH NEXT '+CONVERT(varchar,@pagesize)+' ROWS ONLY OPTION (RECOMPILE)' END ELSE BEGIN SET @SqlCount=( SELECT COUNT(*) FROM Users as USR WHERE USR.Is_Active=1) SET @SQLQuery ='select usr.UserId as UserId,usr.First_Name,usr.Last_Name,usr.Email_Address,usr.Created_Date,'+CONVERT(VARCHAR,@SqlCount)+' as TotalRecords from Users as usr where usr.Is_Active=1 order by usr.'+ CONVERT(VARCHAR,@Sorting) +' '+ @SortOrder +' OFFSET '+CONVERT(varchar,@From)+' ROWS FETCH NEXT '+CONVERT(varchar,@pagesize)+' ROWS ONLY OPTION (RECOMPILE)' end print @SQLQuery exec (@SQLQuery) END
Add the required JS and CSS in the layout file.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title - Server Side Datatable Demo</title>
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<link href="~/Content/dataTables.bootstrap.min.css" rel="stylesheet" />
<script src="~/Scripts/jquery-3.3.1.min.js"></script>
<script src="~/Scripts/jquery.dataTables.min.js"></script>
<script src="~/Scripts/dataTables.bootstrap.min.js"></script>
</head>
<body>
<div class="container body-content" style="margin-top:5%">
@RenderBody()
<hr />
<footer>
<p>© @DateTime.Now.Year - Server Side Datatable Demo</p>
</footer>
</div>
@RenderSection("scripts", required: false)
</body>
</html>
Navigate to View -> Home -> Index.cshtml
@{
ViewBag.Title = "Home Page";
}
<table id="data" class="table table-bordered">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr>
</thead>
<tbody></tbody>
</table>
<script>
GetDataTableData();
function GetDataTableData() {
$("#data").DataTable({
"processing": true,
"serverSide": true,
"filter": true,
"orderMulti": false,
"destroy": true,
"ordering": true,
"ajax": {
"url": '/Home/GetDetails',
"type": "POST",
"datatype": "json"
},
"columns": [
{
"data": "First_Name", "name": "First_Name", "autoWidth": true,
mRender: function (data, type, full) {
var id = data;
return "<p style='background-color:#090;color: white;text-align: center;'>" + id + "</p>";
}
}
, { "data": "Last_Name", "name": "Last_Name", "autoWidth": true }
, { "data": "Email_Address", "name": "Email_Address", "autoWidth": true }
]
});
}
</script>
As here you can see we have added mRender in the JS code which is used for customizing the data table as per our need.
"data": "First_Name", "name": "First_Name", "autoWidth": true,
mRender: function (data, type, full) {
var id = data; return "<p style='background-color:#090;color: white;text-align: center;'>" + id + "</p>";
}
} We will add the Controller side code for it.
using ServerSidePaginationUsingSP.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace ServerSidePaginationUsingSP.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
return View();
}
[HttpPost]
public JsonResult GetDetails()
{
DBFirstDemoEntities _context = new DBFirstDemoEntities();
List<GetUserDetails_Result> data = new List<GetUserDetails_Result>();
var start = (Convert.ToInt32(Request["start"]));
var Length = (Convert.ToInt32(Request["length"])) == 0 ? 10 : (Convert.ToInt32(Request["length"]));
var searchvalue = Request["search[value]"] ?? "";
var sortcoloumnIndex = Convert.ToInt32(Request["order[0][column]"]);
var SortColumn = "";
var SortOrder = "";
var sortDirection = Request["order[0][dir]"] ?? "asc";
var recordsTotal = 0;
try
{
switch (sortcoloumnIndex)
{
case 0:
SortColumn = "First_Name";
break;
case 1:
SortColumn = "Last_Name";
break;
case 2:
SortColumn = "Email_Address";
break;
case 3:
SortColumn = "Created_Date";
break;
case 4:
SortColumn = "Role_Name";
break;
default:
SortColumn = "UserId";
break;
}
if (sortDirection == "asc")
SortOrder = "asc";
else
SortOrder = "desc";
data = _context.GetUserDetails(start, searchvalue, Length, SortColumn, sortDirection).ToList();
recordsTotal = data.Count > 0 ? data[0].TotalRecords : 0;
}
catch (Exception ex)
{
}
return Json(new { data = data, recordsTotal = recordsTotal, recordsFiltered = recordsTotal }, JsonRequestBehavior.AllowGet);
}
}
} Output:
You can download the source code from here
In this article, we have to show Create and Used PIPE in angular
In this article, we have to show Create and Used PIPE in angular
In this article, we have to show Create and Used PIPE in angular