Beginner's Guide to Create GridView in ASP.Net MVC 5

Introduction

 

In this post, we will be seeing how we can create a GridView in ASP.NET MVC, like we have in ASP.NET web form. There are many third party plugins, written in jQuery available for both server side and client side. These plugins provide all the essential functionality that we have in web forms, including searching, sorting, and paging etc. It totally depends on the requirements of the specific application if the search is needed at client side or server side.

 

You can download the source code from this link. Some of the libraries and plugins are:

• Grid.Mvc
• MVCGrid.NET
• PagedList.MVC
• jQuery.Grid
• jQuery Grid for ASP.NET MVC
• jQuery DataTables

These all have their pros and cons, but personally, I have found jQuery datatables to be a good choice. It is highly flexible. It supports pagination, instant-search, and multi-column ordering. It also supports almost all the data sources to which it can be bound. Some of them are:

• DOM
• JavaScript
• AJAX
• Server-side processing

One of the best options I like in it is that it supports both client side searching, pagination, sorting etc, but it also provides the option to have server side processing, as there can be a case where we have too much data in database. In that case, the client side paging wouldn’t be a good option. Just think, millions of rows in a table and if they are bound to it using client side pagination, it will make our page unresponsive due to a high amount of rows processing and html rendering.

 

We will first see an example of how we can implement it, using client side processing. So, let’s get started. We will have a working Grid with searching, sorting, and paging at the end of the post, which will look like:

 

 

First of all, create a database and table that we will be using in this post. Open SQL Management Studio and run the following script:

CREATE DATABASE [GridExampleMVC]
GO
CREATE TABLE [dbo].[Assets] (
    [AssetID]                   UNIQUEIDENTIFIER NOT NULL,
    [Barcode]                   NVARCHAR (MAX)   NULL,
    [SerialNumber]              NVARCHAR (MAX)   NULL,
    [FacilitySite]              NVARCHAR (MAX)   NULL,
    [PMGuide]                   NVARCHAR (MAX)   NULL,
    [AstID]                     NVARCHAR (MAX)   NOT NULL,
    [ChildAsset]                NVARCHAR (MAX)   NULL,
    [GeneralAssetDescription]   NVARCHAR (MAX)   NULL,
    [SecondaryAssetDescription] NVARCHAR (MAX)   NULL,
    [Quantity]                  INT              NOT NULL,
    [Manufacturer]              NVARCHAR (MAX)   NULL,
    [ModelNumber]               NVARCHAR (MAX)   NULL,
    [Building]                  NVARCHAR (MAX)   NULL,
    [Floor]                     NVARCHAR (MAX)   NULL,
    [Corridor]                  NVARCHAR (MAX)   NULL,
    [RoomNo]                    NVARCHAR (MAX)   NULL,
    [MERNo]                     NVARCHAR (MAX)   NULL,
    [EquipSystem]               NVARCHAR (MAX)   NULL,
    [Comments]                  NVARCHAR (MAX)   NULL,
    [Issued]                    BIT              NOT NULL,
    CONSTRAINT [PK_dbo.Assets] PRIMARY KEY CLUSTERED ([AssetID] ASC)
)
GO

The complete SQL script file is attached in the source code. So, you can use it to create the database and table with sample data.

 

Now, create a new ASP.NET MVC 5 web application. Open Visual Studio 2015. Go to File >> New >> Project.

 

 

 

From the dialog, navigate to the Web and select ASP.NET Web Application project and click OK.

 

 

From Templates, select MVC. Check the unit tests if you are writing the unit tests as well, for your implementations. Click OK.

 

Our project is created with basic things in place for us. Now, we will start by creating the database context class because we will be using Entity Framework for the Data Access.

 

First of all, we need to create Model for the Asset table which we will be used for retrieving the data, using ORM.

 

In Model folder, create a new class named as Asset:

using System.ComponentModel.DataAnnotations;

namespace GridExampleMVC.Models
{
    public class Asset
    {
        public System.Guid AssetID { get; set; }

        [Display(Name = "Barcode")]
        public string Barcode { get; set; }

        [Display(Name = "Serial-Number")]
        public string SerialNumber { get; set; }

        [Display(Name = "Facility-Site")]
        public string FacilitySite { get; set; }

        [Display(Name = "PM-Guide-ID")]
        public string PMGuide { get; set; }

        [Required]
        [Display(Name = "Asset-ID")]
        public string AstID { get; set; }

        [Display(Name = "Child-Asset")]
        public string ChildAsset { get; set; }

        [Display(Name = "General-Asset-Description")]
        public string GeneralAssetDescription { get; set; }

        [Display(Name = "Secondary-Asset-Description")]
        public string SecondaryAssetDescription { get; set; }
        public int Quantity { get; set; }

        [Display(Name = "Manufacturer")]
        public string Manufacturer { get; set; }

        [Display(Name = "Model-Number")]
        public string ModelNumber { get; set; }

        [Display(Name = "Main-Location (Building)")]
        public string Building { get; set; }

        [Display(Name = "Sub-Location 1 (Floor)")]
        public string Floor { get; set; }

        [Display(Name = "Sub-Location 2 (Corridor)")]
        public string Corridor { get; set; }

        [Display(Name = "Sub-Location 3 (Room No)")]
        public string RoomNo { get; set; }

        [Display(Name = "Sub-Location 4 (MER#)")]
        public string MERNo { get; set; }

        [Display(Name = "Sub-Location 5 (Equip/System)")]
        public string EquipSystem { get; set; }

        public string Comments { get; set; }

        public bool Issued { get; set; }

    }
}

Now, Navigate to Models folder from Solution Explorer and open IdenityModels.cs file. We will add a property for the Asset table in the database context which will be the Entity Framework representation of Asset table that we created using the script. Add new property in the ApplicationDbContext class:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("DefaultConnection", throwIfV1Schema: false)
    {
    }

    public DbSet<Asset> Assets { get; set; }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }
}

The above is the default Entity Framework settings for ASP.NET identity 2.0. We are extending it with our own tables, for which we have added new DbSet to Asset table.

 

Now, add an empty controller in Controllers folder, named as AssetController. We will be using this for all the Asset related work. Here is how it should look:

public class AssetController : Controller
{
    // GET: Asset
    public ActionResult Index()
    {
        return View();
    }
}

Now, we will install the jQuery datatables that we will be using to build the Grid. Go to Tools >> NuGet Package Manager >> Manage Nuget Packages for Solution and click it.

 

 

The package manager will get opened and by default it will be displaying the installed nuget packages in your solution. Click the browser button and then search for jQuery datatables package. Select it and check the projects of the solution in which you want to install this package. In our case, we are installing it in GridExampleMVC web project only as per requirement. Then, press the install button.

 

 

 

Visual Studio will prompt you to tell that it is going to modify the solution. You will have to press OK to continue the installation of the package.

After the nuget package is installed successfully, we need to include the necessary JS and CSS of jQuery datatables in the View where we will use it. For that, we have to register the jQuery datatables. Open the BundleConfig.cs file located in App_Start folder and add the following code for CSS and JS files, at the end:

bundles.Add(new ScriptBundle("~/bundles/datatables").Include(
"~/Scripts/DataTables/jquery.dataTables.min.js",
"~/Scripts/DataTables/dataTables.bootstrap.js"));

bundles.Add(new StyleBundle("~/Content/datatables").Include(
"~/Content/DataTables/css/dataTables.bootstrap.css"));

After registering the scripts and style for datatables, we need to add them in our master layout which is by default _Layout.cshtml located in Views >> Shared as defined in the _ViewStart.cshtml, located in the same location.

 

 

Before writing the controller code, we need to configure the connection string for Entity Framework that will be used to connect databases when it will be doing database operations i.e. running queries. So, our connection string should be pointing to a valid data source, so that our application won’t break when we run it.

 

For that, open web.config and provide the connection string for the database. In config file, you will find the  connectionStrings under configurations node. You will need to modify the connection string in that node according to your system. In my case, it looks like:

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=GridExampleMVC;Integrated Security=True;MultipleActiveResultSets=true" providerName="System.Data.SqlClient" />
</connectionStrings>

Now, in controller, add a property for database context that we will be using for querying the database.

private ApplicationDbContext _dbContext;

public ApplicationDbContext DbContext
{
    get
    {
        return _dbContext ?? HttpContext.GetOwinContext().Get<ApplicationDbContext>();
    }
    private set
    {
        _dbContext = value;
    }

}

This property will be used to query the database with Entity Framework in all actions of the controller wherever needed.

 

Now, in the index action, we will simply fetch all the rows of the table and pass it to the View:

public ActionResult Index()
{
    return View(DbContext.Assets.ToList());
}

Our complete controller class code now looks like:
using GridExampleMVC.Models;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Microsoft.AspNet.Identity.Owin;


namespace GridExampleMVC.Controllers
{
    public class AssetController : Controller
    {

        private ApplicationDbContext _dbContext;

        public ApplicationDbContext DbContext
        {
            get
            {
                return _dbContext ?? HttpContext.GetOwinContext().Get<ApplicationDbContext>();
            }
            private set
            {
                _dbContext = value;
            }

        }

        public AssetController()
        {

        }

        public AssetController(ApplicationDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        // GET: Asset
        public ActionResult Index()
        {
            return View(DbContext.Assets.ToList());
        }
    }
}

Here comes the View part now. Here, we will write the code about how it should render as html. So, create a View with Template Empty (Without Model) for the Index action and add the following code in it:

@model IEnumerable< GridExampleMVC.Models.Asset>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-primary list-panel" id="list-panel">
            <div class="panel-heading list-panel-heading">
                <h1 class="panel-title list-panel-title">Assets</h1>
            </div>
            <div class="panel-body">
                <table id="assets-data-table" class="table table-striped table-bordered" style="width:100%">
                    <thead>
                        <tr>
                            <th>Bar Code</th>
                            <th>Manufacturer</th>
                            <th>Model Number</th>
                            <th>Building</th>
                            <th>Room No</th>
                            <th>Quantity</th>
                        </tr>
                    </thead>
                    <tbody>
                        @foreach (var asset in Model)
                        {
                            <tr>
                                <td>@asset.Barcode</td>
                                <td>@asset.Manufacturer</td>
                                <td>@asset.ModelNumber</td>
                                <td>@asset.Building</td>
                                <td>@asset.RoomNo</td>
                                <td>@asset.Quantity</td>
                            </tr>
                        }
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

@section Scripts
{

 <script type="text/javascript">
     $(document).ready(function () {

         $('#assets-data-table').DataTable();
     });
    </script>

}

Now, run the application and you will see a Grid with sorting, searching, and filtering available in it. But, there is one problem -  it is processed on client side. All the data is rendered by View when action is called, which may make page performance slow or increase the page load time if there is a large number of rows coming.

 

We will be seeing in another post, how we can make it better, using server side paging, sorting, and filtering which surely is a better approach when we have huge data set.