Server side pagination in AngularJs
In this we will discuss:
- paging
- filtering
- Sorting
- Searching
The article divided into 5 parts
- Service
- Controller
- Html Tempelate
- AngularJS Controller
- Store Procedure
Product Service
public ProductsResponse GetProducts(int organizationID, int pageNumber = 1, int maxRecords = 10, string orderBy = "OrgName", string sortMethod = "DESC", string search = null) { ProductsResponse products = new ProductsResponse(); try { using (var dbo = new Data.Entities()) { var objectContext = ((IObjectContextAdapter)dbo).ObjectContext; var command = dbo.Database.Connection.CreateCommand(); command.CommandType = System.Data.CommandType.StoredProcedure; command.CommandText = "sp_GetProducts"; SqlParameter param_OrderBy = new SqlParameter("@orderBy", SqlDbType.VarChar); param_OrderBy.Direction = ParameterDirection.Input; param_OrderBy.Value = orderBy; command.Parameters.Add(param_OrderBy); SqlParameter param_Decending = new SqlParameter("@isDescending", SqlDbType.Bit); param_Decending.Direction = ParameterDirection.Input; param_Decending.Value = (sortMethod == "DESC") ? true : false; command.Parameters.Add(param_Decending); SqlParameter param_PageNumber = new SqlParameter("@pageNumber", SqlDbType.Int); param_PageNumber.Direction = ParameterDirection.Input; param_PageNumber.Value = pageNumber; command.Parameters.Add(param_PageNumber); SqlParameter param_searchWord = new SqlParameter("@searchWord", SqlDbType.VarChar); param_searchWord.Direction = ParameterDirection.Input; param_searchWord.Value = search; command.Parameters.Add(param_searchWord); SqlParameter param_RecordPerPage = new SqlParameter("@rowsPerPage", SqlDbType.Int); param_RecordPerPage.Direction = ParameterDirection.Input; param_RecordPerPage.Value = maxRecords; command.Parameters.Add(param_RecordPerPage); SqlParameter param_organizationID = new SqlParameter("@organizationID", SqlDbType.Int); param_organizationID.Direction = ParameterDirection.Input; param_organizationID.Value = organizationID; command.Parameters.Add(param_organizationID); dbo.Database.Connection.Open(); using (var reader = command.ExecuteReader()) { var countRecord = ((IObjectContextAdapter)dbo).ObjectContext.Translate<BaseListResponse>(reader: reader).SingleOrDefault(); products.TotalRecords = countRecord.TotalRecords; reader.NextResult(); products.ProductsList = ((IObjectContextAdapter)dbo).ObjectContext.Translate<ProductsModel>(reader: reader).ToList(); } } } catch (Exception ex) { throw ex; } return products; }
Product Controller
[CheckSession]
[RoutePrefix("api/products")]
public class ProductsController : ApiController
{
private Product product = new Product();
[CheckSession]
[HttpGet, AllowAnonymous, Route("getAllproducts")]
public HttpResponseMessage GetProducts(int pageNumber = 1, string orderBy = "CreatedOn", string sortingMethod = "DESC", string searchWord = null)
{
int organizationID = 0;
ProductsResponse productsData = new ProductsResponse();
try
{
productsData = product.GetProducts(organizationID, pageNumber, 20 , orderBy, sortingMethod, searchWord);
if (productsData.Errors.Count == 0)
{
return Request.CreateResponse(HttpStatusCode.OK, productsData);
}
}
catch (Exception ex)
{
throw ex;
}
return Request.CreateResponse(HttpStatusCode.BadRequest, productsData);
}
}
HTML Template
<div ng-controller="ProductsCtrl" ng-init="GetAllProducts()"> <!-- begin row --> <div class="row"> <!-- begin col-12 --> <div class="col-md-12"> <!-- begin panel --> <div class="panel panel-inverse"> <div class="panel-heading"> <div class="panel-heading-btn"> <a href="javascript:;" class="btn btn-xs btn-icon btn-circle btn-default" data-click="panel-expand"><i class="fa fa-expand"></i></a> <a href="javascript:;" class="btn btn-xs btn-icon btn-circle btn-success" data-click="panel-reload"><i class="fa fa-repeat"></i></a> <a href="javascript:;" class="btn btn-xs btn-icon btn-circle btn-warning" data-click="panel-collapse"><i class="fa fa-minus"></i></a> </div> <h4 class="panel-title">Products</h4> </div> <div class="panel-body"> <div class="row"> <div class="col-md-8 align-mergin"> <button class="btn btn-primary m-r-5 m-b-5" type="button" ng-click="AddEditProductDailog(null)">Add Products</button> </div> <div class="col-md-4 align-mergin"> <div id="custom-search-input"> <div class="input-group pull-right"> <div class="form-group has-feedback"> <input type="text" ng-model="SearchWord" ng-change="OnChangeOfSearch()" maxlength="250" ng-keyup="$event.keyCode == 13 ? GetSearch() : null" class="form-control input-sm" placeholder="Search"> </div> <span class="input-group-btn"> <button ng-click="GetSearch()" class="btn btn-info btn-sm" type="button"> <i class="glyphicon glyphicon-search"></i> </button> </span> </div> </div> </div> </div> <table id="productDT" class="table table-striped table-bordered sortth"> <thead> <tr> <th class="details-control sorting_disabled noSort" rowspan="1" colspan="1" aria-label="" style="width: 18px;"> <!--<input type="checkbox" ng-model="IsShowAll" ng-change="ShowAll()" />--> </th> <th nowrap ng-click="Sort('CompanyName')" ng-class="(orderByField != 'CompanyName' ? 'sorting' : reverseSort == true ? 'sorting_asc' : 'sorting_desc')"> Brand </th> <th nowrap ng-click="Sort('ProType')" ng-class="(orderByField != 'ProType' ? 'sorting' : reverseSort == true ? 'sorting_asc' : 'sorting_desc')"> Product Type </th> <th class="noSort"> Model </th> <th nowrap ng-click="Sort('ProductName')" ng-class="(orderByField != 'ProductName' ? 'sorting' : reverseSort == true ? 'sorting_asc' : 'sorting_desc')"> Description </th> <th nowrap ng-click="Sort('Storage')" ng-class="(orderByField != 'Storage' ? 'sorting' : reverseSort == true ? 'sorting_asc' : 'sorting_desc')"> Storage </th> <th nowrap ng-click="Sort('Colour')" ng-class="(orderByField != 'Colour' ? 'sorting' : reverseSort == true ? 'sorting_asc' : 'sorting_desc')"> Colour </th> <th nowrap ng-click="Sort('Grade')" ng-class="(orderByField != 'Grade' ? 'sorting' : reverseSort == true ? 'sorting_asc' : 'sorting_desc')"> Grade </th> <th nowrap ng-click="Sort('WSellingPrice')" ng-class="(orderByField != 'WSellingPrice' ? 'sorting' : reverseSort == true ? 'sorting_asc' : 'sorting_desc')"> Wholesale Price </th> <th nowrap ng-click="Sort('RSellingPrice')" ng-class="(orderByField != 'RSellingPrice' ? 'sorting' : reverseSort == true ? 'sorting_asc' : 'sorting_desc')"> Retail Price </th> <th nowrap ng-click="Sort('Barcode')" ng-class="(orderByField != 'Barcode' ? 'sorting' : reverseSort == true ? 'sorting_asc' : 'sorting_desc')"> Barcode </th> <th class="noSort">Actions</th> </tr> </thead> <tbody> <tr ng-repeat="pro in ProductData" id="trc{{$index}}" ng-init="$last && DTIsReady()"> <td class=" details-control" id="tdc{{$index}}" ng-click="ShowPhones($index,pro.ProductId)"></td> <td>{{pro.CompanyName}}</td> <td>{{pro.ProductTypeName}}</td> <td>{{pro.Model}} </td> <td>{{pro.ProductName}} </td> <td> {{pro.Storage}}</td> <td> {{pro.Colour}}</td> <td> {{pro.Grade}}</td> <td> {{pro.SellingPriceWholesale>0?'£'+ pro.SellingPriceWholesale:''}}</td> <td> {{pro.SellingPriceRetail>0?'£'+ pro.SellingPriceRetail:''}}</td> <td> {{pro.Barcode}}</td> <td> <a href="javascript:; " ng-click="AddEditProductDailog(pro.ProductId)"><span class="glyphicon glyphicon-pencil"></span></a> <a href="javascript:; " style="margin-left: 10px" ng-click="DeleteProd(pro.ProductId)"><span class="glyphicon glyphicon-trash"></span></a> <!--<button class="btn btn-xs btn-info" style="margin-left: 10px" type="button" ng-click="ShowPhones(pro.ProductId)">Show phones</button>--> </td> </tr> <tr ng-if="ProductData.length == 0"> <td class="text-center" colspan="11"> No record found </td> </tr> </tbody> </table> <div ng-if="ProductData.length != 0"> <span> <button ng-disabled="CurrentPage == 1" ng-click="PreviousPage()" class="btn btn-primary">«</button> <span class="btn btn-default"><span ng-bind="CurrentPage"></span></span> <button ng-disabled="RecordsPerPage * CurrentPage >= TotalRecords" ng-click="NextPage()" class="btn btn-primary">»</button> </span> <span> Page: <span ng-bind="CurrentPage"></span> of <span ng-bind="TotalPages"></span>, Total Records: <span ng-bind="TotalRecords"></span> </span> </div> </div> </div> <!-- end panel --> </div> <!-- end col-12 --> </div> </div>
AngularJS Controller
adminApp.controller('ProductsCtrl', function ($scope, $http) { $scope.ProductData = null; $scope.CurrentPage = 1; $scope.TotalRecords = 0; $scope.TotalPages = 0; $scope.SearchWord = null; $scope.orderByField = 'ProductName'; $scope.sortingMethod = 'ASC'; $scope.reverseSort = false; $scope.isTable = false; $scope.IsShowAll = false; $scope.IsBarcode = false; $scope.SearchWord = $scope.globalProdsearch; $scope.GetAllProducts = function () { $scope.GetProductColorStorageGrade(); $http({ method: 'GET', url: '/api/products/getAllproducts', params: { pageNumber: $scope.CurrentPage, orderBy: $scope.orderByField, sortingMethod: $scope.sortingMethod, searchWord: $scope.SearchWord } }) .then(function (response) { if (response.data != null) { $scope.ErrorMessage = ''; if (response.data.ProductsList.length > 0 && $.fn.dataTable.isDataTable('#productDT')) { $('#productDT').DataTable().clear(); $('#productDT').DataTable().destroy(); $scope.dtPro = null; } $scope.ProductData = response.data.ProductsList; $scope.TotalRecords = response.data.TotalRecords; $scope.TotalPages = $scope.GetTotalPages(); } }, function (e) { if (e != null && e.status == 302) { swal("Error", e.data, "error").then((value) => { window.location = "/Home/Login"; }); } swal("Error", e.data.Errors[0], "error"); $scope.LoadErrorPage(e); }); }; $scope.GetTotalPages = function () { return Math.ceil($scope.TotalRecords / $scope.RecordsPerPage); }; $scope.Sort = function (column) { if ($scope.orderByField != column) { $scope.reverseSort = false; } else { $scope.reverseSort = !$scope.reverseSort; } $scope.orderByField = column; $scope.sortingMethod = ($scope.reverseSort) ? "DESC" : "ASC"; $scope.GetAllProducts(); } $scope.NextPage = function () { $scope.CurrentPage = $scope.CurrentPage + 1; $scope.GetAllProducts(); }; $scope.PreviousPage = function () { $scope.CurrentPage = $scope.CurrentPage - 1; $scope.GetAllProducts(); }; $scope.OnChangeOfSearch = function () { $scope.CurrentPageOrg = 1; if ($scope.SearchWord == null || $scope.SearchWord == '') { $scope.GetAllProducts(); } }; $scope.OnChangeOfClear = function () { $scope.CurrentPageOrg = 1; $scope.SearchWord = ''; $scope.GetAllOrgs(); }; $scope.GetSearch = function () { $scope.CurrentPageOrg = 1; $scope.GetAllProducts(); } $scope.DTIsReady = function () { if ($scope.ProductData.length > 0 && !$.fn.dataTable.isDataTable('#productDT')) { setTimeout(() => { $scope.dtPro = $('#productDT').DataTable({ "paging": false, "ordering": false, "info": false, "searching": false }); if (!$("productDT th").hasClass('sorting')) { $(".sortth th").not('.sorting_desc, .sorting_asc, .noSort').addClass('sorting'); } }, 100); } } });
Store Procedure
CREATE PROCEDURE [dbo].[sp_GetProducts] @orderBy nvarchar(250) = 'ProductName', @isDescending bit = 1, @pageNumber int = 1, @rowsPerPage int =10, @searchWord nvarchar(250) = null, @organizationID int AS BEGIN SET NOCOUNT ON SELECT COUNT(pro.[ProductId]) TotalRecords FROM [dbo].[Products] pro INNER JOIN [dbo].[Company] comp on comp.[CompanyId] = pro.[CompanyId] INNER JOIN [dbo].[ProductType] pt on pt.ProductTypeId = pro.ProductTypeId WHERE ( comp.[CompanyName] Like '%'+@searchWord+'%' OR pro.[ProductName] Like '%'+@searchWord+'%' OR pro.Model Like '%'+@searchWord+'%' OR pro.[Storage] Like '%'+@searchWord+'%' OR pro.[Colour] Like '%'+@searchWord+'%' OR pro.[Grade] Like '%'+@searchWord+'%' OR pro.[Barcode] Like '%'+@searchWord+'%' OR pt.[ProductTypeName] Like '%'+@searchWord+'%' OR pro.[SellingPrice_Wholesale] Like '%'+@searchWord+'%' OR @searchWord IS NULL Or @searchWord = '' OR @searchWord IS NULL Or @searchWord = '' ) AND pro.[OrganizationId] = @organizationID SELECT pro.[ProductId] ,comp.[CompanyName] ,pro.[ProductName] ,pro.Model ,pro.[Storage] ,pro.[Colour] ,pro.[Grade] ,pro.[SellingPrice_Wholesale] AS SellingPriceWholesale ,pro.[SellingPrice_Retail] AS SellingPriceRetail ,pt.[ProductTypeName] ,pro.Barcode FROM [dbo].[Products] pro INNER JOIN [dbo].[Company] comp on comp.[CompanyId] = pro.[CompanyId] INNER JOIN [dbo].[ProductType] pt on pt.ProductTypeId = pro.ProductTypeId WHERE ( comp.[CompanyName] Like '%'+@searchWord+'%' OR pro.[ProductName] Like '%'+@searchWord+'%' OR pro.Model Like '%'+@searchWord+'%' OR pro.[Storage] Like '%'+@searchWord+'%' OR pro.[Colour] Like '%'+@searchWord+'%' OR pro.[Grade] Like '%'+@searchWord+'%' OR pro.[Barcode] Like '%'+@searchWord+'%' OR pt.[ProductTypeName] Like '%'+@searchWord+'%' OR pro.[SellingPrice_Wholesale] Like '%'+@searchWord+'%' OR @searchWord IS NULL Or @searchWord = '' OR @searchWord IS NULL Or @searchWord = '' ) AND pro.[OrganizationId] = @organizationID ORDER BY CASE WHEN @orderBy = 'CompanyName' AND @isDescending =1 THEN comp.[CompanyName] END DESC, CASE WHEN @orderBy = 'CompanyName' AND @isDescending =0 THEN comp.[CompanyName] END ASC, CASE WHEN @orderBy = 'ProductName' AND @isDescending =1 THEN pro.[ProductName] END DESC, CASE WHEN @orderBy = 'ProductName' AND @isDescending =0 THEN pro.[ProductName] END ASC, CASE WHEN @orderBy = 'Storage' AND @isDescending =1 THEN pro.[Storage] END DESC, CASE WHEN @orderBy = 'Storage' AND @isDescending =0 THEN pro.[Storage] END ASC, CASE WHEN @orderBy = 'Colour' AND @isDescending =1 THEN pro.[Colour] END DESC, CASE WHEN @orderBy = 'Colour' AND @isDescending =0 THEN pro.[Colour] END ASC, CASE WHEN @orderBy = 'Grade' AND @isDescending =1 THEN pro.[Grade] END DESC, CASE WHEN @orderBy = 'Grade' AND @isDescending =0 THEN pro.[Grade] END ASC, CASE WHEN @orderBy = 'Barcode' AND @isDescending =1 THEN pro.Barcode END DESC, CASE WHEN @orderBy = 'Barcode' AND @isDescending =0 THEN pro.Barcode END ASC, CASE WHEN @orderBy = 'ProType' AND @isDescending =1 THEN pt.[ProductTypeName] END DESC, CASE WHEN @orderBy = 'ProType' AND @isDescending =0 THEN pt.[ProductTypeName] END ASC, CASE WHEN @orderBy = 'WSellingPrice' AND @isDescending =1 THEN pro.[SellingPrice_Wholesale] END DESC, CASE WHEN @orderBy = 'WSellingPrice' AND @isDescending =0 THEN pro.[SellingPrice_Wholesale] END ASC, CASE WHEN @orderBy = 'RSellingPrice' AND @isDescending =1 THEN pro.[SellingPrice_Retail] END DESC, CASE WHEN @orderBy = 'RSellingPrice' AND @isDescending =0 THEN pro.[SellingPrice_Retail] END ASC OFFSET ((@pageNumber - 1) * @rowsPerPage) ROWS FETCH NEXT @rowsPerPage ROWS ONLY END
0 Comments