Yogi

SW Engg

ASP.NET CORE — Learn CRUD Operations in ADO.NET from Zero to Hero

CRUD Operations in ADO.NET ASP.NET Core

In this ASP.NET Core Tutorial you will learn to do CRUD operations in ADO.NET from the very beginning. This tutorial will help you to learn ADO.NET so that you can start doing database programming in ASP.NET CORE with C#.

Download the full Source code of this application from GitHub
________________________________________________________

Audience

This tutorial is made for everyone who wants to learn ASP.NET Core and ADO.NET quickly. All the things are explained in easy & step by step manner. You can download the complete source code and run them in your PC.

Here I will do the CRUD (CREATE, READ, UPDATE & DELETE) Operations on a teachers table in the SQL Server database.

I personally believe that “ The best way to learn any programming language is by building the CRUD operations at the first place”.

This tutorial is divided into following section:

1. Creating a new ASP.NET Core Web Application
2. Configuring ASP.NET Core Web Application
3. Create Layout, ViewImports, ViewStart and add Bootstrap
4. Creating a Database
5. Adding Connection String of the Database in the Application
6. Adding Model class for Teacher
7. Adding Client Side Validation feature
8. Performing the CREATE Teacher Functionality
9. Performing the READ Teacher Functionality
10. Performing the UPDATE Teacher Functionality
11. Performing the DELETE Teacher Functionality

1. Creating a new ASP.NET Core Web Application

In your Visual Studio, select ‘File > New Project’. A new window will get opened, here:

a. On the left side, select ‘Installed > Visual C# > Web’.
b. On the middle part, select ‘ASP.NET Core Web Application’.
c. On the lower part, give your project name as ‘CRUDADO’ or anything you want, and select the location on the drive where project files will be stored. Also select the option — ‘Create directory for solution’ and finally click the ‘OK’ button to create your project.
Create ASP.NET Core Web Application

Next, a new window will open that ask you to select the project template, dot net framework and a few more things. In this window make sure you select these things:

a. On the 2 drop downs at the top, select ‘.NET Core’ and ‘ASP.NET Core 2.0’. You can also select ASP.NET Core version greater than 2.0.
b. Select the ‘Empty’ project template.
c. Click the ‘OK’ button to create your ASP.NET Core Web Application
Select Properties for ASP.NET Core Application

2. Configuring ASP.NET Core Web Application

Your application should have support of MVC framework, routing and Static files. All this can be done from the ‘Startup.cs’ class which is located in the root of the application.

Open the Startup.cs class and add these supports inside the ConfigureServices() and Configure() methods as shown below:

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.DependencyInjection;
namespace CRUDADO
{
public class Startup
{
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc();
}
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
app.UseStaticFiles();
app.UseDeveloperExceptionPage();
app.UseMvc(routes =>
{
routes.MapRoute(
name: "default",
template: "{controller=Home}/{action=Index}/{id?}");
});
}
}
}

3. Create Layout, ViewImports, ViewStart and add Bootstrap

Create ‘Views’ folder in the root of your project, and inside it create a ‘Shared’ folder.

Next, right click on the ‘Shared’ folder, and select ‘Add > New Item’. You will get a new window showing the item list, from this list select ‘MVC View Layout Page’ and click the ‘Add’ button.

This will add _Layout.cshtml file in the Shared folder and it will be open for editing. You need to add the following code to it:

<!DOCTYPE html>
<html>
<head>
<title>@ViewData["Title"]</title>
<meta name="viewport" content="width=device-width" />
<link href="/lib/bootstrap/dist/css/bootstrap.css" rel="stylesheet" />
</head>
<body class="m-1 p-1">
@RenderBody()
</body>
</html>

Next, download the Bootstrap Package and extract the Bootstrap zip file contents inside the ‘wwwroot > lib’ folder of your project.

Bootstrap is used to create responsive layout that are mobile friendly. It is the most popular CSS framework in the world.

Next, right click on the ‘Views’ folder and select ‘Add > New Item’. On the items list, select ‘MVC View Imports Page’ and click the ‘Add’ button. This will add _ViewImports.cshtml file.

Import the built-in tag helpers & models namespace for the Views, by adding the following code to this file:

@using CRUDADO.Models
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

In the same way, right click the ‘Views’ folder and select ‘Add > New Item’. On the items list, select ‘MVC View Start Page’ and click the ‘Add’ button.

This will add the _ViewStart.cshtml file with the following initial code:

@{
Layout = "_Layout";
}

It tells that all the Views will have the Layout from the _Layout.cshtml file which is located inside the Shared folder.

4. Creating a Database

You must have SQL Server 2017 (any edition) installed in your PC. However, any other edition of SQL Server like 2016, 2008, 2012, will work. You can use the Developer and Express editions of SQL Server 2017 which are free to use — Download SQL Server 2017.

Next, you need to connect to the SQL Server which you can do with SQL Server Object Explorer in Visual Studio. So select ‘View > SQL Server Object Explorer in Visual Studio’ in Visual Studio.

When ‘SQL Server Object Explorer’ window opens, right click on the SQL Server node and select Add SQL Server.

Add SQL Server

Next, a Connect window opens up. In this window, select the ‘Browse’ tab then click on the ‘Local’ node to open it.

Inside this node, select the 2nd option that says (localdb)\MSSQLLocalDB. After selecting it, you will notice the ‘Server Name:’ field, in the same window, now has the (localdb)\MSSQLLocalDB value.

Now you have to simply click the Connect button to connect to the SQL Server. See the below image:

Connecting SQL Server in SQL Server Object Explorer

In a few seconds time the SQL Server will be connected and ready to use.

Next, right click on Databases node and select Add New Database.

Add New Database

In the dialog box, enter School as the database name and click OK to create the database.

Create School Database

The school database is created and is empty. So now first create the ‘Teacher’ table.

Click the ► sign given in front of School database name to open the various nodes inside it.

Next, right click on the Tables node and select Add New Table.

A Table Design Window will open up in the Visual Studio. Here you can add the fields for the Teacher table.

In this window, inside the T-SQL tab, you can enter the below script to create the ‘Teacher’ table:

CREATE TABLE [dbo].[Teacher]
(
[Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Name] VARCHAR(50) NOT NULL,
[Skills] VARCHAR(250) NOT NULL,
[TotalStudents] INT NOT NULL,
[Salary] MONEY NOT NULL,
[AddedOn] DATE NOT NULL DEFAULT GETDATE()
)

Next, you click the Update button to start the creation process of this table, see the below image which explains this:

Table Design Window

You will get a new dialog box called ‘Preview Database Updates’. Here click the ‘Update Database’ button and within a few seconds the Inventory table will be created.

Preview Database Updates window

You can find this newly created table inside the ‘Tables’ node.

The ‘Teacher’ table has 6 columns which are:

1. Id — a primary key column that will be auto-generated from 1 since it is provided with Identity(1,1) attribute.
2. Name — a varchar(50) column for storing the name of the teacher.
3. Skills — for storing the skills of the teacher.
4. TotalStudents — an Int column for storing the total number of students which a given teacher teaches.
5. Salary — for storing the salary of the teacher.
6. AddedOn — for storing the time of the creation of the record. The column has a Date type and is it automatically gets the current time using the GETDATE() SQL function.

Note that the columns have NOT NULL attribute so they cannot contain NULL in them.

5. Adding Connection String of the Database in the Application

A Connection String stores the parameters to the database and is used to connect the application to it. These parameters are the name of the driver, Server name and Database name. It also contains security information such as user name and password needed for the database connection.

The connection string is stored inside the appsettings.json file which resides in the root of the application.

Right click on the project name in the Solution Explorer and select Add > New Item. Then when the ‘Add New Item’ window opens up, select ASP.NET Configuration file file, and give it the name as appsettings.json (see below image). Finally click the ‘Add’ button.

Adding appsettings.json file

The file will be created and open up for editing in Visual Studio. Add the below connection string to it:

{
"ConnectionStrings": {
"DefaultConnection": "Server= (localdb)\\MSSQLLocalDB;Database=School;Trusted_Connection=True;MultipleActiveResultSets=true"
}
}

I defined trusted Connection as true in the connection string so the Windows credentials of the current user are used to authenticate against the SQL Server.

6. Adding Model class for Teacher

Right click on the project name in the Solutions Explorer and select ‘Add > New Folder’. Name this folder as ‘Models’. Then right click this ‘Models’ folder and select ‘Add > New Item’.

In the window that opens, select a new Class and name it teachers. Add the following properties to this class:

using CRUDADO.CustomValidation;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
namespace CRUDADO.Models
{
public class Teacher
{
[Required]
public int Id { get; set; }
        [Required]
public string Name { get; set; }
        [Required]
[SkillsValidate(Allowed = new string[] { "ASP.NET Core", "ASP.NET MVC", "ASP.NET Web Forms" }, ErrorMessage = "You skills are invalid")]
public string Skills { get; set; }
        [Range(5, 50)]
public int TotalStudents { get; set; }
        [Required]
public decimal Salary { get; set; }

[Required]
public DateTime AddedOn { get; set; }
}
}

This class has 6 properties for the 6 columns in the Teacher table in the database. I have applied the [Required] attributes on Id, Name, Skills, Salary and AddedOn properties, the TotalStudent property is applied with the [Range] attribute so it will only allow values from 5 to 50.

Also note the [SkillsValidate] attribute applied to the Skills property. The [SkillsValidate] attribute Is a custom validation attribute through which I will force Skills property to only accept 3 values which are:

1. ASP.NET Core
2. ASP.NET MVC
3. ASP.NET Web Forms

Next, I will create the [SkillsValidate] custom validation attribute. So create a new folder on the root of the application and name it as ‘CustomValidation’. Inside this folder add a new class called ‘SkillsValidate.cs’ and add the following code to it:

using Microsoft.AspNetCore.Mvc.ModelBinding.Validation;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace CRUDADO.CustomValidation
{
public class SkillsValidate : Attribute, IModelValidator
{
public string[] Allowed { get; set; }
public string ErrorMessage { get; set; }

public IEnumerable<ModelValidationResult> Validate(ModelValidationContext context)
{
if (Allowed.Contains(context.Model as string))
return Enumerable.Empty<ModelValidationResult>();
else
return new List<ModelValidationResult> {
new ModelValidationResult("", ErrorMessage)
};
}
}
}

7. Adding Client Side Validation feature

The Client Side validation feature is performed by ‘jQuery’ and 2 validation plugins which are:

1. jQuery Validation
2. jQuery Validation Unobtrusive

To install these 3 run the following commands in the ‘Package Manager Console’ window:

PM> Install-Package jQuery -Version 3.3.1
PM> Install-Package jQuery.Validation -Version 1.17.0
PM> Install-Package jQuery.Validation.Unobtrusive -Version 2.0.20710

8. Performing the CREATE Teacher Functionality

Create ‘Controllers’ folder on the root of the Application. Inside this folder add a new controller called ‘HomeController.cs’. The controller will have the initial code as shown below:

using System.Collections.Generic;
using System.Linq;
using Microsoft.AspNetCore.Mvc;
namespace CRUDADO.Controllers
{
public class HomeController : Controller
{
public IActionResult Index()
{
return View();
}
}
}

The connection string is kept in appsettings.json file. To access this file I have to get the object of IConfiguration interface through Dependency Injection feature. Therefore I have to add the IConfiguration interface in the constructor of the HomeController and MVC framework will automatically provide me the object for it.

So change the Home Controller’s code to as shown below:

using System.Collections.Generic;
using System.Linq;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
namespace CRUDADO.Controllers
{
public class HomeController : Controller
{
public IConfiguration Configuration { get; }
public HomeController(IConfiguration configuration)
{
Configuration = configuration;
}

public IActionResult Index()
{
return View();
}
}
}

The CREATE Teacher Functionality is done through a new Action method called ‘CREATE’. So add the Create Action methods to the Home Controller as shown below:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using CRUDADO.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
namespace CRUDADO.Controllers
{
public class HomeController : Controller
{
public IConfiguration Configuration { get; }
public HomeController(IConfiguration configuration)
{
Configuration = configuration;
}
        public IActionResult Index()
{
return View();
}
        public IActionResult Create()
{
return View();
}
        [HttpPost]
public IActionResult Create_Post(Teacher teacher)
{
if (ModelState.IsValid)
{
string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"Insert Into Teacher (Name, Skills, TotalStudents, Salary) Values ('{teacher.Name}', '{teacher.Skills}','{teacher.TotalStudents}','{teacher.Salary}')";
                    using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
return RedirectToAction("Index");
}
}
else
return View();
}
}
}

In the Create Action method I got the connection string in the below code line:

string connectionString = Configuration["ConnectionStrings:DefaultConnection"];

Then I used the ‘SqlCommand’ object to insert the teacher’s records by running the insert SQL Query:

string sql = $"Insert Into Teacher (Name, Skills, TotalStudents, Salary) Values ('{teacher.Name}', '{teacher.Skills}','{teacher.TotalStudents}','{teacher.Salary}')";

Next, add the ‘Views’ folder in the root of the application. Inside this folder create a new folder called ‘Home’.

Next, create a new View called ‘Create.cshtml’ inside this ‘Home’ folder (i.e. ‘Views>Home’). Add the following code to this View:

@model Teacher
@{
Layout = "_Layout";
var title = "CREATE Teacher";
ViewData["Title"] = title;
}
<style>
.input-validation-error {
border-color: red;
}
</style>
<h2>@title</h2>
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<form class="m-1 p-1" method="post">
<div class="form-group">
<label asp-for="Name"></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="Skills"></label>
<input asp-for="Skills" type="text" class="form-control" />
<span asp-validation-for="Skills" class="text-danger"></span>
</div>
    <div class="form-group">
<label asp-for="TotalStudents"></label>
<input asp-for="TotalStudents" type="text" class="form-control" />
<span asp-validation-for="TotalStudents" class="text-danger"></span>
</div>
    <div class="form-group">
<label asp-for="Salary"></label>
<input asp-for="Salary" type="text" class="form-control" />
<span asp-validation-for="Salary" class="text-danger"></span>
</div>
<button type="submit" class="btn btn-primary">Submit</button></form>
<script src="/lib/jquery/dist/jquery.min.js"></script>
<script src="/lib/jquery-validation/dist/jquery.validate.min.js"></script>
<script src="/lib/jquery-validation-unobtrusive/dist/jquery.validate.unobtrusive.min.js"></script>

The View has a Model of type ‘Teacher’ and it creates Input fields for ‘Name, Skills, TotalStudents and Salary’, so that user can fill and submit it.

When the Submit button is clicked then the Create Action of type HttpPost, is called and the new teacher records is created.

Notice the 3 script files which do the Client Side Validation of the input fields in the View:

<script src="/lib/jquery/dist/jquery.min.js"></script>
<script src="/lib/jquery-validation/dist/jquery.validate.min.js"></script>
<script src="/lib/jquery-validation-unobtrusive/dist/jquery.validate.unobtrusive.min.js"></script>

If you are performing the Insertion of records through EF core then you need to see this tutorial —Insert Records in Entity Framework Core.

Testing the Create Teacher functionality

Run the application and in the browser go to the URL of ‘Create’ View, which is — http://localhost:52730/Home/Create. You will see the Create Teacher form in the browser.

Without filling any fields press the Submit button and you will see the validation errors displayed by jQuery Validation plugins.

jQuery Validations on Teacher Form

Now fill all the fields (as shown by the below image) and click the submit button.

Create Teacher

The teacher record will be created in the database and you will be redirected to the Index View which is currently empty.

You can confirm the record is inserted in the Teacher’s table. For this go to the ‘SQL Server Object Explorer’, then right click the ‘Teacher’ table and select ‘View Data’.

The teacher table will open and you will see the new teacher record in it, see the below image:

Teacher Record Inserted in Database

9. Performing the READ Teacher Functionality

Now we will create the READ Teacher Functionality. So change the Index Action in the Home Controller to return all the teachers to the View as shown below:

public IActionResult Index()
{
List<Teacher> teacherList = new List<Teacher>();
string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
    using (SqlConnection connection = new SqlConnection(connectionString))
{
//SqlDataReader
connection.Open();

string sql = "Select * From Teacher";SqlCommand command = new SqlCommand(sql, connection);
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
Teacher teacher = new Teacher();
teacher.Id = Convert.ToInt32(dataReader["Id"]);
teacher.Name = Convert.ToString(dataReader["Name"]);
teacher.Skills = Convert.ToString(dataReader["Skills"]);
                teacher.TotalStudents = Convert.ToInt32(dataReader["TotalStudents"]);
teacher.Salary = Convert.ToDecimal(dataReader["Salary"]);
teacher.AddedOn = Convert.ToDateTime(dataReader["AddedOn"]);
                teacherList.Add(teacher);
}
}
connection.Close();
}
return View(teacherList);
}

In this action I execute the ‘Select * from Teacher’ query with the SqlCommand’s ExecuteReader() method to get an SqlDataReader object.

I then loop through all the records using SqlDataReader’s ‘.Read()’ method, and create a teacher list object which contains all the teacher’s records. This object is returned to the View as the Model in the very end of the code.

Next, add the ‘Index’ View inside the ‘Views/Home/’ folder with the following code:

@model IEnumerable<Teacher>
@{
Layout = "_Layout";
var title = "READ Teacher";
ViewData["Title"] = title;
}
<h2>@title</h2>
<h3><a asp-action="Create" class="btn btn-sm btn-secondary">Create</a></h3>
<table class="table table-bordered table-sm table-striped">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Skills</th>
<th>Total Students</th>
<th>Salary</th>
<th>Added On</th>
<th>Update</th>
<th>Delete</th>
</tr>
</thead>
<tbody>
@if (Model == null)
{
<tr>
<td colspan="7" class="text-center">No Model Data</td>
</tr>
}
else
{
@foreach (var p in Model)
{
<tr>
<td>@p.Id</td>
<td>@p.Name</td>
<td>@p.Skills</td>
<td>@p.TotalStudents</td>
<td>@string.Format(new System.Globalization.CultureInfo("en-US"), "{0:C2}", p.Salary)</td>
<td>@string.Format("{0:dddd, dd MMMM yyyy}", p.AddedOn)</td>
<td><a asp-action="Update" asp-route-id="@p.Id">Update</a></td>
<td>
<form asp-action="Delete" method="post" asp-route-id="@p.Id">
<button>Delete</button>
</form>
</td>
</tr>
}
}
</tbody>
</table>

Notice that I have also created the columns for Update and Delete functionality in the table’s thead and tbody elements:

<table class="table table-bordered table-sm table-striped">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Skills</th>
<th>Total Students</th>
<th>Salary</th>
<th>Added On</th>
<th>Update</th>
<th>Delete</th>
</tr>
</thead>
<tbody>
    else
{
@foreach (var p in Model)
{
<tr>
                <td><a asp-action="Update" asp-route-id="@p.Id">Update</a></td>
<td>
<form asp-action="Delete" method="post" asp-route-id="@p.Id">
<button>Delete</button>
</form>
</td>
</tr>
}
}
</tbody>
</table>

We will create the Update and Delete Functionality in the next sections.

Related tutorial which you can’t miss — Read Records in Entity Framework Core.

Testing the Read Teacher functionality

Run your application and you will see the Teacher’s table records get displayed in the Index View. This is shown by the below image:

Read Teacher View

10. Performing the UPDATE Teacher functionality

The Update functionality is done by adding Update Action methods to the Home Controller. The codes to add to the Controller are given below:

public IActionResult Update(int id)
{
string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
Teacher teacher = new Teacher();
    using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"Select * From Teacher Where Id='{id}'";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
        using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
teacher.Id = Convert.ToInt32(dataReader["Id"]);
teacher.Name = Convert.ToString(dataReader["Name"]);
teacher.Skills = Convert.ToString(dataReader["Skills"]);
                teacher.TotalStudents = Convert.ToInt32(dataReader["TotalStudents"]);
                teacher.Salary = Convert.ToDecimal(dataReader["Salary"]);
                teacher.AddedOn = Convert.ToDateTime(dataReader["AddedOn"]);
}
}
connection.Close();
}
return View(teacher);
}
[HttpPost]
[ActionName("Update")]
public IActionResult Update_Post(Teacher teacher)
{
string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"Update Teacher SET Name='{teacher.Name}', Skills='{teacher.Skills}', TotalStudents='{teacher.TotalStudents}', Salary='{teacher.Salary}' Where Id='{teacher.Id}'";
        using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
return RedirectToAction("Index");
}

The HttpGet version of Update action method takes the ‘id’ of the teacher in it’s parameter and then runs the SQL Query called Select * From Teacher Where Id='{id}' with the SqlCommand object. It gets the teacher record’s value and returns it to the Update View.

The HttpPost version of Update Action method does the actual update of the teacher’s record using ADO.NET ‘SqlCommand’ object. The important thing to note is the update query which is:

string sql = $"Update Teacher SET Name='{teacher.Name}', Skills='{teacher.Skills}', TotalStudents='{teacher.TotalStudents}', Salary='{teacher.Salary}' Where Id='{teacher.Id}'";

Next, create the Update View inside the ‘Views/Home/’ folder with the following code:

@model Teacher
@{
Layout = "_Layout";
var title = "UPDATE Teacher";
ViewData["Title"] = title;
}
<style>
.input-validation-error {
border-color: red;
}
</style>
<h2>@title</h2>
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<form class="m-1 p-1" method="post">
<div class="form-group">
<label asp-for="Id"></label>
<input asp-for="Id" type="text" readonly class="form-control" />
</div
<div class="form-group">
<label asp-for="Name"></label>
<input asp-for="Name" type="text" class="form-control" />
<span asp-validation-for="Name" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Skills"></label>
<input asp-for="Skills" type="text" class="form-control" />
<span asp-validation-for="Skills" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="TotalStudents"></label>
<input asp-for="TotalStudents" type="text" class="form-control" />
<span asp-validation-for="TotalStudents" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Salary"></label>
<input asp-for="Salary" type="text" class="form-control"/>
<span asp-validation-for="Salary" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="AddedOn"></label>
<input asp-for="AddedOn" type="text" class="form-control" asp-format="{0:d}" />
</div>
<button type="submit" class="btn btn-primary">Submit</button>
</form>
<script src="/lib/jquery/dist/jquery.min.js"></script>
<script src="/lib/jquery-validation/dist/jquery.validate.min.js"></script><script src="/lib/jquery-validation-unobtrusive/dist/jquery.validate.unobtrusive.min.js"></script>

The View is similar to the Index View we created earlier. I have made the ‘Id’ field as readonly so that user cannot change it.

Testing the Update Teacher functionality

Run your application and click the ‘Update’ link for the 1st teacher records, see below image:

Click Update Link

The record will open for updation. Change name to ‘Bill Gates’ and salary to ‘100000000’. Finally click the submit button as shown by the below image:

Update Record

Record will be updated and you will be redirected to the Index View where you can see the updated record fields as shown by the below image:

Record Updated

11. Performing the DELETE Teacher Functionality

Create Delete Action method in the Home Controller whose code is given below:

HttpPost]
public IActionResult Delete(int id)
{
string connectionString = Configuration["ConnectionStrings:DefaultConnection"]
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"Delete From Teacher Where Id='{id}'";
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
ViewBag.Result = "Operation got error:" + ex.Message;
}
connection.Close();
}
}
return RedirectToAction("Index");
}

This method takes the id of the teacher’s record in it’s parameter and then execute the Delete SQL query:

string sql = $"Delete From Teacher Where Id='{id}'";

Testing the Delete Teacher functionality

Run your application and click the ‘Delete’ link given against any of the record. Check the below images which shows the record deletion process:

Delete Link
Record Deleted

Download the full Source code of this application from GitHub
________________________________________________________

Conclusion

This completes this CRUD operations tutorial in ASP.NET Core using ADO.NET. I hope you loved reading this and understand how the codes are working. If you have any confusion go through all the code which you can download from here.

Follow ME

I hope you found something new to add to your web development knowledge in this tutorial.

If it is so then don’t forget to clap for a few times to show your like. It will not only bring a smile to my bad face but also motivate me to write more and more good articles to web developers.

And as always, follow me to get notified whenever I publish a new article on Medium.

I have also published another tutorial on HACKERNOON, you would like to see it too — 7 Common Web Development problems which every developer from Beginners to Experts should know [with multiple solutions]

More by Yogi

Topics of interest

More Related Stories