Asp.net core 3.1 crud operation with Ado.net

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:

• Visual studio 2019 Download
• Download SQL Server Management Studio (SSMS)

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..

• Author
• Recent Posts

DuttaluruVijayakumar

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

Latest posts by DuttaluruVijayakumar (see all)

• Asp.net core 3.1 crud operation with Ado.net - June 9, 2020
• how to upload files in asp.net core using web API - May 27, 2020
• Setup Angular 8in visual studio 2019 - May 26, 2020