Microsoft hubs(8920128728): Full CRUD operation using datatables in ASP.NET MVC

Step - 1: Create New Project.

Go to File > New > Project > ASP.NET Web Application (under web) > Enter enter application name > select your project location > and then click on add button > It will brings up a new dialog window for select template > here I will select Empty template > checked MVC checkbox from Add folder and core referances for: > and then click on ok button.

Step-2: Add a Database.

Now I will create a database for our application. As this is a tutorial project, I will add a database in our applications here in the app_data folder.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create a table in our database.

In this example, I have added the table for store Employee information for implementing CRUD operation in Datatable.

double click on the database under app_data folder for open the database in server explorer > expand the database and Right click on Tables node > click on Add New Table > here we will write schema of the table for the table we want to create > now click on Update button for create the table and then again click on Update Database button.

Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >

Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

As I have already shown you how we can implement server-side paging, sorting, filtering functionality in Datatables, here we will only focus on CRUD functionality. So here we will show data in Datatables without implementing server-side paging, sorting, filtering functionality for the sake of simplicity.

Step-5: Create a Controller.

Go to Solution Explorer > Right Click on Controllers folder form Solution Explorer > Add > Controller > Enter Controller name > Select Templete "empty MVC Controller"> Add.

Here I have created a controller "HomeController"

Now we will add a view for Index action (which is already added in the home controller) where we will show data in Datatables.

Step-6: Add view for Index action.

Right Click on Action Method (here right click on Index action) > Add View... > Enter View Name > Select "Empty" under Template dropdown > > Add.
In this page, first of all, we need to add required the jQuery & CSS libraries. So we will add those required libraries in our application from NuGet package manager.
For add libraries from NuGet package manager
Go to solution explorer > Right Click on your project name > Manage NuGet Packages... > Search & select the resources you want to Add in your project > click Install button.
We will add following libraries from NuGet Package Manager

jQuery Library (I have downloaded jQuery 3.1.1)
jQuery.UI.Combined 1.12.1
jQuery.Validation 1.16.0
Microsoft.jQuery.Unobtrusive.Validation 3.2.3

HTML Code


@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css" />
    <link href="~/Content/themes/base/jquery-ui.min.css" rel="stylesheet" />
    <style>
        span.field-validation-error {
            color: red;
        }
    </style>
</head>
<body>
    <div style="width:90%; margin:0 auto" class="tablecontainer"> 
        <a class="popup btn btn-primary" href="/home/save/0" style="margin-bottom:20px; margin-top:20px;">Add New Employee</a>
        <table id="myDatatable">
            <thead>
                <tr>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Email ID</th>
                    <th>City</th>
                    <th>Country</th>
                    <th>Edit</th>
                    <th>Delete</th>
                </tr>
            </thead>
        </table>
    </div>
 
    <script src="~/Scripts/jquery-3.1.1.min.js"></script>
    <script src="~/Scripts/jquery.validate.min.js"></script>
    <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>
    <script src="~/Scripts/jquery-ui-1.12.1.min.js"></script>
 
    <script>
        $(document).ready(function () {
            var oTable = $('#myDatatable').DataTable({
                "ajax": {
                    "url" : '/home/GetEmployees',
                    "type" : "get",
                    "datatype" : "json"
                },
                "columns": [
                    { "data": "FirstName", "autoWidth": true },
                    { "data" : "LastName", "autoWidth" : true},
                    { "data": "EmailID", "autoWidth": true },
                    { "data": "City", "autoWidth": true },
                    { "data": "Country", "autoWidth": true },
                    {
                        "data": "EmployeeID", "width": "50px", "render": function (data) {
                            return '<a class="popup" href="/home/save/'+data+'">Edit</a>';
                        }
                    },
                    {
                        "data": "EmployeeID", "width": "50px", "render": function (data) {
                            return '<a class="popup" href="/home/delete/' + data + '">Delete</a>';
                        }
                    }
                ]
            })
            $('.tablecontainer').on('click', 'a.popup', function (e) {
                e.preventDefault();
                OpenPopup($(this).attr('href'));
            })
            function OpenPopup(pageUrl) {
                var $pageContent = $('<div/>');
                $pageContent.load(pageUrl, function () {
                    $('#popupForm', $pageContent).removeData('validator');
                    $('#popupForm', $pageContent).removeData('unobtrusiveValidation');
                    $.validator.unobtrusive.parse('form');
 
                });
 
                $dialog = $('<div class="popupWindow" style="overflow:auto"></div>')
                          .html($pageContent)
                          .dialog({
                              draggable : false,
                              autoOpen : false,
                              resizable : false,
                              model : true,
                              title:'Popup Dialog',
                              height : 550,
                              width : 600,
                              close: function () {
                                  $dialog.dialog('destroy').remove();
                              }
                          })
 
                $('.popupWindow').on('submit', '#popupForm', function (e) {
                    var url = $('#popupForm')[0].action;
                    $.ajax({
                        type : "POST",
                        url : url,
                        data: $('#popupForm').serialize(),
                        success: function (data) {
                            if (data.status) {
                                $dialog.dialog('close');
                                oTable.ajax.reload();
                            }
                        }
                    })
 
                    e.preventDefault();
                })
                $dialog.dialog('open');
            }
        })
    </script>
</body>
</html>

Step-7: Add a new method in HomeController.

Here I have added a method GetEmployees for fetch employees data from the database.


public ActionResult GetEmployees()
{
    using (MyDatabaseEntities dc = new MyDatabaseEntities())
    {
        var employees = dc.Employees.OrderBy(a => a.FirstName).ToList();
        return Json(new { data = employees }, JsonRequestBehavior.AllowGet);
    }
}

Now for doing CRUD operation, we will add some MVC action in our Home Controller.

Step-8: Add a new MVC Action Save (GET) in HomeController.

Here we will add a new MVC Action (GET Method) for getting the form for add/update employee information.


[HttpGet]
public ActionResult Save(int id)
{
    using (MyDatabaseEntities dc = new MyDatabaseEntities())
    {
        var v = dc.Employees.Where(a => a.EmployeeID == id).FirstOrDefault();
        return View(v);
    }
}

Step-9: Add a partial view for that Save MVC action.

Here we will add a partial view for the save action as we will open the form in jQuery popup dialog window.


@model DatatableCRUD.Models.Employee
<h2>Save</h2>
@using (Html.BeginForm("save","home", FormMethod.Post, new { id= "popupForm" }))
{
    if (Model != null && Model.EmployeeID > 0)
    {
        @Html.HiddenFor(a=>a.EmployeeID)
    }
 
    <div class="form-group">
        <label>First Name</label>
        @Html.TextBoxFor(a=>a.FirstName,new { @class = "form-control" })
        @Html.ValidationMessageFor(a=>a.FirstName)
    </div>
    <div class="form-group">
        <label>Last Name</label>
        @Html.TextBoxFor(a => a.LastName, new { @class = "form-control" })
        @Html.ValidationMessageFor(a => a.LastName)
    </div>
    <div class="form-group">
        <label>Email</label>
        @Html.TextBoxFor(a => a.EmailID, new { @class = "form-control" })
        @Html.ValidationMessageFor(a => a.EmailID)
    </div>
    <div class="form-group">
        <label>City</label>
        @Html.TextBoxFor(a => a.City, new { @class = "form-control" })
        @Html.ValidationMessageFor(a => a.City)
    </div>
    <div class="form-group">
        <label>Country</label>
        @Html.TextBoxFor(a => a.Country, new { @class = "form-control" })
        @Html.ValidationMessageFor(a => a.Country)
    </div>
 
    <div>
        <input type="submit" value="Save" />
    </div>
}

Right click inside the save action > Add View.. > Enter view name > Select Empty (without model) from template dropdown > Check the Checkbox "Create as a partial view".

Step-10: Add an another MVC Action Save (POST) in HomeController.

Here we will add a another MVC Action (POST Method) for saving data to the database.


[HttpPost]
public ActionResult Save(Employee emp)
{
    bool status = false;
    if (ModelState.IsValid)
    {
        using (MyDatabaseEntities dc = new MyDatabaseEntities())
        {
            if (emp.EmployeeID > 0)
            {
                //Edit 
                var v = dc.Employees.Where(a => a.EmployeeID == emp.EmployeeID).FirstOrDefault();
                if (v != null)
                {
                    v.FirstName = emp.FirstName;
                    v.LastName = emp.LastName;
                    v.EmailID = emp.EmailID;
                    v.City = emp.City;
                    v.Country = emp.Country;
                }
            }
            else
            {
                //Save
                dc.Employees.Add(emp);
            }
            dc.SaveChanges();
            status = true;
        }
    }
    return new JsonResult { Data = new { status = status} };
}

Step-11: Add an another MVC Action Delete (GET) in HomeController.

Here we will add an another MVC Action (GET Method) for getting the confirm dialog window before deleting the selected employee.


[HttpGet]
public ActionResult Delete(int id)
{
    using (MyDatabaseEntities dc = new MyDatabaseEntities())
    {
        var v = dc.Employees.Where(a => a.EmployeeID == id).FirstOrDefault();
        if (v != null)
        {
            return View(v);
        }
        else
        {
            return HttpNotFound();
        }
    }
}

Step-12: Add a partial view for that Delete MVC action.

Right click inside the save action > Add View.. > Enter view name > Select Empty (without model) from template dropdown > Check the Checkbox "Create as a partial view".


@model DatatableCRUD.Models.Employee
 
<h2>Delete Employee</h2>
@using (Html.BeginForm("delete","home", FormMethod.Post,new { id= "popupForm" }))
{
    @Html.HiddenFor(a=>a.EmployeeID)
    <div class="form-group">
        <label>First Name</label>
        <p>@Model.FirstName</p>
    </div>
 
    <div class="form-group">
        <label>Last Name</label>
        <p>@Model.LastName</p>
    </div>
 
    <div class="form-group">
        <label>Email</label>
        <p>@Model.EmailID</p>
    </div>
 
    <div class="form-group">
        <label>City</label>
        <p>@Model.City</p>
    </div>
 
    <div class="form-group">
        <label>Country</label>
        <p>@Model.Country</p>
    </div>
 
    <div>
        <input type="submit" value="Delete" />
    </div>
}

Step-13: Add an another MVC Action Delete (POST) in HomeController.

Here in this MVC Action, we will write code for delete selected employee data from our database.


[HttpPost]
[ActionName("Delete")]
public ActionResult DeleteEmployee(int id)
{
    bool status = false;
    using (MyDatabaseEntities dc = new MyDatabaseEntities())
    {
        var v = dc.Employees.Where(a => a.EmployeeID == id).FirstOrDefault();
        if (v != null)
        {
            dc.Employees.Remove(v);
            dc.SaveChanges();
            status = true;
        }
    }
    return new JsonResult { Data = new { status = status} };
}

Step-14: Run Application.

We have done all the steps. Now it's time to run the application.

Microsoft hubs(8920128728)

Saturday, August 10, 2019

Full CRUD operation using datatables in ASP.NET MVC