Step by step Perform CRUD Operations in MVC5 without the use of Entity Framework


Through-out the article we will learn step-by-step: How to perform crud operations in MVC5 without using Entity Framework. We will discuss crud operations with usage of pure Sql server. I have mention the code for Save,Edit,delete given below.

Abstract


Through-out the article we will learn step-by-step: How to perform crud operations in MVC5 without using Entity Framework. We will discuss crud operations with usage of pure Sql server.

Introduction


In these days, MVC5 is most growing framework and as a developer I am curious to know and learn
about the new features of this framework.

From last couple of days I was wondering how can I perform CRUD operations using pure sql queries. For the same I have gone through various forums etc. Unfortunately, I did saw very few posts/articles for the same.

Finally, I tried and came with a solution to perform CRUD operations without the use of Entity Framework. In this article, I am going to share my findings with all of you.

Why Step-by-Step


Ah! This question came to my mind while I was finding the solution of my problem, I jotted down few points and finally
called them as a steps to perform CRUD Operations.

Pre-requisite


To implement the solution and to feel the taste of code, you should:

  • Have Visual Studio 2013 or later with the support of MVC5

  • Have Basic Idea of SQL Server

  • Have Basic Idea of Sql queries



What we are waiting for?


So, what we are waiting for here, lets get started. Just following these step(s) and we will done!

Step1: Create MVC5 Project


Launch your visual studio and perform following action (Refer to snapshot)
File->New->Select the new project ->Web Application->Select the MVC Template.

Create New Project in MVC5

Selecting a template for project:

How to Select Template

Step2:Create controller and view


Right click the Controller folder then Empty Controller-> give the controller name, then add methods
what do you need and then right click to create a new view.


Step3:Bind the Records using


In your view, write or copy/paste code, mentioned in following code-snippet:


public ActionResult Index()
{
sqladp = new SqlDataAdapter("Select * from TblEmp", sqlcon);
sqladp.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
lstemp.Add(new Employee() { EmpId = int.Parse(dr[0].ToString()), EmpName = dr[1].ToString(), EmpNo = dr[2].ToString() });
}
return View(lstemp);
}


Following code invoke the Inserted Records to database.


[HttpGet]
public ActionResult Create()
{
return View();
}

[HttpPost]
public ActionResult Create(Employee empcls)
{
sqlcon.Open();
SqlCommand sqlcmd = new SqlCommand("Insert into TblEmp values('"+ empcls.EmpNo +"','"+ empcls.EmpName +"')", sqlcon);
sqlcmd.ExecuteNonQuery();
lstemp = GetDatas();
return View("Index", lstemp);
}


Following code-snippet defines How to select corresponding record and then to update them.


[HttpGet]
public ActionResult Edit(string id)
{
sqlcon.Open();
string Sqry= "select * from TblEmp where Id='"+ id + "'";
empcls = GetDatasval(Sqry);
return View("Edit", empcls);
}

[HttpPost]
public ActionResult Edit(Employee empcl)
{
sqlcon.Open();
SqlCommand sqlcmd = new SqlCommand("Update TblEmp set Empno='" + empcl.EmpNo + "',EmpName='" + empcl.EmpName + "' where id=" + empcl.EmpId, sqlcon);
sqlcmd.ExecuteNonQuery();
lstemp = GetDatas();
return View("Index", lstemp);
}


Now, Fetch the records from database by passing parameters and without parameters.


public List<Employee> GetDatas()
{
sqladp = new SqlDataAdapter("Select * from TblEmp", sqlcon);
sqladp.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
lstemp.Add(new Employee() { EmpId = int.Parse(dr[0].ToString()), EmpName = dr[1].ToString(), EmpNo = dr[2].ToString() });
}
return lstemp;
}

public Employee GetDatasval(string Sqry)
{
sqladp = new SqlDataAdapter(Sqry, sqlcon);
sqladp.Fill(dt);
empcls.EmpId = Convert.ToInt32(dt.DefaultView[0][0]);
empcls.EmpName = dt.DefaultView[0][1].ToString();
empcls.EmpNo = dt.DefaultView[0][2].ToString();
return empcls;
}


Following code-snippet describing how to delete the corresponding records:


[HttpGet]
public ActionResult Delete(string id)
{
string Sqry = "Delete from TblEmp where Id='" + id + "'";
sqlcon.Open();
SqlCommand sqlcmd = new SqlCommand(Sqry, sqlcon);
sqlcmd.ExecuteNonQuery();
lstemp = GetDatas();
return View("Index", lstemp);
}


Creating view


First of all create a view using scaffolding:
How to add Scaffold
Write or copy/paste code to your index.cshtml view, mentioned in the following code-snippet:


<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.EmpId)
</th>
<th>
@Html.DisplayNameFor(model => model.EmpName)
</th>
<th>
@Html.DisplayNameFor(model => model.EmpNo)
</th>
<th></th>
</tr>

@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.EmpId)
</td>
<td>
@Html.DisplayFor(modelItem => item.EmpName)
</td>
<td>
@Html.DisplayFor(modelItem => item.EmpNo)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id =item.EmpId }) |
@Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Delete", "Delete", new { id=item.EmpId })
</td>
</tr>
}

</table>



@using (Html.BeginForm())
{
@Html.AntiForgeryToken()

<div class="form-horizontal">
<h4>Employee</h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.LabelFor(model => model.EmpId, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.EmpId, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.EmpId, "", new { @class = "text-danger" })
</div>
</div>

<div class="form-group">
@Html.LabelFor(model => model.EmpName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.EmpName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.EmpName, "", new { @class = "text-danger" })
</div>
</div>

<div class="form-group">
@Html.LabelFor(model => model.EmpNo, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.EmpNo, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.EmpNo, "", new { @class = "text-danger" })
</div>
</div>

<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Create" class="btn btn-default" />
</div>
</div>
</div>
}

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



Following is the final view of our CRUD Operations:
Selected Record Edit

Conclusion


In this whole article, we discussed about CRUD operations using MVC5.
1. Create a new project
2. Choose correct template
3. Add Controller
4. Add view using Scaffoldings
5. Add various operations

Hope, you find this article helpful.


Attachments

Comments

Author: Gaurav Kumar Arora04 Jul 2015 Member Level: Gold   Points : 0

Hey DotNet Developer,
Good start for writing a great topics. Keep it up!

Author: Dotnet Developer-201504 Jul 2015 Member Level: Gold   Points : 1

Hi Gaurav,

Thanks for your encouraging words!

I appreciate your help. Thank you very much.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: