Saturday, 16 September 2017

Delete multiple record from WebGrid in ASP.NET MVC using CheckBox

In this article, I am going to explain you how to delete multiple records from ASP.NET MVC WebGrid with the help of CheckBox and Delete button. In this tutorial, we will be discussing only deleting the records. Insert and update will discuss in later tutorials.

I am using visual studio 2013 ultimate, SQL-Server database and Dapper Micro ORM to map database tables to model entity. Below is step by step tutorial.

Creating Database Table

Below is script to create database table and insert some dummy records.

CREATE TABLE tbl_Employee
(
EmpID INT PRIMARY KEY IDENTITY,
Name VARCHAR(100),
Salary INT,
City VARCHAR(50),
Country VARCHAR(50)
)
GO
INSERT INTO tbl_Employee VALUES('Rahul',10000,'Mumbai','India')
INSERT INTO tbl_Employee VALUES('Deepak',15200,'Mumbai','India')
INSERT INTO tbl_Employee VALUES('Savio',15000,'Delhi','India')
INSERT INTO tbl_Employee VALUES('Yogesh',10500,'Mumbai','India')
INSERT INTO tbl_Employee VALUES('Sachin',17000,'Chennai','India')
INSERT INTO tbl_Employee VALUES('Ponting',80000,'Sydney','Australia')
INSERT INTO tbl_Employee VALUES('Josh',24463,'Perth','Australia')
INSERT INTO tbl_Employee VALUES('Johnson',24562,'Melbourne','Australia')
INSERT INTO tbl_Employee VALUES('Gimmy',32456,'Perth','Australia')
INSERT INTO tbl_Employee VALUES('Rex',68355,'Sydney','Australia')
INSERT INTO tbl_Employee VALUES('Rick',99999,'New York','USA')
INSERT INTO tbl_Employee VALUES('Savy',15465,'Chicago','USA')
INSERT INTO tbl_Employee VALUES('Todd',24562,'New York','USA')
INSERT INTO tbl_Employee VALUES('Mary',35485,'New York','USA')
INSERT INTO tbl_Employee VALUES('Mike',15245,'Chicago','USA')

Creating ASP.NET MVC Project

Next step is to create asp.net mvc project.
Go to FileNewProject. A new window will be open as shown below.
Now go to Web and select .NET Framework 4.5 and give project name and click on OK .

Creating asp.net mvc 5 application

Now new window will open as shown below.
Now Select Empty Template, check on MVC checkbox and click on OK.

Selecting asp.net mvc 5 empty template

Now, a new project will be created as shown below.

Asp.net mvc 5 folder structure

Installing Dapper Micro-ORM

Next step is to install Dapper Micro-ORM. Right click on References and go to Manage NuGet Packages as shown below.

Adding Dapper ORM using NuGet Packages

After that a new window will open as shown below. Search for Dapper ORM and install it.

Installing dapper orm

Creating Model

Now add a class file Employee.cs in Models folder. Add below properties to Employee class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
 
namespace WebGridDemo.Models
{
    public class Employee
    {
        public int EmpID { get; set; }
        public string Name { get; set; }
        public int Salary { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
    }
}

Now add a New Folder and rename as Repository to root directory of application. Add a class file EmployeeMaster.cs inside Repository folder. Create two methods GetAllEmployees and DeleteRecords inside EmployeeMaster class. GetAllEmployees method executes stored a procedure which returns IEnumerable of type Employee. DeleteRecords method uses in-line query to delete record by using EmpID.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Dapper;// add namespance
using WebGridDemo.Models;// add namespance
using System.Data.SqlClient;// add namespance
using System.Configuration;// add namespance
using System.Data; // add namespance

namespace WebGridDemo.Repository
{
  public class EmployeeMaster
  {
    public IEnumerable<Employee> GetAllEmployees()
    {
       //using stored procedure to fetch all the records
       using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ToString()))
       {
         try
         {
             var emp_list = con.Query<Employee>("usp_GetEmployeeDetails", null, null, true, 0, CommandType.StoredProcedure);
             return emp_list;
         }
         catch
         {
             return null;
         }
       }
    }
    public void DeleteRecords(int id)
    {
       //using inline sql query to delete records
       using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ToString()))
       {
         try
         {
             string query = "delete from tbl_Employee where EmpID=@EmpID";
             var abc = con.Execute(query, new { EmpID = id });
         }
         catch
         {
             //return null;
         }
       }
    }
  }
}

Below is the script for stored procedure to fetch data.

CREATE proc usp_GetEmployeeDetails
AS
BEGIN
 SELECT * FROM tbl_Employee
END

Adding Controller

Next step is to add controller to application. Add a new empty controller class named as HomeController and add two action methods as shown below. Index method is used to return list of employee to the Index view and DeleteRecords is post method used to delete the records.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebGridDemo.Models;// add namespace
using WebGridDemo.Repository; // add namespace

namespace WebGridDemo.Controllers
{
    public class HomeController : Controller
    {
        [HttpGet]
        public ActionResult Index()
        {
            EmployeeMaster obj = new EmployeeMaster();
            List<Employee> emplist = new List<Employee>();
            emplist = obj.GetAllEmployees().ToList();
            return View(emplist);
        }

        [HttpPost]
        public ActionResult DeleteRecords(string[] EmpIDs)
        {
            if (EmpIDs != null && EmpIDs.Length > 0)
            {
                foreach (var id in EmpIDs)
                {
                    EmployeeMaster obj = new EmployeeMaster();
                    obj.DeleteRecords(Convert.ToInt32(id));
                }
            }
            return RedirectToAction("Index");
        }
    }
}

Adding View

Next step is to add view. Right click inside index action method and add a new view named as Index and select empty model and add a View. Now open Index.cshtml view which is created inside Views/Home folder. Replace all code with below code.

@model IEnumerable<WebGridDemo.Models.Employee>

@{
    ViewBag.Title = "WebGrid Demo";
    var grid = new WebGrid(canSort: true, rowsPerPage: 5);
    grid.Bind(source: Model);
}

<style>
    .header_style {
        background-color: #055897;
        color: #ffffff;
    }

        .header_style a {
            color: #ffffff !important;
        }

    .table-pager > td {
        padding-top: 10px;
    }

        .table-pager > td > a {
            background-color: #f5f5f5;
            border: 1px solid #ccc;
            border-radius: 3px;
            padding: 3px 7px;
        }

            .table-pager > td > a:hover {
                background-color: #f0f0f0;
            }

    .webgrid-alternating-row {
        background-color: #EAF2D3;
        padding: 3px 7px 2px;
    }
</style>

<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/core.js" type="text/javascript"></script>

<h3 class="text-info">Delete Multiple Reords in WebGrid in MVC</h3>

<div class="test" style="overflow: scroll; width: auto;">
    <div style="width: auto">
        @using (Html.BeginForm("DeleteRecords", "Home", FormMethod.Post))
        {
            if (Model.Count() > 0)
            {
                @grid.GetHtml(
                tableStyle: "table table-responsive table-bordered",
                headerStyle: "header_style",
                rowStyle: "odd",
                alternatingRowStyle: "webgrid-alternating-row",
                footerStyle: "table-pager",
                firstText: "First",
                lastText: "Last",
                nextText: "Next",
                previousText: "Previous",
                columns: grid.Columns
                (
                grid.Column(format: @<text><input type="checkbox" name="EmpIDs" value="@item.EmpId" /></text>, header: "Select"),
                grid.Column("EmpId", "Employee ID"),
                grid.Column("Name", "Employee Name"),
                grid.Column("Salary", "Salary"),
                grid.Column("City", "City"),
                grid.Column("Country", "Country"),
                grid.Column(
                 "Action", format: (item) =>
                 {
                     var links =
                     Html.ActionLink("  ", "Edit", "Product", new { id = item.EmpID }, new { @class = "btn btn-default glyphicon glyphicon-edit" }).ToHtmlString() +
                     Html.ActionLink("  ", "Delete", "Product", new { }, new { @class = "btn btn-default glyphicon glyphicon-remove delete-product", @data_id = item.EmpID }).ToHtmlString();

                     return Html.Raw(links);
                 }
                 , canSort: false)
              ),
mode: WebGridPagerModes.All
)
                <input type="submit" class="btn btn-danger" value="Delete Selected Records" />
            }
            else
            {
             <p class="text-info, text-danger">No Records Found</p>
             <table class="table table-responsive table-bordered">
                 <thead>
                     <tr class="header_style">
                         <th scope="col">
                             Select
                         </th>
                         <th scope="col">
                             Employee ID
                         </th>
                         <th scope="col">
                             Name
                         </th>
                         <th scope="col">
                             Salary
                         </th>
                         <th scope="col">
                             City
                         </th>
                         <th scope="col">
                             Country
                         </th>
                     </tr>
                 </thead>
             </table>
            }
        }
    </div>
</div>

Right now the Edit and Delete buttons will not be working. We will discuss in later articles. Now run your application, you can see the records will be displayed in WebGrid. You can select record using checkbox and Click on "Delete Selected Records" to delete records.

Display and deleting record from webgrid
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON