Server Side Pagination Using Stored Procedure In ASP.NET MVC

May 24, 2019 0 10.2K

Faisal Pathan

  • 200
  • 832.5K
  • 161
  • 82

Faisal Pathan is a founder of TheCodeHubs, .NET Project Manager/Team Leader, and C# Corner MVP. ...

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

Prerequisites:

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.

  • bootstrap.min.css
  • dataTables.bootstrap.min.css
  • jquery-3.3.1.min.js
  • jquery.dataTables.min.js
  • dataTables.bootstrap.min.js

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>&copy; @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:

output

You can download the source code from here

 

Faisal Pathan

Faisal Pathan is a founder of TheCodeHubs, .NET Project Manager/Team Leader, and C# Corner MVP. He has extensive experience with designing and developing enterprise-scale applications. He has good skills in ASP.NET C#, ASP.NET Core, ASP.NET MVC, AngularJS, Angular, React, NodeJS, Amazon S3, Web API, EPPlus, Amazon MWS, eBay Integration, SQL, Entity Framework, JavaScript, eCommerce Integration like Walmart, Tanga, Newegg, Group-on Store, etc. and Windows services.
Read More

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories