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: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.
Selecting a template for project:
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:
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:
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
Hey DotNet Developer,
Good start for writing a great topics. Keep it up!