CRUD Operations In JTable Using MVC

Introduction

In this article, we will discuss CRUD (Create, Read, Update, Delete) Operations functionality In jTable Using MVC and entity framework. I will use visual studio 2012 framework and SQL server 2008 R2 for this demo.

So, before opening visual studio let’s create and populate student table using following script.

Create table student
(
StudentId varchar(50),
RollNo varchar(50),
FullName varchar(50),
Gender varchar(10),
City varchar(50),
)

Insert into student values('016a7375-6da4-471b-9182-e1a122828d9e',1,'Sara','Female','Chennai')
Insert into student values('048e49c9-8cb0-4b18-8afe-9c2f89e0ddf1',2,'David','Male','Sydeny')
Insert into student values('04e4d633-f0af-4269-be06-1b4a5588ae0d',3,'Dora','Female','New York')
Insert into student values('071e0c85-50eb-4283-8d7f-1b055990a944',4,'Maya','Female','London')
Insert into student values('0c868ccb-382d-4349-a338-97210a17d254',5,'Vikram', 'Male','Mumbai')
Insert into student values('17f95a81-934d-43fd-b780-4afd8509d8e0',6,'Nikhil','Male','Delhi')

Let’s start for this demo step by step using MVC.

Step 1: Create MVC project, by using following steps.

Open Visual Studio.
Click on File -> New -> Project.
In this project, I am using C# as a programming language, but you can also choose VISUAL BASIC if you know syntaxes of VB.
Inside Visual C# -> Web -> Project -> Select Asp.Net MVC 3, 4 or 5 -> Name your project -> JtableDemo -> Select Ok

Now one more dialog box will open -> select ‘Internet Application’ as a project template. -> OK.

Step 2: Install ‘jTable’ and ‘entity framework’ from NuGet Package Manager by using following steps

Open solution explorer -> Right click on your project name and click on Manage Nuget Packages

Go to online tab and Search for jTable and click on install button.

Now search for Entity framework and click on install button.

Once you install jtbale and entity framework. You will find them inside your project folder.

Step 3 : Add new folder in your project for entities.

Right click on your project name -> click add -> click New Folder -> rename that new folder to ‘Entities’

Once you created ‘Entities’ folder add one class file inside that folder -> name that class file -> Student.cs

Copy and paste following code inside student class file.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace JtableDemo.Entities
{
    [Table("student")]
    public class Student
    {
        public string StudentId { get; set; }
        public string RollNo { get; set; }
        public string FullName { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }      
    }
}

Now add one more class file inside Entities folder, name that file name -> ‘StudentContext’ -> Copy following code inside studentcontext class file.

StudentContext.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using JtableDemo.Entities;


namespace JtableDemo.Models
{
    public class StudentContext : DbContext
    {
        public DbSet<Student> Students { get; set; }
    }
}

Step 4 : Add new controller using following steps

Right click on controller folder. Name your controller -> StudentController -> Click on Add button.

Now open StudentController and add following 4 methods in your controller.

using JtableDemo.Entities;
using JtableDemo.Models;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace JtableDemo.Controllers
{
    public class StudentController : Controller
    {
        //
        // GET: /Student/

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

GetStudentDetails Action

public JsonResult GetStudentDetails()
       {
           StudentContext db = new StudentContext();
           try
           {
               List<Student> students = new List<Student>();
               students = db.Students.ToList();
               return Json(new { Result = "OK", Records = students }, JsonRequestBehavior.AllowGet);
           }
           catch (Exception ex)
           {
               return Json(new { Result = "ERROR", Message = ex.Message });
           }
       }

Create Action

[HttpPost]
       public JsonResult Create(Student Model)
       {
           StudentContext db = new StudentContext();
           try
           {
               Model.StudentId = Guid.NewGuid().ToString();

               db.Students.Add(Model);
               db.SaveChanges();
               return Json(new { Result = "OK", Records = Model }, JsonRequestBehavior.AllowGet);
           }
           catch (Exception ex)
           {
               return Json(new { Result = "ERROR", Message = ex.Message });
           }
       }

Edit Action

[HttpPost]
        public JsonResult Edit(Student Model)
        {
            StudentContext db = new StudentContext();
            try
            {
                db.Entry(Model).State = EntityState.Modified;
                db.SaveChanges();
                return Json(new { Result = "OK" }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                return Json(new { Result = "ERROR", Message = ex.Message });
            }
        }

Delete Action

[HttpPost]
        public JsonResult Delete(String StudentID)
        {
            StudentContext db = new StudentContext();
            try
            {
                Student students = db.Students.Find(StudentID);
                db.Students.Remove(students);
                db.SaveChanges();
                return Json(new { Result = "OK" }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                return Json(new { Result = "ERROR", Message = ex.Message });
            }
        }

Step 5 : Add view using following steps.

Open student controller -> Right Click on Index method.
Click on Add View Option.

Add following code inside your index.cshtml

@{  
    ViewBag.Title = "Student Details";  
}  
  
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<h2>Student Details</h2>  
  
<div id="StudentsTable"></div>  
  
@section scripts{  
    <!--Adding Theme for jTable Grid-->  
    <!--You can choose any type of theme from the themes folder-->  
    <link href="~/Scripts/jtable/themes/metro/blue/jtable.min.css" rel="stylesheet" />  
    @*<link href="~/Content/themes/base/jquery-ui.css" rel="stylesheet" />*@  
    <link href="http://jtable.org/Content/themes/metroblue/jquery-ui.css" rel="stylesheet" type="text/css" />  

    

    <script src="~/Scripts/jquery-ui-1.9.2.min.js"></script>  
  
    <!--Adding jTable Plugin-->  
    <script src="~/Scripts/jtable/jquery.jtable.min.js"></script>  
  
     
}  
<script type="text/javascript">

$(document).ready(function () {
    $('#StudentsTable').jtable({
        title: 'Students Detail',
        actions: {
            listAction: '/Student/GetStudentDetails',
            createAction: '/Student/Create',
            updateAction: '/Student/Edit',
            deleteAction: '/Student/Delete'
        },
        fields: {
            StudentId: {
                key: true,
                list: false
            },
            RollNo: {
                title: 'Roll Number',
                width: '15%'
            },
           FullName: {
                title: 'Student Name',
                width: '45%'
            },
            Gender: {
                title: 'Gender',
                width: '15%',
            },
            City: {
                title: 'City',
                width: '15%',
            }
           
        }
    });
    $('#StudentsTable').jtable('reload');
});
    </script>

Step 6 : Web.config

Replace your connectionstring with following code inside your web.config file

<connectionStrings>
    <add name="StudentContext" connectionString="data source=.;initial catalog=Sample;integrated security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>