WebGrid Custom Paging Links To Avoid Load On Server Using Stored Procedure And ASP.NET MVC

Introduction

In ASP.NET, we use GridView for fetching the data and showing the output. Also, we implement CRUD operationss using GridView. We can do the same  implementation in ASP.NET MVC using WebGrid.

Link For More References

• Part 1
  
• Part 2
  

Description

I will show you how to fetch the data dynamically from the database and bind to the WebGrid control, using the stored procedure in MVC 4.0. In this blog, today, I will show you how to write code in ASP.NET MVC for implementation of WebGrid Control using Static Data. In later sessions, I will show you the process of using GridView Dynamically, that means using the SQL Server data source.

Link To Source Code

 

Click Here>> 

 

Important Code Sections To Follow

Code ref of GridviewController.cs,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using SatyaWebGridCustomPagingLinks.Models;
using System.Web.Helpers;

namespace SatyaWebGridCustomPagingLinks.Controllers
{
    public class GridviewController : Controller
    {
        //
        // GET: /Gridview/

        public ActionResult Index(int? page)
        {
            List<modeldata> lmd = new List<modeldata>();
            //creating list of model.

            DataSet ds = new DataSet();

            Connection.Connection con = new Connection.Connection();

            //The connection namespace as we mentioned earlier .
            //namespace Gridsample.Connection.

            // connection to getdata.

            ds = con.mydata();

            // fill dataset


            foreach (DataRow dr in ds.Tables[0].Rows)

            // loop for adding add from dataset to list<modeldata>
            {
                lmd.Add(new modeldata
                {
                    // adding data from dataset row in to list<modeldata>
                    SM_StudentID = Convert.ToInt64(dr["SM_StudentID"]),
                    SM_Name = dr["SM_Name"].ToString()
                });
            }
            return View(lmd.ToList());
        }

        public ActionResult EfficientWay()
        {
            return View();
        }

        [HttpGet]
        public JsonResult EfficientPaging(int? page)
        {
            List<modeldata> lmd = new List<modeldata>();
            //creating list of model.

            DataSet ds = new DataSet();

            Connection.Connection con = new Connection.Connection();

            //The connection namespace as we mentioned earlier .
            //namespace Gridsample.Connection.

            // connection to getdata.

            ds = con.mydata();

            // fill dataset


            foreach (DataRow dr in ds.Tables[0].Rows)

            // loop for adding add from dataset to list<modeldata>
            {
                lmd.Add(new modeldata
                {
                    // adding data from dataset row in to list<modeldata>
                    SM_StudentID = Convert.ToInt64(dr["SM_StudentID"]),
                    SM_Name = dr["SM_Name"].ToString()
                });
            }
            int skip = page.HasValue ? page.Value - 1 : 0;
            var data = lmd.OrderBy(o => o.SM_StudentID).Skip(skip * 5).Take(5).ToList();
            var grid = new WebGrid(data);
            var htmlString = grid.GetHtml(tableStyle: "webGrid",
                                          headerStyle: "header",
                                          alternatingRowStyle: "alt",
                                          htmlAttributes: new { id = "DataTable" });
            return Json(new
            {
                Data = htmlString.ToHtmlString(),
                Count = lmd.Count() / 5
            }, JsonRequestBehavior.AllowGet);
        }
    }
}

Code description

The grid.Pager method sets the paging for the WebGrid. The problem is when you page through the data, all the data is returned. If you try to limit the data being returned, the problem you’ll encounter because you’re only returning a subset of the data is the WebGrid thinks there’s only that amount of data to display, so the paging links will disappear! Not good! So to show you what this looks like, I’m going to only return the first 5 records for the WebGrid, because I only want to display 5 records per page. Here’s my updated action method. 

public ActionResult Index(int? page)
        {
return View(lmd.Take(5).ToList());
}

Now because I’m returning only 5 instead of 10 records, the paging has disappeared from the WebGrid!

The WebGrid’s paging is it needs the total count of data that the grid is supposed to display. If you limit the WebGrid to only the records you want displayed, you limit the total count and the paging doesn’t work. To fix this problem you need to separate the two. One set of data for the grid and a separate piece of data that tells the WebGrid how many paging links to display. Unfortunately the paging object is locked, so I had to make my own. create an action method that returns the WebGrid as one object, and a total record count as another object and wrap both of them in a JSON object. Here’s the action method.

The JSON being returned stores two object

• Data – this stores the data only needed for the grid. It utilizes the Skip and Take methods to limit the data being returned. It is returning the WebGrid as HTML. This ensures this code will replicate what the WebGrid would look like if we were declaring it in the Razor mark-up.
• Count – this stores the total records. This will be used for creating the paging links.
  

iI you page through the data, the page you’re requesting will be sent as a parameter to the action method, and only that data will be fetched from the server.

int skip = page.HasValue ? page.Value - 1 : 0;
            var data = lmd.OrderBy(o => o.SM_StudentID).Skip(skip * 5).Take(5).ToList();
            var grid = new WebGrid(data);
            var htmlString = grid.GetHtml(tableStyle: "webGrid",
                                          headerStyle: "header",
                                          alternatingRowStyle: "alt",
                                          htmlAttributes: new { id = "DataTable" });
            return Json(new
            {
                Data = htmlString.ToHtmlString(),
                Count = lmd.Count() / 5
            }, JsonRequestBehavior.AllowGet);
        }

Code ref of Index.cshtml

<body>
    @{
        var grid = new WebGrid(Model, canPage: true, rowsPerPage: 4);
        grid.Pager(WebGridPagerModes.NextPrevious);
        @grid.GetHtml(tableStyle: "table",
                htmlAttributes: new { id = "DataTable" },
             headerStyle: "header", //applying style.
             footerStyle: "grid-footer", //applying style.
             alternatingRowStyle: "webgrid-alternating-row",
             rowStyle: "webgrid-row-style",
                columns: grid.Columns(
                    grid.Column("SM_StudentID"),
                    grid.Column("SM_Name")
        ));
    }
</body>

Code description

In this section the two columns are mentioned. 

grid.Column("SM_StudentID"),
grid.Column("SM_Name")

Code ref of EfficientWay.cshtml

<body>
    <script type="text/javascript" src="../../Scripts/jquery-1.10.2.js"></script>
    <script type="text/javascript" src="../../Scripts/Pager.js"></script>
</body>

Code Description

Pager.js has the details about footer section that calls the EfficientPaging action and displays the returned WebGrid and creates the paging links.

OUTPUT

Custom Paging Link

 

 

Put Breakpoints and Debug To See the Page No. Using C#



Note

Creating your own paging links avoids repeated hits on the server which was creating a load on the server.

Summary

• How to bind the WebGrid to the database and fetch the data.
• Avoids hits on server by using Efficient Paging with WebGrid.