In this article, we will learn about how to perform the Asp.net core 3.1 crud operation with ado.net step by step
Prerequisite:
see the following link about Introduction about asp.net Core 2.2
Steps for implementing the Asp.net core crud operation
- Create a Database
- Create an Asp.net core project
- Add model class
- Implementing custom errors
- Add controller
- Establish the connection string
- Implementing the views for creating, update, All lecturers list
Create a Database:
Go to SQL Server Management studio create database named as a SimpleDB
Query
Create database sampleDB
Select your database SampleDB and execute the given below Query
USE [SampleDB]
GO
/****** Object: Table [dbo].[Teacher] Script Date: 6/9/2020 8:17:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Teacher](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Skills] [nvarchar](50) NULL,
[TotalStudents] [nvarchar](50) NULL,
[Salary] [nvarchar](50) NULL,
[AddedOn] [date] NULL,
CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Add some lectures details
Create an ASP.NET Core MVC Project: open Visual Studio, create a new ASP.NET Core project.
Add model class
Right-click on your project Add model class under model folder names as lecturer.cs
create a properties named as “Id” with data type as int, “LecturerName” with data type string, “Skills” with datatype string, “NoofStudents” with datatype int, “Salary” with datatype decimal, “AddedOn” with data type DateTime.
Add Given below code
using CoreCrudOperations.CustomValidation;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
namespace CoreCrudOperations.Models
{
public class lecturer
{
public int Id { get; set; }
[Required]
public string LecturerName { get; set; }
[Required]
[SkillsValidate(Allowed = new string[] { "ASP.NET Core", "ASP.NET MVC", " Web Forms" }, ErrorMessage = "You skills are invalid")]
public string Skills { get; set; }
[Range(5, 50)]
public int NoofStudents { get; set; }
[Required]
public decimal Salary { get; set; }
public DateTime AddedOn { get; set; }
}
}
Implementing custom errors class
Right-click on your project add new folder named as CustomValidation inside add custom validation class named as SkillsValidate as below:
the given below "SkillsValidate" class Attribute it will validate the Skills this [SkillsValidate(Allowed = new string[] { "ASP.NET Core", "ASP.NET MVC", " Web Forms" }, ErrorMessage = "You skills are invalid")]
using Microsoft.AspNetCore.Mvc.ModelBinding.Validation;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace CoreCrudOperations.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)
};
}
}
}
Add controller
Create a new folder named Controllers in your project. In this folder, create a new controller named HomeController.cs as given below:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using CoreCrudOperations.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
namespace CoreCrudOperations.Controllers
{
public class HomeController : Controller
{
public IConfiguration Configuration { get; }
public HomeController(IConfiguration configuration)
{
Configuration = configuration;
}
public IActionResult Index()
{
List<lecturer> teacherList = new List<lecturer>();
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())
{
lecturer teacher = new lecturer();
teacher.Id = Convert.ToInt32(dataReader["Id"]);
teacher.LecturerName = Convert.ToString(dataReader["Name"]);
teacher.Skills = Convert.ToString(dataReader["Skills"]);
teacher.NoofStudents = Convert.ToInt32(dataReader["TotalStudents"]);
teacher.Salary = Convert.ToDecimal(dataReader["Salary"]);
teacher.AddedOn = Convert.ToDateTime(dataReader["AddedOn"]);
teacherList.Add(teacher);
}
}
connection.Close();
}
return View(teacherList);
}
public IActionResult Create()
{
return View();
}
[HttpPost]
public IActionResult Create(lecturer teacher)
{
if (ModelState.IsValid)
{
string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"Insert Into Teacher (Name, Skills, TotalStudents, Salary,AddedOn) Values ('{teacher.LecturerName}', '{teacher.Skills}','{teacher.NoofStudents}','{teacher.Salary}', '{teacher.AddedOn}')";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
return RedirectToAction("Index");
}
}
else
return View();
}
public IActionResult Update(int id)
{
string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
lecturer teacher = new lecturer();
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.LecturerName = Convert.ToString(dataReader["Name"]);
teacher.Skills = Convert.ToString(dataReader["Skills"]);
teacher.NoofStudents = 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(lecturer teacher)
{
string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"Update Teacher SET Name='{teacher.LecturerName}', Skills='{teacher.Skills}', TotalStudents='{teacher.NoofStudents}', Salary='{teacher.Salary}' Where Id='{teacher.Id}'";
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
return RedirectToAction("Index");
}
[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");
}
}
}
In the above Home controller, We will also create an Action method named as an index which will return the List<lecturer>
Establish the connection string:
Go to you appsettings.json add the given below code:
{
"Logging": {
"LogLevel": {
"Default": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"DefaultConnection": "Server=your\\SQLEXPRESS;Database=SampleDB;Trusted_Connection=True;MultipleActiveResultSets=true"
}
}
Implementing the views for Add,update,All lecturers list
Implementing the view for a list of Lectures, go to home controller right-click on index method add a view named as an index.cshtml follow below code:
Index.cshtml
@model IEnumerable<lecturer>
@{
Layout = "_Layout";
var title = "Lecturers List";
ViewData["Title"] = title;
}
<h2>
@title (Asp.net core 3.1 crud operation with Ado.net
)
</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.LecturerName</td>
<td>@p.Skills</td>
<td>@p.NoofStudents</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>
Implementing the view for Adding the details of the lecture named as Create.cshtml as given below:
@model lecturer
@{
Layout = "_Layout";
var title = "Add Lecturer";
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="LecturerName"></label>
<input asp-for="LecturerName" class="form-control" />
<span asp-validation-for="LecturerName" 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="NoofStudents"></label>
<input asp-for="NoofStudents" type="text" class="form-control" />
<span asp-validation-for="NoofStudents" 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>
Implementing the view for update view named as Update.cshtml
@model lecturer
@{ Layout = "_Layout";
var title = "Update Lecturer";
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="LecturerName"></label>
<input asp-for="LecturerName" type="text" class="form-control" />
<span asp-validation-for="LecturerName" 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="NoofStudents"></label>
<input asp-for="NoofStudents" type="text" class="form-control" />
<span asp-validation-for="NoofStudents" 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>
Run your project:
So we have completed Asp.net core 3.1 crud operation with Ado.net
Summary:
In this article, we have seen how to perform CRUD operation in ASP.NET Core using ADO.NET.
We have seen how to give our custom Error method implementation.
Hope you all liked it.
Thanks..
hello ! my name is Duttaluru Vijay.i am working in Microsoft .net technologies since 2016.
I like to share my work experience, research, and knowledge through my blog.
I love developing the applications in Microsoft technologies including asp.net mvc,c#.net, windfarms, SQL server,entityfrawork,Ajax,Jquery,WEbApi,Asp.net core, SEO, WordPress, UI..more
I like to share my work experience, research, and knowledge through my blog.
I love developing the applications in Microsoft technologies including asp.net mvc,c#.net, windfarms, SQL server,entityfrawork,Ajax,Jquery,WEbApi,Asp.net core, SEO, WordPress, UI..more
Latest posts by DuttaluruVijayakumar (see all)
- dtoptions in angular10 datatable - April 29, 2021
- Angular 11 Crud Operation - April 14, 2021
- How to create dynamic form fields using jQuery - January 4, 2021