In my last article, we have discussed insert update and delete data in MVC using Entity Framework. In this post, We will do insert, update, and delete(Crud Operation) functionality in MVC 5 Web Application without using Entity Framework that means in this article we will use database connection and SQLCommand for performing the Crud operation instead of entity framework.
So Let’s start , step by step to learn how easily we can complete this requirement in any project. We will try to create a basic View where Users can see all the lists of records in a Table with add button for adding new records, edit button for updating existing records and delete links to delete any existing records with a confirmation alert box.
we can divide the complete operation into step
- Adding New Empty ASP.NET MVC Project in Visual Studio
- Creating Database, Table for performing crud operation
- Adding Controller
- Adding Model Classes and validation using data annotation
- Adding Action Method in Controller and write database logic.
- Build and run Project
So let’s start by creating an Mvc .Net Project
Step 1 – First create an ASP.NET MVC Application, open visual studio, and add an empty MVC project.
Step 2. Create SQL table for performing the CRUD Operation
In this step, we are going to create the database table, for our database operation .I have created a table called TblPatient for that.which is got, the Id, PatientName, PatientNumber, PatientEmail, Adress, and BloodGroup
columns.
CREATE TABLE [dbo].[TblPatient]( [Id] [int] IDENTITY(1,1) NOT NULL, [PatientName] [nvarchar](250) NULL, [PatientNumber] [nvarchar](100) NULL, [PatientEmail] [nvarchar](150) NULL, [Address] [nvarchar](500) NULL, [BloodGroup] [nvarchar](100) NULL, CONSTRAINT [PK_TblPatient] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY]
Step 3 –Now, create a controller inside Controllers folder named HomeController.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace CrudWithEntity.Controllers
{
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
}
}Step 4 – Right, click on Models Folder in your project Add Class. Select class option form menu and create new class PatientModelVeiw.cs.
PatientModelVeiw.cs
public class PatientModelVeiw
{
[Display(Name = "Id")]
public int Id { get; set; }
[Required(ErrorMessage = "Patient name is required.")]
public string PatientName { get; set; }
[Required(ErrorMessage = "Patient Number is required.")]
[DataType(DataType.PhoneNumber)]
[RegularExpression(@"^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$", ErrorMessage = "Not a valid Patient phone number")]
public string PatientNumber { get; set; }
[Required(ErrorMessage = "Patient Email is required.")]
[EmailAddress(ErrorMessage = "Invalid Patient Email Address")]
public string PatientEmail { get; set; }
[Required(ErrorMessage = "Patient Address is required.")]
public string Address { get; set; }
[Required(ErrorMessage = "BloodGroup is required.")]
public string BloodGroup { get; set; }
}I’m using the Data Annotations for validating our model data, in simple words we can understand Data Annotations as certain validations that we use in our models class to validate the data, entered by the user.
ASP.NET MVC provides the Data Annotation attribute in System.ComponentModel.DataAnnotations namespace to validate the models using.
Step 6- Now let’s write the logic in our controller
Open HomeController and add the following action methods. Here, I am going to add 5 action methods
1. For showing All Patient in the clinic- Index()
2. For showing PatientDetails- PatientDetails()
3. For Adding new Patient – AddPatient()
4. For Update Patient Details – UpdatePatient()
5. For deleting Patient – DeletePatient ()
using CrudWithEntity.Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
namespace CrudWithEntity.Controllers
{
public class HomeController : Controller
{
// 1. *************show the list of Patient in the Clinic ******************
// GET: Home
string constr = "Data Source=ADEQUATE-ASHOK\\SQLEXPRESS01;Initial Catalog=SampleRestApi;User ID=adk;Password=adk@1234";
public ActionResult Index()
{
List<PatientModelVeiw> Patients = new List<PatientModelVeiw>();
string query = "SELECT * FROM TblPatient";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
Patients.Add(new PatientModelVeiw
{
Id = Convert.ToInt32(sdr["Id"]),
PatientName = Convert.ToString(sdr["PatientName"]),
PatientEmail = Convert.ToString(sdr["PatientEmail"]),
PatientNumber = Convert.ToString(sdr["PatientNumber"]),
Address = Convert.ToString(sdr["Address"]),
BloodGroup = Convert.ToString(sdr["BloodGroup"])
});
}
}
con.Close();
}
}
if (Patients.Count == 0)
{
Patients.Add(new PatientModelVeiw());
}
return View(Patients);
}
// GET: Home/PatientDetails/5
public ActionResult PatientDetails(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
PatientModelVeiw Patient = new PatientModelVeiw();
string query = "SELECT * FROM TblPatient where Id=" + id;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
Patient = new PatientModelVeiw
{
Id = Convert.ToInt32(sdr["Id"]),
PatientName = Convert.ToString(sdr["PatientName"]),
PatientEmail = Convert.ToString(sdr["PatientEmail"]),
PatientNumber = Convert.ToString(sdr["PatientNumber"]),
Address = Convert.ToString(sdr["Address"]),
BloodGroup = Convert.ToString(sdr["BloodGroup"])
};
}
}
con.Close();
}
}
if (Patient == null)
{
return HttpNotFound();
}
return View(Patient);
}
// 2. *************ADD NEW Patient in the Clinic ******************
// GET: Home/CreatePatient
public ActionResult CreatePatient()
{
return View();
}
// POST: Home/CreatePatient
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult CreatePatient([Bind(Include = "Id,PatientName,PatientEmail,PatientNumber,Address,BloodGroup")] PatientModelVeiw patientModelVeiw)
{
try
{
if (ModelState.IsValid)
{
using (SqlConnection con = new SqlConnection(constr))
{
//inserting Patient data into database
string query = "insert into TblPatient values (@PatientName, @PatientEmail, @PatientNumber,@Address,@BloodGroup)";
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@PatientName", patientModelVeiw.PatientName);
cmd.Parameters.AddWithValue("@PatientEmail", patientModelVeiw.PatientEmail);
cmd.Parameters.AddWithValue("@PatientNumber", patientModelVeiw.PatientNumber);
cmd.Parameters.AddWithValue("@Address", patientModelVeiw.Address);
cmd.Parameters.AddWithValue("@BloodGroup", patientModelVeiw.BloodGroup);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
return RedirectToAction("Index");
}
}
catch
{
}
return View(patientModelVeiw);
}
// 3. *************Update Patient Detail in the Clinic ******************
// GET: Home/UpdatePatient/5
public ActionResult UpdatePatient(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
PatientModelVeiw Patient = new PatientModelVeiw();
string query = "SELECT * FROM TblPatient where Id=" + id;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
Patient = new PatientModelVeiw
{
Id = Convert.ToInt32(sdr["Id"]),
PatientName = Convert.ToString(sdr["PatientName"]),
PatientEmail = Convert.ToString(sdr["PatientEmail"]),
PatientNumber = Convert.ToString(sdr["PatientNumber"]),
Address = Convert.ToString(sdr["Address"]),
BloodGroup = Convert.ToString(sdr["BloodGroup"])
};
}
}
con.Close();
}
}
if (Patient == null)
{
return HttpNotFound();
}
return View(Patient);
}
// POST: Home/UpdatePatient/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult UpdatePatient([Bind(Include = "Id,PatientName,PatientEmail,PatientNumber,Address,BloodGroup")] PatientModelVeiw patientModelVeiw)
{
if (ModelState.IsValid)
{
string query = "UPDATE TblPatient SET PatientName = @PatientName, PatientEmail = @PatientEmail,PatientNumber=@PatientNumber,Address=@Address,BloodGroup=@BloodGroup Where Id =@Id";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@PatientName", patientModelVeiw.PatientName);
cmd.Parameters.AddWithValue("@PatientEmail", patientModelVeiw.PatientEmail);
cmd.Parameters.AddWithValue("@PatientNumber", patientModelVeiw.PatientNumber);
cmd.Parameters.AddWithValue("@Address", patientModelVeiw.Address);
cmd.Parameters.AddWithValue("@BloodGroup", patientModelVeiw.BloodGroup);
cmd.Parameters.AddWithValue("@Id", patientModelVeiw.Id);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
return RedirectToAction("Index");
}
return View(patientModelVeiw);
}
// 3. *************Deelete Patient in the Clinic ******************
// GET: Home/DeletePatient/5
public ActionResult DeletePatient(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
PatientModelVeiw Patient = new PatientModelVeiw();
string query = "SELECT * FROM TblPatient where Id=" + id;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
Patient = new PatientModelVeiw
{
Id = Convert.ToInt32(sdr["Id"]),
PatientName = Convert.ToString(sdr["PatientName"]),
PatientEmail = Convert.ToString(sdr["PatientEmail"]),
PatientNumber = Convert.ToString(sdr["PatientNumber"]),
Address = Convert.ToString(sdr["Address"]),
BloodGroup = Convert.ToString(sdr["BloodGroup"])
};
}
}
con.Close();
}
}
return View(Patient);
}
// POST: Home/DeletePatient/5
[HttpPost, ActionName("DeletePatient")]
[ValidateAntiForgeryToken]
public ActionResult DeletePatient(int id)
{
using (SqlConnection con = new SqlConnection(constr))
{
string query = "Delete FROM TblPatient where Id='" + id + "'";
using (SqlCommand cmd = new SqlCommand(query, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
return RedirectToAction("Index");
}
}
}Now Right-click on Index ActionMethod, add View in Which we Will show the list of the patient.
Input the below details in Add View Dialog box.
- View Name(Name of your view by default action method name): Index
- Template(choose a template from the dropdown, we selected the list because we want to show the list of the patient ): List
- Model Class(model class for which you want to create the view): PatientModelVeiw
- Click Add button to Add View.
Index.cshtml
@model IEnumerable<CrudWithEntity.Models.PatientModelVeiw>
@{
ViewBag.Title = "Index";
}
<h2>List Of Patient</h2>
<p>
@Html.ActionLink("Create New", "CreatePatient")
</p>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.PatientName)
</th>
<th>
@Html.DisplayNameFor(model => model.PatientEmail)
</th>
<th>
@Html.DisplayNameFor(model => model.PatientNumber)
</th>
<th>
@Html.DisplayNameFor(model => model.Address)
</th>
<th>
@Html.DisplayNameFor(model => model.BloodGroup)
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.PatientName)
</td>
<td>
@Html.DisplayFor(modelItem => item.PatientEmail)
</td>
<td>
@Html.DisplayFor(modelItem => item.PatientNumber)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@Html.DisplayFor(modelItem => item.BloodGroup)
</td>
<td>
@Html.ActionLink("Edit Detail", "UpdatePatient", new { id = item.Id }) |
@Html.ActionLink("Show Details", "PatientDetails", new { id = item.Id }) |
@Html.ActionLink("Delete", "DeletePatient", new { id = item.Id })
</td>
</tr>
}
</table>
In the same manner create view for each action method ,Follow same Step for PatientDetails(), AddPatient,UpdatePatient() and DeletePatient () Method.
select Details Template for PatientDetails()
PatientDetails.cshtml
@model CrudWithEntity.Models.PatientModelVeiw
@{
ViewBag.Title = "PatientDetails";
}
<h2>PatientDetails</h2>
<div>
<hr />
<dl class="dl-horizontal">
<dt>
@Html.DisplayNameFor(model => model.PatientName)
</dt>
<dd>
@Html.DisplayFor(model => model.PatientName)
</dd>
<dt>
@Html.DisplayNameFor(model => model.PatientEmail)
</dt>
<dd>
@Html.DisplayFor(model => model.PatientEmail)
</dd>
<dt>
@Html.DisplayNameFor(model => model.PatientNumber)
</dt>
<dd>
@Html.DisplayFor(model => model.PatientNumber)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Address)
</dt>
<dd>
@Html.DisplayFor(model => model.Address)
</dd>
<dt>
@Html.DisplayNameFor(model => model.BloodGroup)
</dt>
<dd>
@Html.DisplayFor(model => model.BloodGroup)
</dd>
</dl>
</div>
<p>
@Html.ActionLink("Edit", "UpdatePatient", new { id = Model.Id }) |
@Html.ActionLink("Back to List", "Index")
</p>
select Create Template for AddPatient() Method
CreatePatient.cshtml
@model CrudWithEntity.Models.PatientModelVeiw
@{
ViewBag.Title = "CreatePatient";
}
<h2>CreatePatient</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.LabelFor(model => model.PatientName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.PatientName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.PatientName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PatientEmail, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.PatientEmail, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.PatientEmail, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PatientNumber, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.PatientNumber, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.PatientNumber, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.BloodGroup, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.BloodGroup, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.BloodGroup, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Sumit" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
select Edit Template for UpdatePatient() Method
UpdatePatient.cshtml
@model CrudWithEntity.Models.PatientModelVeiw
@{
ViewBag.Title = "UpdatePatient";
}
<h2>UpdatePatient</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
@Html.HiddenFor(model => model.Id)
<div class="form-group">
@Html.LabelFor(model => model.PatientName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.PatientName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.PatientName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PatientEmail, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.PatientEmail, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.PatientEmail, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PatientNumber, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.PatientNumber, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.PatientNumber, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.BloodGroup, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.BloodGroup, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.BloodGroup, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Sumit" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
select Delete Template for DeletePatient()
DeletePatient.cshtml
@model CrudWithEntity.Models.PatientModelVeiw
@{
ViewBag.Title = "DeletePatient";
}
<h2>DeletePatient</h2>
<div>
<hr />
<dl class="dl-horizontal">
<dt>
@Html.DisplayNameFor(model => model.PatientName)
</dt>
<dd>
@Html.DisplayFor(model => model.PatientName)
</dd>
<dt>
@Html.DisplayNameFor(model => model.PatientEmail)
</dt>
<dd>
@Html.DisplayFor(model => model.PatientEmail)
</dd>
<dt>
@Html.DisplayNameFor(model => model.PatientNumber)
</dt>
<dd>
@Html.DisplayFor(model => model.PatientNumber)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Address)
</dt>
<dd>
@Html.DisplayFor(model => model.Address)
</dd>
<dt>
@Html.DisplayNameFor(model => model.BloodGroup)
</dt>
<dd>
@Html.DisplayFor(model => model.BloodGroup)
</dd>
</dl>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-actions no-color">
<input type="submit" value="Delete Patient" class="btn btn-danger" /> |
@Html.ActionLink("Back to List", "Index")
</div>
}
</div>
Now Project should look like the below image
Now run your project and perform the opration
How ASP.NET MVC Works? Simple Explanation.
How ASP.NET MVC Works: ASP.NET MVC is a completely new framework designed by Microsoft to create ASP.NET based web applications, which completely takes care of SoC (Separation of Concerns) and Testability.
Using this, while doing web development, we only do development under the stateless Behavior of the web, where we have to fulfill the need of statefulness by other methods such as Sessions and Cookies.
Therefore, while using this model, we can access and manipulate each part of the webpage with various core technologies such as HTML, CSS, JavaScript, etc., which Microsoft Hide to achieve simulation like Statefulness under WebForms.
In other words, if we do web development using ASP.NET WebForms technology, then we do not need to understand these Core Technologies properly. But when we do our web development using ASP.NET MVC technology, then we need to have adequate and appropriate knowledge of these core technologies.
In this model, there are two completely different steps to be followed for both processing Incoming Requests and generating Appropriate HTML Markup for Web Browser as Response, which is known as Controller and View.
The Controller Part of the Web Application accepts the incoming Incoming Request and decides what Action to Perform. After the Action is performed, the Controller sends the raw Response Data to the part named View of the application.
View Engine retrieves the raw Response data from the Controller and writes the Markup Render as Output in Web Browser, in Output Stream of Web Browser.
In model, the Controller Part of Web Application handles the work related to the processing of Web Application, while the View Part prepares the Response Data generated from that processing to be presented in the Web Browser. While the formatting of the Response that is returned by this View Part is done separately by CSS and by JavaScript, the task of managing Behavior and interactions under Webpage is done separately.
That is, no individual webpage of a web application based on an ASP.NET MVC Model is dependent on any Physical ASPX Server File. Although the ASPX file is also a part of our Web Project in this model, this ASPX file represents only the Plain HTML Template Part of our Web Application, which is sent in the View Engine Part, Web Browser of this Model Response uses HTML as a template to create. That is, there is no Code-Behind File in this model.
When we are creating our web application using ASP.NET’s MVC Model, we have to think and develop our web application not around UI or Code-Behind File but around Controllers and Actions. As many HTTP requests are performed in this model, they have to be mapped as a Controller and Action Pair.
Whenever an Action Execute occurs, the data is produced from the execution of that Action. View Engine receives this Raw Data and a Template File and generates Final Markup, JSON, JavaScript, etc… by placing Raw Data on Appropriate Locations in the Template File, as Response for Rendering / Using on Web Browser Is returned.
Read Similar Articles
- How to write raw sql query with custom model in entity framework core
- XUnit: Mock IMemoryCache In Unit Test | How To Mock IMemoryCache In ASP.NET Core
- [Simple Way]-Fetch Data From API and Display In Table React Js
- [Solved]-Connection to node -1 (/127.0.0.1:9092) could not be established. Broker may not be available
