MVC4 Edit,update,Delete,cancel inside gridview using sql database

MVC4 Edit,update,Delete,cancel inside gridview using sql database.

Introduction: This tutorial i am explain insert,update ,delete ,cancel operation inside gridview using Sql database in mvc4 .here we are using .net framwork 4.5 and mvc4 web application.

Download Complete Code : Download Here

Tools: visual studio 2012 and sql server database

Step1: Create mvc4 web application

Step2: Create database as following and add the table like below.

Step 3: Now click on controller and add new conrtoller & give name "ABC"

Step 4: Now cerate model and give name DAL.cs as following.

Model >> Add >>class

Step5: Now you can add the following code in DAL.cs file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel.DataAnnotations;
namespace MvcApplication1.Models
{
    public class DAL
    {
        public int id { get; set; }
        [Required]
        public string Name { get; set; }
        public string City { get; set; }
        public string Education { get; set; }

        public SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\MVC_Project\MvcGridview\MvcApplication1\App_Data\Database1.mdf;Integrated Security=True");


        public List<DAL> Select_data()
        {
            List<DAL> D = new List<DAL>();
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from tbl_user", con);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DAL da = new DAL();
                da.id = Convert.ToInt32(dr["id"].ToString());
                da.Name = dr["Name"].ToString();
                da.City = dr["City"].ToString();
                da.Education = dr["Education"].ToString();
                D.Add(da);
            }
            con.Close();
            return D;
        }
        public List<DAL> Edit_data(int id)
        {
            List<DAL> D = new List<DAL>();
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from tbl_user where id='" + id + "'", con);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DAL da = new DAL();
                da.id = Convert.ToInt32(dr["id"].ToString());
                da.Name = dr["Name"].ToString();
                da.City = dr["City"].ToString();
                da.Education = dr["Education"].ToString();
                D.Add(da);
            }
            con.Close();
            return D;
        }
        public void Update_data(DAL da)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("update tbl_user set Name='" + da.Name + "', City='" + da.City + "', Education='" + da.Education + "' where id='" + id + "'", con);
            cmd.ExecuteNonQuery();
            con.Close();

        }
        public void delete_data(DAL da)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("delete from tbl_user where id='" + da.id + "'", con);
            cmd.ExecuteNonQuery();
            con.Close();
        }

    }
}

Step :6 Now you can add the following code in controller "ABC" .Here you need to add the namespace as following

using MvcApplication1.Models;
using System.Data;
using System.Data.SqlClient;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcApplication1.Models;
using System.Data;
using System.Data.SqlClient;
namespace MvcApplication1.Controllers
{
    public class ABCController : Controller
    {
        //
        // GET: /ABC/

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


        public ActionResult Update(DAL da)
        {
            da.Update_data(da);
          return RedirectToAction("GridEdit");
        }

        public ActionResult Delete(DAL da)
        {
            da.delete_data(da);
            return RedirectToAction("GridEdit");
        }

       public ActionResult GridEdit(DAL da)
        {
            return View(da.Select_data());
        }
    }

    }

Step 7 : Now you can add the javascript code and css style in GridEdit.cshtml page and and bind the gridview data as following code.

@model IEnumerable<MvcApplication1.Models.DAL>
<script src="~/Scripts/jquery-1.7.1.min.js"></script>
<script type="text/javascript">

</script>
<style type="text/css">
    .wGrid
    {
        margin: 3px;
        border-collapse: collapse;
        width: 100%;
        font-family: Tahoma;
    }
    .gridHeader
    {
        background-color: black;
        font-weight: bold;
        color: White !important;
    }
    .wGrid th a
    {
        color: yellow;
        text-decoration: none;
    }
    .wGrid th, .wGrid td
    {
        border: 2px solid blue;
        padding: 4px;
    }
    .alt
    {
        background-color: #F4EFEF;
    }
    .wGrid th a:hover
    {
        text-decoration: none;
    }
    .to-the-right
    {
        text-align: leftt;
    }
</style>

@{
    ViewBag.Title = "GridEdit";
}

<h2>GridEdit</h2>

@{
    var grid = new WebGrid(source: Model, canPage: true, defaultSort: "Id", rowsPerPage: 3);
}
<h2 style="float: right; padding-right: 30%;">
   @Html.ActionLink("GridEdit", "GridEdit", "ABC")
</h2>
<div id="gridContent" style="padding-right: 30%;">
    @grid.GetHtml(
         tableStyle: "wGrid",
         fillEmptyRows: false,
         alternatingRowStyle: "alt",
         headerStyle: "gridHeader",
         mode: WebGridPagerModes.All,
         firstText: "<< First",
         previousText: " < Previous",
         nextText: "Next >",
         lastText: "Last >>",
        emptyRowCellValue: null,
    columns:
        grid.Columns(
        grid.Column("Id", format: @<text>
        <span class="display-mode">@item.Id</span>
        <label id="Id" class="edit-mode">@item.Id</label>
        </text>),
        grid.Column("Name", "Name", format: @<text>
        <span class="display-mode">
        <label id="lblName">@item.Name</label>
        </span>
        <input type="text" id="Name" value="@item.Name" class="edit-mode" />
        </text>),
        grid.Column("City", "Cty", format: @<text>
        <span class="display-mode">
        <label id="lblCity">@item.City</label>
        </span>
        <input type="text" id="City" value="@item.City" class="edit-mode" /></text>),
        grid.Column("Education", "Education", format: @<text>
        <span class="display-mode">
        <label id="lblEducation">@item.Education</label>
        </span>
       <input type="text" id="Education" value="@item.Education" class="edit-mode" />
       </text>),
        grid.Column("Action", format: @<text>
    <button class="edit display-mode"> Edit</button>
    <button class="delete display-mode"> Delete</button>
    <button class="save edit-mode"> Save</button>
    <button class="cancel edit-mode"> Cancel</button>
    </text>, canSort: false)
    ))
</div>

Now run Project and see output like below .