Tuesday, 30 December 2014

Enhancing WebGrid with Insert Update and Delete Operations Using Repository Pattern with Entity Framework in ASP.NET MVC 4 Razor

Hi friends, in this article I will explain about How to Enhance the WebGrid with Insert Update and Delete Operations Using Repository Pattern with Entity Framework in ASP.NET MVC 4 Razor.
I already explained in the previous articles about How to Update multiple rows at once Using MVC 4 Razor and Entity FrameworkMVC 4 Razor: Delete multiple rows in WebGrid with CheckBox selection and with confirmation in ASP.NET using C#.NET and MVC 4 Razor: How to Select / Deselect All Checkboxes inside a Webgrid in ASP.NET Application using C#.NET.
Repository pattern is used to create an abstraction layer between the data access layer and the business logic layer. 
This abstraction layer contains data manipulation methods which communicate with the data access layer to serve data as per the business requirements to the logical layer. The main purpose to create this layer is for isolating data access layer so that changes cannot affect the business logic layer directly.

SQL script for USERS table Structure
CREATE TABLE [dbo].[USERS](
     [User_id] [int] IDENTITY(1,1) NOT NULL,
     [UserName] [varchar](100) NULL,
     [Gender] [varchar](10) NULL,
     [Address] [varchar](200) NULL,
 CONSTRAINT [PK_USERS] PRIMARY KEY CLUSTERED
(
     [User_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

Add ADO.NET Entity Data Model and take USERS Table as shown in the figure.

The User.edmx design as shown in the below figure.

Create interface IuserRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MvcRepositoryPatternEF.Models;

namespace MvcRepositoryPatternEF.Models
{
    interface IUserRepository
    {
        IEnumerable<USER> GetUserDetails();
        USER GetUserByID(int userId);
        void AddUser(USER user);
        void DeleteUser(int userId);
        void UpdateUser(USER user);
        USER Details(int userId);
    }
}

Create Class UserRepository.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.IO;
using MvcRepositoryPatternEF.Models;
using MvcRepositoryPatternEF;

namespace MvcRepositoryPatternEF.Models
{
    public class UserRepository : IUserRepository
    {
        private UserDBEntities db = new UserDBEntities();

        public UserRepository(UserDBEntities db)
        {
            this.db = db;
        }

        public IEnumerable<USER> GetUserDetails()
        {
            try
            {
                return db.USERS.ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (db != null)
                {
                    db.Dispose();
                }
            }
        }

        public USER GetUserByID(int userId)
        {
            try
            {
                return db.USERS.SingleOrDefault(x => x.User_id == userId);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (db != null)
                {
                    db.Dispose();
                }
            }
        }

        public void AddUser(USER user)
        {
            try
            {
                db.USERS.AddObject(user);
                db.SaveChanges();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (db != null)
                {
                    db.Dispose();
                }
            }
        }

        public void UpdateUser(USER user)
        {
            try
            {
                var usernew = db.USERS.Where(x => x.User_id == user.User_id).FirstOrDefault();
                usernew.UserName = user.UserName;
                usernew.Gender = user.Gender;
                usernew.Address = user.Address;
                db.SaveChanges();
                usernew = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (db != null)
                {
                    db.Dispose();
                }
            }
        }

        public void DeleteUser(int userId)
        {
            try
            {
                USER user = db.USERS.SingleOrDefault(x => x.User_id == userId);
                db.USERS.DeleteObject(user);
                db.SaveChanges();
                user = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (db != null)
                {
                    db.Dispose();
                }
            }
        }

        public USER Details(int userId)
        {
            try
            {
                dynamic obj = new USER();
                obj = db.USERS.SingleOrDefault(s => s.User_id == userId);
                return obj;

            }
            catch (Exception ex) { throw ex; }
            finally
            {
                if (db != null)
                {
                    db.Dispose();
                }
            }
        }
    }
}

Controller(UserController):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcRepositoryPatternEF.Models;

namespace MvcRepositoryPatternEF.Controllers
{
    public class UserController : Controller
    {
        private IUserRepository db;
        public UserController()
        {
            db = new UserRepository(new UserDBEntities());
        }
        private UserDBEntities db2 = new UserDBEntities();
        public ActionResult Index()
        {
            var userList = from user in db.GetUserDetails() select user;
            return View(userList);
        }
        public ActionResult AddUser()
        {
            return View();
        }
        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult AddUser(USER user)
        {
            db.AddUser(user);
            return RedirectToAction("Index");
        }
        public ActionResult Details(int id = 0)
        {
            USER user = db.Details(id);
            return View(user);
        }
        public ActionResult Delete(int id = 0)
        {
            db.DeleteUser(id);
            return RedirectToAction("Index");
        }
        public ActionResult Edit(int id=0)
        {
            USER user = db2.USERS.SingleOrDefault(x => x.User_id == id);
            if (user == null)
            {
                return HttpNotFound();
            }
            return View(user);
        }
        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult Edit(USER user)
        {
            db.UpdateUser(user);
            return RedirectToAction("Index");
        }
    }
}

View(Index.cshtml):
@model IEnumerable<MvcRepositoryPatternEF.USER>
@{
    ViewBag.Title = "Index";
    var grid = new WebGrid(source: Model, canPage: true, defaultSort: "User_Id", rowsPerPage: 3, canSort: true); 

}
<h2>
    Index</h2>
<style type="text/css">
    .webGrid
    {
        margin: 4px;
        border-collapse: collapse;
        width: 500px;
        font-family: Tahoma;
    }
    .grid-header
    {
        background-color: #990000;
        font-weight: bold;
        color: White !important;
    }
    .webGrid th a
    {
        color: White;
        text-decoration: none;
    }
    .webGrid th, .webGrid td
    {
        border: 1px solid black;
        padding: 5px;
    }
    .alt
    {
        background-color: #F4EFEF;
    }
    .webGrid th a:hover
    {
        text-decoration: underline;
    }
    .to-the-right
    {
        text-align: right;
    }
</style>
<p>
    @Html.ActionLink("Create New", "AddUser")
</p>

@grid.GetHtml(
tableStyle: "webGrid",
htmlAttributes: new { id = "checkableGrid" },
fillEmptyRows: true,
alternatingRowStyle: "alt",
headerStyle: "grid-header",
footerStyle: "foot-grid",
mode: WebGridPagerModes.All,
firstText: "<< First",
previousText: " < Previous",
nextText: "Next >",
lastText: "Last >>",
caption: "User Details",
emptyRowCellValue: null,
columns: new[]{
    grid.Column("User_ID",header:"User ID",style:"to-the-right"),
    grid.Column("UserName"),
    grid.Column("Gender"),
    grid.Column("Address"),
    grid.Column("",header: "Actions",format: @<text>@Html.ActionLink("Edit", "Edit", new { id = item.User_id })
|
@Html.ActionLink("Delete", "Delete", new { id = item.User_id })
|
@Html.ActionLink("Details", "Details", new { id = item.User_id })
</text>
    )
}
    )

The output of the above code as shown in the below figure.


View(AddUser.cshtml):
@model MvcRepositoryPatternEF.USER

@{
    ViewBag.Title = "AddUser";
}

<h2>AddUser</h2>

@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)

    <fieldset>
        <legend>USER</legend>

        <div class="editor-label">
            @Html.LabelFor(model => model.UserName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.UserName)
            @Html.ValidationMessageFor(model => model.UserName)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Gender)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Gender)
            @Html.ValidationMessageFor(model => model.Gender)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Address)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Address)
            @Html.ValidationMessageFor(model => model.Address)
        </div>

        <p>
            <input type="submit" value="Create" />
        </p>
    </fieldset>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}


The output of the above code as shown in the below figure.


View(Edit.cshtml):
@model MvcRepositoryPatternEF.USER

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>

@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)

    <fieldset>
        <legend>USER</legend>

        @Html.HiddenFor(model => model.User_id)

        <div class="editor-label">
            @Html.LabelFor(model => model.UserName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.UserName)
            @Html.ValidationMessageFor(model => model.UserName)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Gender)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Gender)
            @Html.ValidationMessageFor(model => model.Gender)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Address)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Address)
            @Html.ValidationMessageFor(model => model.Address)
        </div>

        <p>
            <input type="submit" value="Save" />
        </p>
    </fieldset>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

The output of the above code as shown in the below figure.
After updating the output will be like as shown in the below figure.



View(Details.cshtml):
@model MvcRepositoryPatternEF.USER
@{
    ViewBag.Title = "Details";
}
<h2>
    Details</h2>
   
<fieldset>
    <legend>USER</legend>
    <table>
        <tr>
            <td>
                @Html.DisplayNameFor(model => model.UserName):            
                @Html.DisplayFor(model => model.UserName)
            </td>
        </tr>
          <tr>
            <td>
                @Html.DisplayNameFor(model => model.Gender):            
                @Html.DisplayFor(model => model.Gender)
            </td>
        </tr>  <tr>
            <td>
                @Html.DisplayNameFor(model => model.Address):             
                @Html.DisplayFor(model => model.Address)
            </td>
        </tr>
    </table>
  
</fieldset>
<p>
    @Html.ActionLink("Edit", "Edit", new { id = Model.User_id }) |
    @Html.ActionLink("Back to List", "Index")
</p>


The output of the above code as shown in the below figure.
You can download the above code by clicking on the below Download image.

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.