How to Handle JSON Data with aspnet MVC and JQuery

Today I am going to explain about how to get JSON data with Asp.Net MVC to make ajax call using jquery. As we know JSON is very light weight as compare to xml or other datasets. So, in this article, I will create a blog system as for demo where first you will bind the DropDownList with blog categories and on select on individual category, respective blogs details will be populated. For this demonstration, I have used Code First approach.

To create new Asp.Net MVC application, open Visual Studio 2015/2013. Go to File menu and select New and then choose New Project. It will display the following New Project Windows where you can choose different types for project. So, from the right panel you need to choose Templates < Visual C# and then select Web.

After that from the left panel, you need to choose Asp.Net Web Application. Give the suitable name for the project as “JSONWithAspNetMVCExample” and click to OK.

After clicking on OK, It will open other windows where can be choose different templates for Asp.Net applications. So, here we need to go with MVC template and click to OK.

Create Entity and DataContext Class

As in this article, we are using two entities to make blog system. So, I am using two entity and these are category and blog. So, basically all the category will display inside the DropDownList and based on DropDownList value selection, blog details will be bind with html table using JQuery. So, there are two entities classes required here.

Blog.cs

Following is blog class where properties are defined. I have used Table attribute with class name and it is because for this it will take same name and a table will be created inside the database when you run the application first. Since we are using Code First approach where model or entity creates first and on the basis database and tables are generated.

using System;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

namespace JsonWithAspNetMVCExample.Models
{
    [Table("NextPosts")]
    public class Blog
    {
        [Key]
        public int PostId { get; set; }

        [MaxLength(500)]
        [Column(TypeName = "varchar")]
        public string PostTitle { get; set; }

        [MaxLength(1000)]
        [Column(TypeName = "text")]
        public string ShortPostContent { get; set; }

        [Column(TypeName = "text")]
        public string FullPostContent { get; set; }

        [MaxLength(255)]
        public string MetaKeywords { get; set; }

        [MaxLength(500)]
        public string MetaDescription { get; set; }
        public DateTime PostAddedDate { get; set; }

        public int CategoryId { get; set; }

        //public virtual int CategoryId { get; set; }
        //[ForeignKey("CategoryId")]
        //public virtual Category Categories { get; set; }
    }
}

Category.cs

Following is the category model, where all the properties have defined for blog's category.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace JsonWithAspNetMVCExample.Models
{
    [Table("NextCategories")]
    public class Category
    {
        [Key]
        [Required(ErrorMessage = "Category is required")]
        public int CategoryId { get; set; }

        [MaxLength(25)]
        public string CategoryName { get; set; }

    }
}

So, it is time to create data access classes for Code First Approach. I have given the name it to "BlogContext" and inside this class I have created DbSet for those classes which are participating in blog application. I have also given here the name of database connection [testconnection].

using System.Data.Entity;

namespace JsonWithAspNetMVCExample.Models
{
    public class BlogContext : DbContext
    {
        public BlogContext() : base("testConnection")
        {
        }
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Category> Categories { get; set; }
    }
}

Create Database Connection

Now first I am going to create connection string for database access which is basically inside the web.config. I have use these name only for testing purpose; you can change it as per your convenience. Be sure before running the application that you have made changes in user name and password as per your sql server.

<connectionStrings>
    <add name="testConnection" connectionString="Data Source=DEL9043B\SQLEXPRESS2012;database = demo; uid=sa; password=yourpassword"
      providerName="System.Data.SqlClient" />
</connectionStrings>

Create Blog Controller

When user request for the particular page in Asp.Net MVC, it first goes to controller and as per routing configuration, controller decides which action needs to be executed. So, this time, I am going to create a new controller as name "BlogController".

To create controller, right click on controller folder from solution and choose Add and then Controller. It will open a popup window where you can provide the name for the controller and click to Add.

Make changes in BlogController class as following to get categories data as well as blogs data based on category value selection from the database. As you can see with following code I have used JsonResult GetBlogDetailByCategoryID(int categoryId) which is returning Json Result.

using JsonWithAspNetMVCExample.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;

namespace JsonWithAspNetMVCExample.Controllers
{
    public class BlogController : Controller
    {
        BlogContext db = null;
        // GET: Blog
        public BlogController()
        {
            db = new BlogContext();
        }
        public ActionResult Index()
        {
            List<SelectListItem> blogCategories = new List<SelectListItem>();
            blogCategories.Add(new SelectListItem { Text = "Select Category", Value = "0", Selected = true });
            var categories = db.Categories.ToList();
            foreach (var c in categories)
            {
                blogCategories.Add(new SelectListItem { Text = c.CategoryName, Value = Convert.ToString(c.CategoryId) });
            }
            ViewBag.CategoryList = blogCategories;
            return View();
        }
        public JsonResult GetBlogDetailByCategoryID(int categoryId)
        {
            List<Blog> blogs = new List<Blog>();
            blogs = db.Blogs.Where(x => x.CategoryId == categoryId).Take(5).ToList();
           
            return Json(blogs, JsonRequestBehavior.AllowGet);
        }

    }
}

I am using Index page where all the categories will bind at the time of loading. So, I have used  @Html.DropDownList("CategoryList") which will bind the ViewBag categories data on page load and show all the cateogries inside DropDownList.

To display blogs details which is belongs to selected category in DropDownList, I am making a ajax call which will directly hit to GetBlogDetailByCategoryID action  method on BlogController and get appropriate data and bind with html table.

<script type="text/javascript">
    $(document).ready(function () {
        $("#CategoryList").change(function () {
            $.ajax({
                type: 'GET',
                url: '@Url.Action("GetBlogDetailByCategoryID")',
                datatype: JSON,
                data: { 'categoryId': $("#CategoryList").val() },
                success: function (data) {
                    $('#blogTable tbody').empty();

                    $.each(data, function (i, item) {
                        var rows = "<tr>"
                + "<td>" + item.PostId + "</td>"
                + "<td>" + item.PostTitle + "</td>"
                + "<td>" + item.ShortPostContent + "</td>"
                + "<td>" + item.MetaDescription + "</td>"
                + "</tr>";
                        $('#blogTable tbody').append(rows);
                    });
                },
                error: function (data) { }
            });
        });
    });
</script>

Index.html

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<script src="https://code.jquery.com/jquery-2.2.4.min.js" integrity="sha256-BbhdlvQf/xTY9gja0Dq3HiwQF8LaCRTXxZKRutelT44=" crossorigin="anonymous"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $("#CategoryList").change(function () {
            $.ajax({
                type: 'GET',
                url: '@Url.Action("GetBlogDetailByCategoryID")',
                datatype: JSON,
                data: { 'categoryId': $("#CategoryList").val() },
                success: function (data) {
                    $('#blogTable tbody').empty();

                    $.each(data, function (i, item) {
                        var rows = "<tr>"
                + "<td>" + item.PostId + "</td>"
                + "<td>" + item.PostTitle + "</td>"
                + "<td>" + item.ShortPostContent + "</td>"
                + "<td>" + item.MetaDescription + "</td>"
                + "</tr>";
                        $('#blogTable tbody').append(rows);
                    });
                },
                error: function (data) { }
            });
        });
    });
</script>
<style type="text/css">
    .zui-table {
        border: solid 1px #DDEEEE;
        border-collapse: collapse;
        border-spacing: 0;
        width:100%;
        font: normal 13px Arial, sans-serif;
    }

        .zui-table thead th {
            background-color: #DDEFEF;
            border: solid 1px #DDEEEE;
            color: #336B6B;
            padding: 10px;
            text-align: left;
        }

        .zui-table tbody td {
            border: solid 1px #DDEEEE;
            color: #333;
            padding: 10px;
        }

    .zui-table-rounded {
        border: none;
    }

        .zui-table-rounded thead th {
            background-color: #CFAD70;
            border: none;            
            color: #333;
        }

            .zui-table-rounded thead th:first-child {
                border-radius: 10px 0 0 0;
            }

            .zui-table-rounded thead th:last-child {
                border-radius: 0 10px 0 0;
            }

        .zui-table-rounded tbody td {
            border: none;
            border-top: solid 1px #957030;
            background-color: #EED592;
        }

        .zui-table-rounded tbody tr:last-child td:first-child {
            border-radius: 0 0 0 10px;
        }

        .zui-table-rounded tbody tr:last-child td:last-child {
            border-radius: 0 0 10px 0;
        }
</style>
<table>
    <tr>
        <td>
            <h2> Get JSON Data with Asp.Net MVC</h2>          
            <br />
        </td>
    </tr>
    <tr>
        <td>
            Select Category : @Html.DropDownList("CategoryList")
        </td>
    </tr>
</table>
<br />
<table id="blogTable" class="zui-table zui-table-rounded">
    <thead>
        <tr>
            <th>PostId</th>
            <th>Title</th>
            <th>Full Content</th>
            <th>Meta Description</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

When you run this application first time, as you know we are using Code First Approach and in this database and tables will be created automatically. So, when you will check the database there will be a test database has created with two tables as NextCategories and NextPosts as following image because these name has defined in model class using table attributes.

These tables are empty so you can run following scripts to insert dummy data for both tables. 

Use Test
Go

-- Insert Records for categories

INSERT INTO NextCategories VALUES ('CSharp')
INSERT INTO NextCategories VALUES ('MVC')
INSERT INTO NextCategories VALUES ('Asp.Net')
INSERT INTO NextCategories VALUES ('HTML')
INSERT INTO NextCategories VALUES ('AngularJS')

-- Insert Records for blogs

INSERT INTO NextPosts VALUES ('CSharp Title 1', 'CSharp Short Description 1','CSharp Long Description 1', 'CSharp Keyword 1', 'CSharp Description 1', GETDATE(), 1 )
INSERT INTO NextPosts VALUES ('MVC Title 1', 'MVC Short Description 1','MVC Long Description 1', 'MVC Keyword 1', 'MVC Description 1', GETDATE(), 2 )
INSERT INTO NextPosts VALUES ('MVC Title 2', 'MVC Short Description 2','MVC Long Description 2', 'MVC Keyword 2', 'MVC Description 2', GETDATE(), 2 )
INSERT INTO NextPosts VALUES ('AngularJS Title 1', 'AngularJS Short Description 1','AngularJS Long Description 1', 'AngularJS Keyword 1', 'AngularJS Description 1', GETDATE(), 5 )
INSERT INTO NextPosts VALUES ('HTML Title 1', 'HTML Short Description 1','HTML Long Description 1', 'HTML Keyword 1', 'HTML Description 1', GETDATE(), 4 )
INSERT INTO NextPosts VALUES ('CSharp Title 2', 'CSharp Short Description 2','CSharp Long Description 2', 'CSharp Keyword 2', 'CSharp Description 2', GETDATE(), 1 )
INSERT INTO NextPosts VALUES ('HTML Title 2', 'HTML Short Description 2','HTML Long Description 2', 'HTML Keyword 2', 'HTML Description 2', GETDATE(), 4 )
INSERT INTO NextPosts VALUES ('Asp.Net Title 1', 'Asp.Net Short Description 1','Asp.Net Long Description 1', 'Asp.Net Keyword 1', 'Asp.Net Description 1', GETDATE(), 3)
INSERT INTO NextPosts VALUES ('HTML Title 3', 'HTML Short Description 3','HTML Long Description 3', 'HTML Keyword 3', 'HTML Description 3', GETDATE(), 4 )
INSERT INTO NextPosts VALUES ('AngularJS Title 2', 'AngularJS Short Description 2','AngularJS Long Description 2', 'AngularJS Keyword 2', 'AngularJS Description 2', GETDATE(), 5 )
INSERT INTO NextPosts VALUES ('AngularJS Title 3', 'AngularJS Short Description 3','AngularJS Long Description 3', 'AngularJS Keyword 3', 'AngularJS Description 3', GETDATE(), 5 )

Now its time to run the application to see the output. So, to run the application, just press F5 and select any category. You will see that corresponding blogs will show below when you select CSharp as category from DropDownList.

If you select any other blog category then it will show corresponding blogs details as per following image.

Conclusion:

So, today we learned how to create an Asp.Net MVC appliction and bind the DropDownList and get JSON result from database and bind it to html table using JQuery and Code First Approach.

I hope this post will help you. Please put your feedback using comment which helps me to improve myself for next post. If you have any doubts please ask your doubts or query in the comment section and If you like this post, please share it with your friends. Thanks