Asp.net core 3.1 crud operation with Ado.net

by DuttaluruVijayakumar Updated : Jun 09, 2020 in Asp.net core

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:

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

Asp.net core 3.1 crud operation with ado.net

Create an ASP.NET Core MVC Project: open Visual Studio, create a new ASP.NET Core project.

Create an ASP.NET Core MVC 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>
Asp.net core 3.1 crud operation with ado.net

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>
Custom Validations in Asp.net core

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

Run your project: 

Asp.net core crud operation with Ado.net
Asp.net core 3.1 crud operation with Ado.net

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

Latest posts by DuttaluruVijayakumar (see all)
Like
Like Love Haha Wow Sad Angry

Related Posts:

Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Amit Dutta
Amit Dutta
August 6, 2021 12:28 pm

I have come across a free online tool which creates crud operations automatically at just a button click. Link is given below
https://codegenerator.equitechsoftwares.com

Last edited 9 months ago by Amit Dutta
1
Reply
DuttaluruVijayakumar
Author
DuttaluruVijayakumar(@duttaluruvijayakumar)
December 3, 2021 6:56 am
Reply to  Amit Dutta

thanks for sharing URL

0
Reply
2
0
Would love your thoughts, please comment.x
()
x