Crud Operations in MVC Without Entity Framework

In my last post we talked about inserting, updating and deleting data in MVC with Entity Framework. In this article we are going to use insert, update and remove (crud operation) functionalities in an MVC 5 web application without using Entity Framework, which means that in this article we will use a database connection and SQLCommand to perform a crud operation instead of Entity Framework.

So let’s start by determining step by step how to easily meet this requirement in each project. We will try to create a simple view where the user can see all the record lists in the table, with an add button to add new records, an edit button to update existing records, and delete links to remove existing records with a confirmation warning.

, we can divide the whole process into one step.

Add a new empty ASP.NET MVC project to Visual Studio
Creating a database, a table for performing a dirt cleaning
Add a controller
Adding and checking model classes with data annotation
Added an action method to the controller logic and database entries.
Installation and commissioning of the project

So let’s start by creating a Mvc .Net project.

Step 1 – First, create an ASP.NET MVC application, open a Visual Studio and add an empty MVC project.

Step 2. Create an SQL table to perform a CRUD operation

In this step we will create a database table, for our database operation. For this I created a table with the name TblPatient. There are columns Id, PatientName, PatientNumber, PatientEmail, Address and BloodGroup
.

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,
[Blood Type] [nvarchar](100) NULL,
CONSTRAINT [PK_TblPatient] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY].

Step 3 – Now create a controller in the Controllers folder named HomeController.

using System ;
using System.Collections.Generic ;
using System.Linq ;
using System.Web ;
using System.Web.Mvc ;

CrudWithEntity.Controllers namespace
{
public class HomeController : Controller
{
// GET : Home
public ActionResult Index()
{
return View();
}
}
}

Step 4 – Right-click on the models folder in your Add Class project. Select the Class Form menu item and create a new class PatientModelVeiw.cs.

PatientModelVeiw.cs

public class PatientModelVeiw
{
[Display(Name = ID)] public int Id { get ; set ; }

Mandatory (ErrorMessage = Patient name required.)] public string Patient name { get ; set ; }

Required(ErrorMessage = Required patient number.)][DataType(DataType.PhoneNumber)][RegularExpression(@^(?([0-9]{3})) ?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$, ErrorMessage = Invalid patient phone number)]]public string PatientNumber { get; set; }

Mandatory (ErrorMessage = Patient email address required.)] [EmailAddress(ErrorMessage = Patient email address invalid)] public string PatientEmail { get ; set ; }

Mandatory (ErrorMessage = Patient Address Required.)] public string Address { get ; set ; }

ErrorMessage = Blood group is required.)] public string BloodGroup { get; set; }
}

I use data annotations to validate the data in our model. In simple terms, we can think of data annotations as a set of checks that we use in our model class to validate the data entered by the user.
ASP.NET MVC provides the Data Annotation attribute in the System.ComponentModel.DataAnnotations namespace that can be used to validate models.

Step six. Now we will write the logic into our controller.

Open the HomeController and add the following action methods. Here I add 5 action methods.

1. 1. To view all patients in the clinic – Index()
2. To view patient details – PatientDetails()
3. Adding a new patient – AddPatient()
4. Update patient information – UpdatePatient()
5. Delete a 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 ;

CrudWithEntity.Controllers namespace
{
public class HomeController : Controller
{.

// 1. ************* See the list of patients 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 Patients = new List() ;
string query = SELECT * FROM TblPatient;
using (SqlConnection con = new SqlConnection(constr)
{
using (SqlCommand cmd = new SqlCommand(request))
{
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/5public 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(request)){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){returns HttpNotFound();}returns View(Patient);}

// 2. *************ADD NEW Patient in Clinic ******************
// GET : Home/Create a Patient
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))
{
/Patient data in database
string query = insert into TblPatient values (@PatientName, @PatientEmail, @PatientNumber,@Adress,@BloodGroup);
using (SqlCommand cmd = new SqlCommand(request, 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 information in the clinic ******************

// GET : Home/UpdatePatient/5public 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(request)){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){returns HttpNotFound();}returns View(Patient);}

// POST : Home/UpdatePatient/5// To protect against attack overload, include 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,PatientNumber=@PatientNumber,Address=@Address,BloodGroup=@BloodGroup Where Id =@Id;using (SqlConnection con = new SqlConnection(constr)){using (SqlCommand cmd = new SqlCommand(request)){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.AddWithValue(@BloodGroup, patientModelVeiw.BloodGroup);cmd.Parameters.AddWithValue(@Id, patientModelVeiw.Id);con.Open();cmd.ExecuteNonQuery();con.Close();}.}return RedirectToAction(Index);}return View(patientModelVeiw);}

// 3. *************Dilettante patients 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(request))
{
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]),
Blood Group = Convert.ToString(sdr[Blood Type]),
} ;
}
}
con.Close() ;
}
}
return View(Patient) ;
}

// POST : Home/DeletePatient/5
[HttpPost, ActionName(DeletePatient)] [ValidateAntiForgeryToken] public ActionResult DeletePatient(id)
{
using (SqlConnection con = new SqlConnection(const))
{
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 where we will display the list of patients.

Enter the following information in the Add View dialog box.

Image name (default image name) : Index
Template (choose a template from the drop-down list, we chose List because we want to display the list of patients) : List
Model class (the model class for which you want to create the view) : PatientModelVeiw
Click the Add button to add a view.

Index.cshtml

Model @IEnumerable

@{
ViewBag.Title = Index ;
}

Patient list

@html. ActionLink(Create new, Create patient)

@foreach (var item in model)
{}

@Html.DisplayNameFor(model => model.PatientName)	@Html.DisplayNameFor(model => model.PatientEmail)	@Html.DisplayNameFor(model => model.PatientNumber)	@Html.DisplayNameFor(model => model.Address)	@Html.DisplayNameFor(model => model.Bloodgroup)
@Html.DisplayFor(modelItem => item.PatientName)	@Html.DisplayFor(modelItem => item.PatientEmail)	@Html.DisplayFor(modelItem => item.PatientNumber)	@Html.DisplayFor(modelItem => item.Address)	@Html.DisplayFor(modelItem => item.BloodGroup)	@Html.ActionLink(Edit information, updatePatient, new { id = item.Id }) \| @Html.ActionLink(View information, PatientDetails, new { id = item.Id }) \| @Html.ActionLink(Delete, removePatient, new { id = item.Id })

Also, create a view for each action method. Do the same for the PatientDetails(), AddPatient, UpdatePatient(), and DeletePatient() methods.

Details Choose template for PatientDetails()

PatientDetails.cshtml

CrudWithEntity.Models.PatientModelVeiw
@{
ViewBag.Title = PatientDetails;
}

Patient data

@Html.DisplayNameFor(model => model.patient name)

@Html.DisplayFor(model => model.name patient)

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

@Html.DisplayFor(template => template.PatientEmail)

@Html.DisplayNameFor(Model => Model.PatientNumber(ModelNumber))

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

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

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

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

@html. DisplayFor(model => model.Bloodgroup)

@Html.ActionLink(Edit, updatePatient, new { id = Model.Id}) |
@Html.ActionLink(Return to list, Pointer)

Select the option Create a template for the AddPatient() method.

create patient.cshtml

CrudWithEntity.Models.PatientModelVeiw
@{
ViewBag.Title = CreatePatient;
}

CreatePatient

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

@Html.ValidationSummary(true, , new { @class = text-hazard }))

@Html.LabelFor(model => model.PatientName, htmlAttributes : new { @class = control-label col-md-2 }))

@Html.EditorFor(model => model.PatientName, new { htmlAttributes = new { @class = form-control } })
@Html.ValidationMessageFor(model => model.PatientName, , new { @class = text hazard })

@Html.LabelFor(model => model.PatientEmail, htmlAttributes : new { @class = control-label col-md-2 }))

@Html.EditorFor(model => model.PatientEmail, new { htmlAttributes = new { @class = form-control } })
@Html.ValidationMessageFor(model => model.PatientEmail, new { @class = text-danger }}

@Html.LabelFor(model => model.PatientNumber, htmlAttributes : new { @class = control-label col-md-2 }))

@Html.EditorFor(model => model.PatientNumber, new { htmlAttributes = new { @class = form-control } })
@Html.ValidationMessageFor(model => model.PatientNumber, , new { @class = text-hazard })

@Html.LabelFor(model => model.Address, htmlAttributes : new { @class = controllabel col-md-2 })

@Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = form-control } })
@Html.ValidationMessageFor(model => model.Address, new { @class = text-danger }}

@Html.LabelFor(model => model.BloodGroup, htmlAttributes : new { @class = control-label col-md-2 }))

@Html.EditorFor(model => model.BloodGroup, new { htmlAttributes = new { @class = form-control } })
@Html.ValidationMessageFor(model => model.BloodGroup, , new { @class = text-danger })

}

@html. ActionLink(Return to list, Index)

Choose Change template for the UpdatePatient() method.

UpdatePrint.cshtml

CrudWithEntity.Models.PatientModelVeiw
@{
ViewBag.Title = UpdatePatient;
}

UpdatePatient

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

@Html.ValidationSummary(true, , new { @class = text hazard })
@Html.HiddenFor(model => model.Id)