CRUD Operations With ASP.NET Core MVC Using ADO.NET

ASP.NET CORE

Introduction

In this article, we will learn how to create a web application using ASP.NET Core MVC with ADO.NET in Visual Studio Code. So, in this post, We will create a sample MVC application and performing CRUD operations on it. Please, read our previous article before proceeding to this article where we understand the Dotnet CLI Command line tool for ASP.NET Core.

Prerequisites

1] Install .NET Core MVC 2.0.0 or above.

2] Install Visual Studio Code.

3] SQL Server 2008 or above.

Creating the Table and Stored Procedures

Now, Let’s go and Open SQL Server to create the Student table in the database. we will be store all records in the tblStudent table. so, use the following script to the tblStudent table.

Create table tblStudent(        
    StudId int IDENTITY(1,1) NOT NULL,        
    Name varchar(20) NOT NULL,        
    City varchar(20) NOT NULL,        
    Department varchar(20) NOT NULL,        
    Gender varchar(6) NOT NULL        
)

So, we need to create stored procedures to get, add, update and delete students records.

1] Insert Student Record

Create procedure spAddstudent         
(        
    @Name VARCHAR(20),         
    @City VARCHAR(20),        
    @Department VARCHAR(20),        
    @Gender VARCHAR(6)        
)        
as         
Begin         
    Insert into tblstudent (Name,City,Department, Gender)         
    Values (@Name,@City,@Department, @Gender)         
End

2] Update an Student Record

Create procedure spUpdateStudent          
(          
   @StudId INTEGER ,        
   @Name VARCHAR(20),         
   @City VARCHAR(20),        
   @Department VARCHAR(20),        
   @Gender VARCHAR(6)        
)          
as          
begin          
   Update tblStudent           
   set Name=@Name,          
   City=@City,          
   Department=@Department,        
   Gender=@Gender          
   where StudentId=@StudId          
End

3] Delete an Student Record

Create procedure spDeleteStudent         
(          
   @StudId int          
)          
as           
begin          
   Delete from tblStudent where StudentId=@StudId          
End

//4] View all Student Records

Create procedure spGetAllStudent      
as      
Begin      
    select *      
    from tblStudent   
    order by StudentId 
End

After that, our Database part has been completed. So Let’s go ahead and we will create the MVC application using Visual Studio code.

Create the MVC Web Application

We will create a new project from the terminal window in Visual Studio Code. So, let’s Open the Visual Studio Code and navigate to view >> Integrated Terminal.

Now, Let’s, go to visual studio code and open the terminal window. After that, we’ll create a new project, for our MvcCoreDemo. an example project using the following command.

C:Users\Monk> mkdir MvcCoreDemo

C:Users\Monk> cd .\MvcCoreDemo\

C:Users\Monk>dotnet new mvc MvcCoreDemo

Project Structure

Now, you will see these project structure in the solution explorer, and all the folders created with the name of Controllers, Models, and Views and other files are generated automatically.

Asp.net core mvc project structure

Let’s go and click on the Models folder. then select “New File”. Name it Student.cs It will create the file inside the Models folder.

adding models in asp.net core mvc application

then the open student.cs file and put the following code inside it. and add required validators and properties fields inside class with the help of System.ComponentModel.DataAnnotations.

using System;    
using System.Collections.Generic;    
using System.ComponentModel.DataAnnotations;    
using System.Linq;    
using System.Threading.Tasks;    
    
namespace MVCCoreDemo.Models    
{    
    public class Student    
    {    
        public int StudId { get; set; }    
        [Required]    
        public string Name { get; set; }    
        [Required]    
        public string Gender { get; set; }    
        [Required]    
        public string Department { get; set; }    
        [Required]    
        public string City { get; set; }    
    }    
}

Again, we will create the class for Database operations. So let’s add a file to the Model folder. Name it StudentDataAccessLayer.cs. Now, we need to add the following code inside the class. with, connection string.

using System;    
using System.Collections.Generic;    
using System.Data;    
using System.Data.SqlClient;    
using System.Linq;    
using System.Threading.Tasks;    
    
namespace MVCoreDemo.Models    
{    
    public class StudentDataAccessLayer    
    {    
        string connectionString = "Your Connection String here";    
    
        //To View all Student details      
        public IEnumerable<Student> GetAllStudent()    
        {    
            List<Student> studList = new List<Student>();    
    
            using (SqlConnection con = new SqlConnection(connectionString))    
            {    
                SqlCommand cmd = new SqlCommand("spGetAllStudent", con);    
                cmd.CommandType = CommandType.StoredProcedure;    
    
                con.Open();    
                SqlDataReader rdr = cmd.ExecuteReader();    
    
                while (rdr.Read())    
                {    
                    Student stud = new Student();    
    
                    stud.StudID = Convert.ToInt32(rdr["StudentID"]);    
                    stud.Name = rdr["Name"].ToString();    
                    stud.Gender = rdr["Gender"].ToString();    
                    stud.Department = rdr["Department"].ToString();    
                    stud.City = rdr["City"].ToString();    
    
                    studList.Add(stud);    
                }    
                con.Close();    
            }    
            return studList;    
        }    
    
        //To Add new student record      
        public void AddStudent(Student student)    
        {    
            using (SqlConnection con = new SqlConnection(connectionString))    
            {    
                SqlCommand cmd = new SqlCommand("spAddStudent", con);    
                cmd.CommandType = CommandType.StoredProcedure;    
    
                cmd.Parameters.AddWithValue("@Name", student.Name);    
                cmd.Parameters.AddWithValue("@Gender", student.Gender);    
                cmd.Parameters.AddWithValue("@Department", student.Department);    
                cmd.Parameters.AddWithValue("@City", student.City);    
    
                con.Open();    
                cmd.ExecuteNonQuery();    
                con.Close();    
            }    
        }    
    
        //To Update the records of a individual student    
        public void UpdateStudent(Student student)    
        {    
            using (SqlConnection con = new SqlConnection(connectionString))    
            {    
                SqlCommand cmd = new SqlCommand("spUpdateStudent", con);    
                cmd.CommandType = CommandType.StoredProcedure;    
    
                cmd.Parameters.AddWithValue("@StudId", student.ID);    
                cmd.Parameters.AddWithValue("@Name", student.Name);    
                cmd.Parameters.AddWithValue("@Gender", student.Gender);    
                cmd.Parameters.AddWithValue("@Department", student.Department);    
                cmd.Parameters.AddWithValue("@City", student.City);    
    
                con.Open();    
                cmd.ExecuteNonQuery();    
                con.Close();    
            }    
        }    
    
        //Get the details of a individual student    
        public Student GetStudentData(int? id)    
        {    
            Student student = new Student();    
    
            using (SqlConnection con = new SqlConnection(connectionString))    
            {    
                string sqlQuery = "SELECT * FROM tblStudent WHERE StudentID= " + id;    
                SqlCommand cmd = new SqlCommand(sqlQuery, con);    
    
                con.Open();    
                SqlDataReader rdr = cmd.ExecuteReader();    
    
                while (rdr.Read())    
                {    
                    student.ID = Convert.ToInt32(rdr["StudID"]);    
                    student.Name = rdr["Name"].ToString();    
                    student.Gender = rdr["Gender"].ToString();    
                    student.Department = rdr["Department"].ToString();    
                    student.City = rdr["City"].ToString();    
                }    
            }    
            return student;    
        }    
    
        //To Delete the record on a particular student    
        public void DeleteStudent(int? id)    
        {    
    
            using (SqlConnection con = new SqlConnection(connectionString))    
            {    
                SqlCommand cmd = new SqlCommand("spDeleteStudent", con);    
                cmd.CommandType = CommandType.StoredProcedure;    
    
                cmd.Parameters.AddWithValue("@StudId", id);    
    
                con.Open();    
                cmd.ExecuteNonQuery();    
                con.Close();    
            }    
        }    
    }    
}

Now, Inside the visual studio code. we need to add the Nuget package reference to System.Data.SqlClient. into
the MvcCoreDemo.csproj file.

<PackageReference Include="System.Data.SqlClient" Version="4.4.0" />

Adding the Controller to the Application

Let’s go to our project and Right-click on the Controllers folder and then select the “New File”. Name it StudentController.cs. it will handle all requests and responses of the client and server.

adding controller in asp.net core mvc

Adding Views to the Application

So, we need to add Views for our controller class, Let’s Right-click on Views folder and create new folder Student.

Then, add the view files into the student folder. we need to add .cshtml files into the student folder.So let’s create Razor views for Details.cshtml,Create.cshtml
,Delete.cshtml and Edit.cshtml.

adding razor views in asp.net core mvc

So, Let’s add code into the razor view for performing CRUD operations. The Index view will display all student records available in the database. Let’s open the Index.cshtml file and put the following code inside it.

@model IEnumerable<MVCCoreDemo.Models.Student>    
       
@{    
        ViewData["Title"] = "Index";    
    }    
<h2>Index</h2>    
<p>    
    <a asp-action="Create">Create New</a>    
</p>    
<table class="table">    
    <thead>    
        <tr>    
            <th>    
                @Html.DisplayNameFor(model => model.Name)    
            </th>    
            <th>    
                @Html.DisplayNameFor(model => model.Gender)    
            </th>    
            <th>    
                @Html.DisplayNameFor(model => model.Department)    
            </th>    
            <th>    
                @Html.DisplayNameFor(model => model.City)    
            </th>    
            <th></th>    
        </tr>    
    </thead>    
    <tbody>    
        @foreach (var item in Model)    
{    
            <tr>    
                <td>    
                    @Html.DisplayFor(modelItem => item.Name)    
                </td>    
                <td>    
                    @Html.DisplayFor(modelItem => item.Gender)    
                </td>    
                <td>    
                    @Html.DisplayFor(modelItem => item.Department)    
                </td>    
                <td>    
                    @Html.DisplayFor(modelItem => item.City)    
                </td>    
                <td>    
                    <a asp-action="Edit" asp-route-id="@item.ID">Edit</a> |    
                    <a asp-action="Details" asp-route-id="@item.ID">Details</a> |    
                    <a asp-action="Delete" asp-route-id="@item.ID">Delete</a>    
                </td>    
            </tr>    
        }    
    </tbody>    
</table>

Now, we need to add the code for Index Action. Let’s go to StudentController.cs and put the following code inside it.

using System;  
using System.Collections.Generic;  
using System.Diagnostics;  
using System.Linq;  
using System.Threading.Tasks;  
using Microsoft.AspNetCore.Mvc;  
using MVCAdoDemo.Models;  
  
namespace MVCCoreDemo.Controllers  
{  
    public class StudentController : Controller  
    {  
        StudentDataAccessLayer objstudent = new StudentDataAccessLayer();  
  
        public IActionResult Index()  
        {  
            List<Student> lststudent = new List<student>();  
            lstStudent = objstudent.GetAllStudents().ToList();  
  
            return View(lstStudent);  
        }  
     }  
}

We have created the StudentDataAccessLayer class object. inside, the StudentController class. for, handling database related operations.

Create View

The create view will add the new student record into the database. Now, open the Create.cshtml view file and put the following code into it.

@model MVCCoreDemo.Models.Student    
    
@{    
    ViewData["Title"] = "Create";    
}    
<h2>Create</h2>    
<h4>Students</h4>    
<hr />    
<div class="row">    
    <div class="col-md-4">    
        <form asp-action="Create">    
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>    
            <div class="form-group">    
                <label asp-for="Name" class="control-label"></label>    
                <input asp-for="Name" class="form-control" />    
                <span asp-validation-for="Name" class="text-danger"></span>    
            </div>    
            <div class="form-group">    
                <label asp-for="Gender" class="control-label"></label>    
                <select asp-for="Gender" class="form-control">    
                    <option value="">-- Select Gender --</option>    
                    <option value="Male">Male</option>    
                    <option value="Female">Female</option>    
                </select>    
                <span asp-validation-for="Gender" class="text-danger"></span>    
            </div>    
            <div class="form-group">    
                <label asp-for="Department" class="control-label"></label>    
                <input asp-for="Department" class="form-control" />    
                <span asp-validation-for="Department" class="text-danger"></span>    
            </div>    
            <div class="form-group">    
                <label asp-for="City" class="control-label"></label>    
                <input asp-for="City" class="form-control" />    
                <span asp-validation-for="City" class="text-danger"></span>    
            </div>    
            <div class="form-group">    
                <input type="submit" value="Create" class="btn btn-default" />    
            </div>    
        </form>    
    </div>    
</div>    
<div>    
    <a asp-action="Index">Back to List</a>    
</div>    
@section Scripts {    
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}    
}

Now, Let’s handle the business logic into the StudentController.cs for Create operation. put the following code into inside it.

[HttpGet]  
public IActionResult Create()  
{  
    return View();  
}  
  
[HttpPost]  
[ValidateAntiForgeryToken]  
public IActionResult Create([Bind] Student student)  
{  
    if (ModelState.IsValid)  
    {  
        objstudent.AddStudent(student);  
        return RedirectToAction("Index");  
    }  
    return View(student);  
}

The [Bind] attribute and bind the model properties to input from keys.

Edit View

In this, We will enable edit for the existing data. Let’s open the Edit.cshtml file and add the following code inside it.

@model MVCAdoDemo.Models.Employee    
    
@{    
    ViewData["Title"] = "Edit";    
}    
<h2>Edit</h2>    
<h4>Students</h4>    
<hr />    
<div class="row">    
    <div class="col-md-4">    
        <form asp-action="Edit">    
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>    
            <input type="hidden" asp-for="ID" />    
            <div class="form-group">    
                <label asp-for="Name" class="control-label"></label>    
                <input asp-for="Name" class="form-control" />    
                <span asp-validation-for="Name" class="text-danger"></span>    
            </div>    
            <div class="form-group">    
                <label asp-for="Gender" class="control-label"></label>    
                <select asp-for="Gender" class="form-control">    
                    <option value="">-- Select Gender --</option>    
                    <option value="Male">Male</option>    
                    <option value="Female">Female</option>    
                </select>    
                <span asp-validation-for="Gender" class="text-danger"></span>    
            </div>    
            <div class="form-group">    
                <label asp-for="Department" class="control-label"></label>    
                <input asp-for="Department" class="form-control" />    
                <span asp-validation-for="Department" class="text-danger"></span>    
            </div>    
            <div class="form-group">    
                <label asp-for="City" class="control-label"></label>    
                <input asp-for="City" class="form-control" />    
                <span asp-validation-for="City" class="text-danger"></span>    
            </div>                
            <div class="form-group">    
                <input type="submit" value="Save" class="btn btn-default" />    
            </div>    
        </form>    
    </div>    
</div>    
<div>    
    <a asp-action="Index">Back to List</a>    
</div>    
@section Scripts {    
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}    
}

So, we need to add the business logic for the Edit view. let’s add the following into the StudentController.

[HttpGet]  
public IActionResult Edit(int? id)  
{  
    if (id == null)  
    {  
        return NotFound();  
    }  
Student student = objstudent.GetStudentData(id);  
  
    if (employee == null)  
    {  
        return NotFound();  
    }  
    return View(employee);  
}  
  
[HttpPut]  
[ValidateAntiForgeryToken]  
public IActionResult Edit(int id, [Bind]Student student)  
{  
    if (id != student.ID)  
    {  
        return NotFound();  
    }  
    if (ModelState.IsValid)  
    {  
        objemployee.UpdateStudent(student);  
        return RedirectToAction("Index");  
    }  
    return View(student);  
}

here, we have two action methods for HttpGet and HttpPut. the HttpGet edit method fetches the student data. and another HttpPut request will handle update the record.

Details View

This view will display the particular student records in details. So, open the Details.cshtml. file and put the following code in it.

@model MVCAdoDemo.Models.Employee    
    
@{    
    ViewData["Title"] = "Details";    
}    
<h2>Details</h2>    
<div>    
    <h4>Students</h4>    
    <hr />    
    <dl class="dl-horizontal">    
        <dt>    
            @Html.DisplayNameFor(model => model.Name)    
        </dt>    
        <dd>    
            @Html.DisplayFor(model => model.Name)    
        </dd>    
        <dt>    
            @Html.DisplayNameFor(model => model.Gender)    
        </dt>    
        <dd>    
            @Html.DisplayFor(model => model.Gender)    
        </dd>    
        <dt>    
            @Html.DisplayNameFor(model => model.Department)    
        </dt>    
        <dd>    
            @Html.DisplayFor(model => model.Department)    
        </dd>    
        <dt>    
            @Html.DisplayNameFor(model => model.City)    
        </dt>    
        <dd>    
            @Html.DisplayFor(model => model.City)    
        </dd>    
    </dl>    
</div>    
<div>    
    <a asp-action="Edit" asp-route-id="@Model.ID">Edit</a> |    
    <a asp-action="Index">Back to List</a>    
</div>

Now, we need to handle Details view the business logic inside the StudentController.cs. so let’s add the following code.

[HttpGet]  
public IActionResult Details(int? id)  
{  
    if (id == null)  
    {  
        return NotFound();  
    }  
    Student student = objstudent.GetStudentData(id);  
  
    if (student == null)  
    {  
        return NotFound();  
    }  
    return View(student);  
}

Delete View

This View will remove the student records. So, add the following code inside the Delete.cshtml.

@model MVCCoreDemo.Models.Student    
    
@{    
    ViewData["Title"] = "Delete";    
}    
<h2>Delete</h2>    
<h3>Are you sure you want to delete this?</h3>    
<div>    
    <h4>Employees</h4>    
    <hr />    
    <dl class="dl-horizontal">    
        <dt>    
            @Html.DisplayNameFor(model => model.Name)    
        </dt>    
        <dd>    
            @Html.DisplayFor(model => model.Name)    
        </dd>    
        <dt>    
            @Html.DisplayNameFor(model => model.Gender)    
        </dt>    
        <dd>    
            @Html.DisplayFor(model => model.Gender)    
        </dd>    
        <dt>    
            @Html.DisplayNameFor(model => model.Department)    
        </dt>    
        <dd>    
            @Html.DisplayFor(model => model.Department)    
        </dd>    
        <dt>    
            @Html.DisplayNameFor(model => model.City)    
        </dt>    
        <dd>    
            @Html.DisplayFor(model => model.City)    
        </dd>    
    </dl>    
    
    <form asp-action="Delete">    
        <input type="hidden" asp-for="ID" />    
        <input type="submit" value="Delete" class="btn btn-default" /> |    
        <a asp-action="Index">Back to List</a>    
    </form>    
</div>

Now, we need to add business logic code for the Delete view. inside, the StudentController.cs. Then, add the following code.

[HttpGet]  
public IActionResult Delete(int? id)  
{  
    if (id == null)  
    {  
        return NotFound();  
    }  
    Student student = objstudent.GetStudentData(id);  
  
    if (student == null)  
    {  
        return NotFound();  
    }  
    return View(student);  
}  
  
[HttpPost, ActionName("Delete")]  
[ValidateAntiForgeryToken]  
public IActionResult DeleteConfirmed(int? id)  
{  
    objstudent.DeleteStudent(id);  
    return RedirectToAction("Index");  
}

we have implemented the two methods with the same name for the delete operations. but we getting conflict, at the compile-time error. So we need to add the attribute routing for route mapping ActionName(“Delete”).

Configure the default route URL

Let’s, open Startup.cs class file and configure the URL route, Before launching the application. so, let’s add app.UseMvc() method.

app.UseMvc(routes =>  
 {  
     routes.MapRoute(  
         name: "default",  
         template: "{controller=Home}/{action=Index}/{id?}");  
 });

In this Route pattern, we set the HomeController as the default controller and index method. and the Id parameter is optional.

Now, let’s go and build our application. see the output

asp.net core mvc index records

Here, we have generate Create form.

razor view create form

Thank you for reading this article, I hope you will understand the CRUD Operations With ASP.NET Core MVC Using ADO.NET. We’ll get into more details about this when we’ll learn ASP.NET Core Project Structure in the next article.

Leave a Reply

Your email address will not be published.