Server side pagination using AngularJs in Asp.net MVC

Introduction:

Today in this article, I will explain How to perform Server side pagination using AngularJS in Asp.net MVC with the Stored procedure. In this example, I have used Ado.Net for database operation and Angular2.      

Server side pagination is very useful when we are using large amounts of data. At that time client side pagination will take a long time to get all the data at the current time, so it's better to make a Server call on every page request.

Follow these steps in order to implement “Server side pagination using AngularJS in Asp.net MVC with Stored procedure”

Step1: Create New Project.

 Go to File > New > Project > Web > Asp.net MVC web project > Enter Application Name > Select your project location > click to add button > It will show new dialog window for select template > here we will select empty project > then click to ok

Step2: Create the table and Stored procedure in the database.

In this example, I have used following table for Server side pagination.

CREATE TABLE [dbo].[tbl_registration] (

    [Sr_no]    INT            IDENTITY (1, 1) NOT NULL,

    [Email]    NVARCHAR (100) NULL,

    [Password] NVARCHAR (MAX) NULL,

    [Name]     VARCHAR (MAX)  NULL,

    [Address]  NVARCHAR (MAX) NULL,

    [City]     NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_tbl_registration] PRIMARY KEY CLUSTERED ([Sr_no] ASC)

);

Now create store procedure for Server side pagination

Create proc Sp_Get_data

@Pageindex int ,

@Pagesize int

as

begin

Select * from tbl_registration order by Sr_no desc Offset @Pagesize*(@Pageindex-1) Rows Fetch next @Pagesize rows only

Select Count(Email) as totalcount from tbl_registration

end

 Run above script in MS SQL Server and click to execute

 Step3: Add Connection string in web.config file

 Here I have added connection string in ‘web.config’ file under Configuration section as follows 

<connectionStrings>

    <add name="con" connectionString="Data Source=.;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>

  </connectionStrings>

 Step4: Create a controller.

Go to Solutions Explorer > right click on controller folder > Add Controller > Enter Controller name > Select template “empty MVC Controller   ” > Add.

Here I have created a controller “HomeController”

Now we will add a view for Index action where we will perform server side pagination.

Step5: Create model class

Now I have to create two class ‘register.cs’ and ‘regsiterlist.cs’ for display record and pagination.

Go to Solutions Explorer > right click on Model> Add New class > Enter class name (Here I rename it as ‘register.cs’).

Write all properties in ‘register.cs’ class as shown below

namespace AngularPagination.Models

{

    public class register

    {

        public int Sr_no { get; set; }

        public string  Email { get; set; }

        public string Password { get; set; }

        public string Name { get; set; }

        public string Address { get; set; }

        public string City { get; set; }

 

    }

}

In a similar way, add one more class as regsiterlist.cs to get the record and total count for pagination details, as shown below.

namespace AngularPagination.Models

{

    public class regsiterlist

    {

        public List<register> registerdata { get; set; }

        public int totalcount { get; set; }

    }

}

Step5: Create a class for Database operation.

Here in this example, I have used Ado.net as database operation so we need to create a class for all database operations. Here I have created ‘db.cs’ class.

 Go to Solutions Explorer > right click on project solution> Add New Folder > Enter Folder name (Here I rename it as ‘database_access_layer’) > right click on folder > Add new class.

Now write the following code snippet given below.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using AngularPagination.Models;

namespace AngularPagination.database_Access_layer

{

    public class db

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

        regsiterlist reglist = new regsiterlist();

        public regsiterlist Get_Paging_data(int pagesize,int pageindex)

        {

            SqlCommand com = new SqlCommand("Sp_Get_data", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@Pageindex", pageindex);

            com.Parameters.AddWithValue("@Pagesize", pagesize);

            con.Open();

            SqlDataReader dr = com.ExecuteReader();

            List<register> rslist = new List<register>();

            while (dr.Read())

            {

                register rs = new register();

                rs.Sr_no = Convert.ToInt32(dr["Sr_no"]);

                rs.Email = dr["Email"].ToString();

                rs.Password = dr["Password"].ToString();

                rs.Name = dr["Name"].ToString();

                rs.Address = dr["Address"].ToString();

                rs.City = dr["City"].ToString();

                rslist.Add(rs);

            }

            dr.NextResult();

            while (dr.Read())

            {

                reglist.totalcount = Convert.ToInt32(dr["totalcount"]);

            }

            reglist.registerdata = rslist;

            return reglist;

        }

    }

}

Inside this class, we are using ADO.NET for database operation and retrieve all information and the total count of the record.

Step6: Add Jsonresult GET methods in the controller for getting the record from Get_Paging_data method.

Here I have created following methods in HomeController, that has fallowing code snippet:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Data;

using AngularPagination.Models;

namespace AngularPagination.Controllers

{

    public class HomeController : Controller

    {

        database_Access_layer.db dblayer = new database_Access_layer.db();

        public JsonResult get_data(int pageindex,int pagesize)

        {

            regsiterlist rslist = new regsiterlist();

            rslist = dblayer.Get_Paging_data(pagesize,pageindex);

            return Json(rslist, JsonRequestBehavior.AllowGet);

        }

    }

}

In the code given above, we are using two parameters as 'pageindex' and 'pagesize' to get the current page record and a total number of record to be displayed respectively.

Step8: Add New JS file for AngularJS Controller and AngularJS library file

Go to Solution Explorer > Right Click on the folder (where you want to save your AngularJS controller JS files, here I have created a folder named "AngularController"  under Resource Folder) > Add > Select Javascript file > Enter a name(I have to name as Homejs.js) > Add.

Inside the Resources folder, keep the angular.js, ui-bootstrap-tpls-0.13.4.min.js and bootstrap.min.css files for our use.

Now we write following code snippet into Homejs.js

var app = angular.module("Homeapp", ['ui.bootstrap']);

 

app.controller("HomeController", function ($scope, $http) {

    $scope.maxsize = 5;

    $scope.totalcount = 0;

    $scope.pageIndex = 1;

    $scope.pageSize = 5;

 

    $scope.registerlist = function () {

        $http.get("/Home/get_data?pageindex=" + $scope.pageIndex + "&pagesize=" + $scope.pageSize).then(function (response) {

 

            $scope.registerdata = response.data.registerdata;

            $scope.totalcount = response.data.totalcount;

        }, function (error) {

            alert('failed');

        });

    }

 

    $scope.registerlist();

 

    $scope.pagechanged = function () {

        $scope.registerlist();

    }

 

    $scope.changePageSize = function () {

        $scope.pageIndex = 1;

        $scope.registerlist();

    }

});

In the code given above, we are using registerlist() Method and passing pageIndex and pageSize as a query string for getting all the record from the controller.

Whenever a user will click any page number, the pageIndex value will change and it will call pagechanged() method and we are calling registerlist() method.

Users can also change the maximum number of items per page by using the drop-down. When a user will change the drop-down value from the drop-down list, the changePageSize() method will be called and inside the method, we are setting pageIndex = 1 and calling the registerlist() method.

Step9:  Add new action into the controller for show data from the database.

 Here I have added ‘Index’ into ‘HomeController’. I have following code.

public ActionResult Index()

        {

            return View();

        }

Step10: Add view for action in controller & design.

Right Click on Action Method > Add View > Enter View Name > Select View Engine (Razor) > Add.

It has following code and design

@{

    Layout = null;

}

 

<!DOCTYPE html>

 

<html>

<head>

    <meta name="viewport" content="width=device-width" />

    <title>Index</title>

    <script src="~/Resource/angular.js"></script>

    <link href="~/Resource/bootstrap.min.css" rel="stylesheet" />

    <script src="~/Resource/ui-bootstrap-tpls-0.13.4.min.js"></script>

    <script src="~/Resource/AngularController/Homejs.js"></script>

</head>

<body ng-app="Homeapp" ng-controller="HomeController">

    <div class="table-responsive">

        <table class="table table-striped table-bordered table-hover tabel-condensed">

            <thead>

                <tr>

                    <th>Sr no</th>

                    <th>Email</th>

                    <th>Password</th>

                    <th>Name</th>

                    <th>Address</th>

                    <th>City</th>

                </tr>

            </thead>

            <tbody>

                <tr ng-repeat="e in registerdata">

                    <td>{{e.Sr_no}}</td>

                    <td>{{e.Email}}</td>

                    <td>{{e.Password}}</td>

                    <td>{{e.Name}}</td>

                    <td>{{e.Address}}</td>

                    <td>{{e.City}}</td>

                </tr>

            </tbody>

            <tfoot>

                <tr>

                    <td align="center" colspan="6   ">

                        <span class="form-group pull-left page-size form-inline">

                            <select id="ddlPageSize" class="form-control control-color"

                                    ng-model="pageSize"

                                    ng-change="changePageSize()">

                                <option value="5">5</option>

                                <option value="10">10</option>

                                <option value="25">25</option>

                                <option value="50">50</option>

                            </select>

                        </span>

                        <div class="pull-right">

                            <pagination total-items="totalcount" ng-change="pagechanged()" items-per-page="pageSize" direction-links="true" ng-model="pageIndex" max-size="maxsize" class="pagination" boundary-links="true" rotate="false" num-pages="numPages"></pagination>

                            <a class="btn btn-primary">Page: {{pageIndex}} / {{numPages}}</a>

                        </div>

                    </td>

                </tr>

            </tfoot>

        </table>

    </div>

</body>

</html>

 

Below is the description for the attributes of pagination element.

• total-items - Total number of items in all the pages.
• items-per-page (Defaults: 10) - Maximum number of items per page. A value less than one 
• max-size (Defaults: null) - Limit number for pages.
• ng-change - It can be to call a function whenever the page changes.
• num-pages - It is read-only and an optional expression assigns the total number of pages to display.
• rotate (Defaults: true) - Whether to keep the current page in the middle of the visible ones.
• direction-links(Default: true) - Whether to display Previous/ Next buttons.
• boundary-links (Default: false) - Whether to display First/ Last buttons.
• ng-model - Current page number.

Step 11: Run Application.

We have done all steps, Now It’s time to run the application