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)

)

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

)

BEGIN

SET NOCOUNT ON;

Delete from dbo.Employees where id = @id

END

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.

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

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.

Choose the entity framework version.

Choose the database objects that you want to use. I want to use Table and 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);

}

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)

<legend>Employee1</legend>

@Html.LabelFor(model => model.Name)

</div>

@Html.EditorFor(model => model.Name)

@Html.ValidationMessageFor(model => model.Name)

</div>

@Html.LabelFor(model => model.Phone)

</div>

@Html.EditorFor(model => model.Phone)

@Html.ValidationMessageFor(model => model.Phone)

</div>

@Html.LabelFor(model => model.Salary)

</div>

@Html.EditorFor(model => model.Salary)

@Html.ValidationMessageFor(model => model.Salary)

</div>

@Html.LabelFor(model => model.Department)

</div>

@Html.EditorFor(model => model.Department)

@Html.ValidationMessageFor(model => model.Department)

</div>

@Html.LabelFor(model => model.EmailId)

</div>

@Html.EditorFor(model => model.EmailId)

@Html.ValidationMessageFor(model => model.EmailId)

</div>

@Html.LabelFor(model => model.ImagePath)

</div>

@Html.ValidationMessageFor(model => model.ImagePath)

</div>

<p>

</p>

</fieldset>

}

<div>

@Html.ActionLink("Back to List", "Index")

</div>

$(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)

<legend>Employee</legend>

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

@Html.LabelFor(model => model.Name)

</div>

@Html.EditorFor(model => model.Name)

@Html.ValidationMessageFor(model => model.Name)

</div>

@Html.LabelFor(model => model.Phone)

</div>

@Html.EditorFor(model => model.Phone)

@Html.ValidationMessageFor(model => model.Phone)

</div>

@Html.LabelFor(model => model.Salary)

</div>

@Html.EditorFor(model => model.Salary)

@Html.ValidationMessageFor(model => model.Salary)

</div>

@Html.LabelFor(model => model.Department)

</div>

@Html.EditorFor(model => model.Department)

@Html.ValidationMessageFor(model => model.Department)

</div>

@Html.LabelFor(model => model.EmailId)

</div>

@Html.EditorFor(model => model.EmailId)

@Html.ValidationMessageFor(model => model.EmailId)

</div>

@Html.LabelFor(model => model.ImagePath)

</div>

@Html.ValidationMessageFor(model => model.ImagePath)

</div>

<p>

</p>

</fieldset>

}

<div>

@Html.ActionLink("Back to List", "Index")

</div>

$(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>

<legend>Employee</legend>

@Html.DisplayNameFor(model => model.Name)

</div>

@Html.DisplayFor(model => model.Name)

</div>

@Html.DisplayNameFor(model => model.Phone)

</div>

@Html.DisplayFor(model => model.Phone)

</div>

@Html.DisplayNameFor(model => model.Salary)

</div>

@Html.DisplayFor(model => model.Salary)

</div>

@Html.DisplayNameFor(model => model.Department)

</div>

@Html.DisplayFor(model => model.Department)

</div>

@Html.DisplayNameFor(model => model.ImagePath)

</div>

@Html.DisplayFor(model => model.ImagePath)

</div>

@Html.DisplayNameFor(model => model.EmailId)

</div>

@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>

}

Monday, April 25, 2016

ASP.NET MVC : CRUD operation using store procedure

No comments:

Post a Comment

Popular Posts

Labels