Monday, April 25, 2016

ASP.NET MVC : CRUD operation using store procedure

In this article I am going to explain how Create, read, update and Delete (CRUD) opratuion using Store procedure in asp.net mvc.


Implementation:
I have created a table 
ASP.NET MVC : CRUD operation using store procedure


Create Store procedure to Insert and update the record:
Create PROCEDURE Sp_InsertUpdateEmployee
(
@Id int,
@Name varchar(100),
@Phone int,
@Salary int,
@Department varchar(100),
@ImagePath varchar(max),
@email varchar(100)
)
AS
BEGIN
            SET NOCOUNT ON;
  if @id =0
  begin
  Insert into Employees (Name,Phone,Salary,Department,ImagePath,EmailId) values (@Name,@Phone,@Salary,@Department,@ImagePath,@email)
  end
   else 
  begin
  update Employees set Name=@name,Phone=@Phone ,Salary=@Salary ,Department=@Department ,ImagePath=@ImagePath,EmailId=@email where id = @id
 end
END

Create store procedure to delete the Record:
CREATE PROCEDURE Sp_DeleteEmployee
(
@id int
)
AS
BEGIN
           
            SET NOCOUNT ON;

   Delete from dbo.Employees where id = @id
END
GO

Add MVC project:-
Now move to visual studio. Add a new empty MVC project.

Add Models:-
In this example I am using data first approach.  So I am going to add the entity data model.

Right click on Models folder >> Add >> New Item. Add new items template will be open in pop up. Click on data and add the ADO.net Entity Data Model.


ASP.NET MVC : CRUD operation using store procedure


Entity data model wizard will be started. Select Generate from database and click on next button.

ASP.NET MVC : CRUD operation using store procedure


Now connection properties window will be open. If you already configured the data connection then choose it and click on next button. If not then configure all the given properties.

ASP.NET MVC : CRUD operation using store procedure


Choose the entity framework version.

ASP.NET MVC : CRUD operation using store procedure


Choose the database objects that you want to use. I want to use Table and Store procedure.

ASP.NET MVC : CRUD operation using store procedure


Build the project. Now you can see all the class and store procedure in model.

Add controller:-
I have added empty controller.

Add the namespace:
using MVC_Project.Models;
using System.IO;

Create object of data context.

DemoEntities1 db = new DemoEntities1();
       
        // GET: /Employee/

        public ActionResult Index()
        {
            return View(db.Employees.ToList());
        }
      
 // Add New Employee/
        public ActionResult Create()
        {
            return View();
        }
        [HttpPost]
        public ActionResult Create(Employee objemployee, HttpPostedFileBase file)
        {
            if(ModelState.IsValid)
            if (file != null && file.ContentLength > 0)
            {
                var fileName = Path.GetFileName(file.FileName);
                var guid = Guid.NewGuid().ToString();
                var path = Path.Combine(Server.MapPath("~/images"), guid + fileName);
                file.SaveAs(path);
                string fl = path.Substring(path.LastIndexOf("\\"));
                string[] split = fl.Split('\\');
                string newpath = split[1];
                string imagepath = "images/" + newpath;

                db.Sp_InsertUpdateEmployee(0, objemployee.Name, objemployee.Phone, objemployee.Salary, objemployee.Department, imagepath,objemployee.EmailId);
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            return View(objemployee);
        }
      
 // Update Employee Information/
        public ActionResult Update(int id = 0)
        {
            Employee emp = db.Employees.Find(id);
            return View(emp);
        }
        [HttpPost]
        public ActionResult Update(Employee objemployee, HttpPostedFileBase file)
        {
            if (ModelState.IsValid)
            if (file != null && file.ContentLength > 0)
            {
                var fileName = Path.GetFileName(file.FileName);
                var guid = Guid.NewGuid().ToString();
                var path = Path.Combine(Server.MapPath("~/images"), guid + fileName);
                file.SaveAs(path);
                string fl = path.Substring(path.LastIndexOf("\\"));
                string[] split = fl.Split('\\');
                string newpath = split[1];
                string imagepath = "images/" + newpath;
                db.Sp_InsertUpdateEmployee(objemployee.Id, objemployee.Name, objemployee.Phone, objemployee.Salary, objemployee.Department,imagepath,objemployee.EmailId);
                db.SaveChanges();

                return RedirectToAction("Index");
            }
            return View(objemployee);
        }
       
// Delete Employee/
        public ActionResult Delete(int id = 0)
        {
            Employee employee = db.Employees.Find(id);           
            return View(employee);
        }
        [HttpPost, ActionName("Delete")]
        public ActionResult DeleteConfirmed(int id)
        {          
            db.Sp_DeleteEmployee(id);
            db.SaveChanges();
            return RedirectToAction("Index");
        }


Add view:--
Add the view for all action Index. Create, update and Delete.

Index View (index.cshtml):
@model IEnumerable<MVC_Project.Models.Employee>

@{
    WebGrid grid = new WebGrid(Model, canSort: true, canPage: true,rowsPerPage:5);
}
<style type="text/css">
    table {
        width: 80%;
    }

    th {
        padding: 2px 2px 2px;
    }

    td {
        text-align: center;
    }
</style>
@grid.GetHtml(
    tableStyle: "table",
    fillEmptyRows: true,
    headerStyle: "false",
    footerStyle: "false",
    mode: WebGridPagerModes.All,
    columns: new[]
    {
        grid.Column("Name","Name"),
        grid.Column("Phone","Phone"),
        grid.Column("Salary","Salary"),

         grid.Column("Department","Department"),
         grid.Column("EmailId","EmailId"),
        grid.Column(header:"Profile Image", format: @<text><img src="../@item.ImagePath" alt="Image" width="100px"/></text>),   
grid.Column("Edit",format:@<text>@Html.ActionLink("Edit", "Update", new { id = item.ID })</text>),
grid.Column("Delete",format:@<text>@Html.ActionLink("Delete", "Delete", new { id = item.ID })</text>),
   }
     )


Create View (create.cshtml)
@model MVC_Project.Models.Employee

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>



@using (Html.BeginForm("Create","Employee",FormMethod.Post, new{enctype = "multipart/form-data"}))
{
    @Html.AntiForgeryToken()
    @Html.ValidationSummary(true)

    <fieldset>
        <legend>Employee1</legend>

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

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

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

        <div class="editor-label">
            @Html.LabelFor(model => model.Department)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Department)
            @Html.ValidationMessageFor(model => model.Department)
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.EmailId)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.EmailId)
            @Html.ValidationMessageFor(model => model.EmailId)
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.ImagePath)
        </div>
        <div class="editor-field">
            <input type="file" name="file" id="ImagePath"  />
            @Html.ValidationMessageFor(model => model.ImagePath)
        </div>

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

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

<script src="http://code.jquery.com/jquery-1.8.2.js"></script>
<script type="text/javascript">
    $(function () {
        $('#ImagePath').change(function () {
            var fileExtension = ['jpeg', 'jpg', 'png', 'gif', 'bmp'];
            if ($.inArray($(this).val().split('.').pop().toLowerCase(), fileExtension) == -1) {
                alert("Only '.jpeg','.jpg', '.png', '.gif', '.bmp' formats are allowed.");
            }
        })
    })
</script>


Update View (update.cshtml):
@model MVC_Project.Models.Employee

@{
    ViewBag.Title = "Update";
}

<h2>Update</h2>

@using (Html.BeginForm("Update","Employee",FormMethod.Post, new{enctype = "multipart/form-data"})) {
    @Html.AntiForgeryToken()
    @Html.ValidationSummary(true)

    <fieldset>
        <legend>Employee</legend>

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

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

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

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

        <div class="editor-label">
            @Html.LabelFor(model => model.Department)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Department)
            @Html.ValidationMessageFor(model => model.Department)
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.EmailId)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.EmailId)
            @Html.ValidationMessageFor(model => model.EmailId)
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.ImagePath)
        </div>
        <div class="editor-field">
            <input type="file" name="file" id="ImagePath" />
            @Html.ValidationMessageFor(model => model.ImagePath)
        </div>

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

<div>
    @Html.ActionLink("Back to List", "Index")
</div>
<script src="http://code.jquery.com/jquery-1.8.2.js"></script>
<script type="text/javascript">
    $(function () {
        $('#ImagePath').change(function () {
            var fileExtension = ['jpeg', 'jpg', 'png', 'gif', 'bmp'];
            if ($.inArray($(this).val().split('.').pop().toLowerCase(), fileExtension) == -1) {
                alert("Only '.jpeg','.jpg', '.png', '.gif', '.bmp' formats are allowed.");
            }
        })
    })
</script>


Delete view (Delete.cshtml)
@model MVC_Project.Models.Employee

@{
    ViewBag.Title = "Delete";
}

<h2>Delete</h2>

<h3>Are you sure you want to delete this?</h3>
<fieldset>
    <legend>Employee</legend>

    <div class="display-label">
         @Html.DisplayNameFor(model => model.Name)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Name)
    </div>

    <div class="display-label">
         @Html.DisplayNameFor(model => model.Phone)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Phone)
    </div>

    <div class="display-label">
         @Html.DisplayNameFor(model => model.Salary)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Salary)
    </div>

    <div class="display-label">
         @Html.DisplayNameFor(model => model.Department)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Department)
    </div>

    <div class="display-label">
         @Html.DisplayNameFor(model => model.ImagePath)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.ImagePath)
    </div>

    <div class="display-label">
         @Html.DisplayNameFor(model => model.EmailId)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.EmailId)
    </div>
</fieldset>
@using (Html.BeginForm()) {
    @Html.AntiForgeryToken()
    <p>
        <input type="submit" value="Delete" /> |
        @Html.ActionLink("Back to List", "Index")
    </p>
}

  

No comments:

Post a Comment