Creating a High-Performance ASP.Net Core CRUD App with ADO.Net

In this blog, I am going to create a web application in ASP.Net Core to demonstrate CRUD operations using ADO.Net.

Table of Contents

Those people who already know about ASP.NET, they will understand this example very well.
Those who are freshers, I would request you to read my previous blog so that they don’t get confused.

To demonstrate this blog, I have created a .NET Core MVC web application in Visual Studio 2017.

You may read this blog – Razor Pages vs MVC in ASP.Net Core

To understand any programming language very well, you need to practice a CRUD (Create, Read, Update, Delete) example. To understand the ASP.NET core CRUD example, we will follow some steps.

ASP.Net Core CRUD example using ADO.Net

We will follow the below steps to demonstrate the CRUD example in ASP.NET Core.

Create a Database and Adding Tables and Stored Procedures.
Create a new ASP.Net Core Application in Visual Studio 2017 or 2019.
Add Database Connection String in appsettings JSON file.
Add a Model class
Create a Controller Class
Create Action methods in Controller.
Create a View with a List Template
Create a View with a Create Template
Create a View with Update Template
Write code to get a list of record from Database
Write Code to create a record in Database
Write Code to update a record
Write Code to delete a record

Let us see in detail how we can create CRUD operations in ASP.NET Core using ADO.Net.

Prerequisites

Visual Studio 2017 or Visual Studio 2019 with the ASP.NET and web development workload
.NET Core SDK 2.2 or later

1. Database Tasks

Our very first step will be creating Database. Create a Database with your desired name. Create a new table using the below script.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product_Master](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [product_name] [varchar](250) NULL,
       [product_desc] [varchar](250) NULL,
       [cost] [decimal](18, 0) NULL,
       [stock] [int] NULL,
 CONSTRAINT [PK_Product_Master] PRIMARY KEY CLUSTERED
(
       [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Write below stored procedure

--Add new Product
CREATE PROCEDURE [dbo].[SP_Add_New_Product]
       -- Add the parameters for the stored procedure here
       @ProductName nvarchar (200),
       @ProductDescription nvarchar (200),
       @ProductCost decimal (18,2),
       @Stock int
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
    -- Insert statements for procedure here
       insert into Product_Master (product_name, product_desc, cost, stock) values (@ProductName,@ProductDescription,
       @ProductCost, @Stock)
END
--Get Product by ID
CREATE PROCEDURE [dbo].[SP_Get_Product_By_Id]
       -- Add the parameters for the stored procedure here
@id int
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
    -- Insert statements for procedure here
select id as Id, product_name as ProductName, product_desc as ProductDescription, cost as ProductCost,
       stock as Stock from Product_Master where id = @id
END
Get all Product
CREATE PROCEDURE [dbo].[SP_Get_ProductList]
       -- Add the parameters for the stored procedure here
        
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
    -- Insert statements for procedure here
       select id as Id, product_name as ProductName, product_desc as ProductDescription, cost as ProductCost,
       stock as Stock from Product_Master
END
--Delete Product by Id
CREATE PROCEDURE [dbo].[SP_Delete_Product_By_Id]
       -- Add the parameters for the stored procedure here
@id int
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
    -- Insert statements for procedure here
Delete from Product_Master where id = @id
END

--Update a Product
CREATE PROCEDURE [dbo].[SP_Update_Product]
       -- Add the parameters for the stored procedure here
@id int,
@ProductName nvarchar (100),
@ProductDescription nvarchar(100),
@ProductCost decimal (18,2),
@Stock int
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
    -- Insert statements for procedure here
update Product_Master set product_name = @ProductName, product_desc = @ProductDescription,
cost = @ProductCost, stock = @Stock where id = @id
END

Create a .Net Core MVC Web Application in Visual Studio 2017 or 2019

Open Visual Studio 2017 or Visual Studio 2019
Create a New Project
Select ASP.Net Core Web Application

Select Web Application (Model-View-Controller) (As we are creating .Net Core MVC web Application)

Click OK.

Your .Net Core MVC Project is now ready for development.Let’s understand ASP.Net Core Project structurewwwroot folderThis folder contains static files such as CSS files and JavaScript files.

Program.cs
This file contains the entry point for the program.

appSettings.json
This file contains configuration data, such as connection strings.

Add a Database Connection String

In the ASP.Net framework, we had a web.config file to manage all the configurable things including the database connection string.
But in .Net core, we don’t have web.config. In ASP.Net Core we have JSON based file to manage database connection string. Because application developed in ASP.Net Core is platform-dependent and JSON is readable to all platforms.

Add below line in your appsettings.json file

"ConnectionStrings": {
    "DefaultConnection": "Data Source=127.0.0.1;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=sa;Password=12345"
  }

(Read my blog – How to manage Database Connection String in .Net Core?)

Add a Model Class

To add a model class, first create a new folder in the project. I kept the folder name Models.
Right-click on the Models folder and add a new class.

I added a class Product with below properties.

public class Product
    {
        public int Id { get; set; }
        public string ProductName { get; set; }
        public string ProductDescription { get; set; }
        public decimal ProductCost { get; set; }
        public int Stock { get; set; }
    }

Add a Controller

To add a controller, right-click on Controllers folder in your project and Add->Controller…
Select MVC Controller – Empty
Give a name to your controller and click

Now add Actions

Add Action methods for all your operations like Product, ProductCreate, ProductUpdate, ProductDelete

Add Views for List

To add a View, right-click on Action method and Add a View for List as shown in the below screen.

Add Views for Create

Right-click on Action method and Add a View for Create.

Add Views for Update

Right-click on Action method and Add a View for Edit/Update

Code to Get a list of record from Database

Create a Product Action method to Get a list of records from Database.
Below is the code for the Product action method.

public IActionResult Product()
        {
            List productList = new List();
            var dbconfig = new ConfigurationBuilder()
                 .SetBasePath(Directory.GetCurrentDirectory())
                 .AddJsonFile("appsettings.json").Build();
            try
            {
                dbconnectionStr = dbconfig["ConnectionStrings:DefaultConnection"];
                string sql = "SP_Get_ProductList";
                using (SqlConnection connection = new SqlConnection(dbconnectionStr))
                {
                    SqlCommand command = new SqlCommand(sql, connection);
                    connection.Open();
                    using (SqlDataReader dataReader = command.ExecuteReader())
                    {
                        while (dataReader.Read())
                        {
                            Product product = new Product();
                            product.Id = Convert.ToInt32(dataReader["Id"]);
                            product.ProductName = Convert.ToString(dataReader["ProductName"]);
                            product.ProductDescription = Convert.ToString(dataReader["ProductDescription"]);
                            product.ProductCost = Convert.ToDecimal(dataReader["ProductCost"]);
                            product.Stock = Convert.ToInt32(dataReader["Stock"]);
                            productList.Add(product);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return View(productList);
        }

Below is the code of Product View

@model IEnumerable<Product>
@{
    ViewData["Title"] = "Product";
}
<h2>Product</h2>
<h4>Product</h4>
<hr />
<div>
    <a asp-action="ProductCreate" class="btn btn-primary">Add New Product</a>
</div>
<div class="row">
    <table class="table table-bordered table-striped">
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Description</th>
                <th>Cost Students</th>
                <th>Stock</th>
                <th>Update</th>
                <th>Delete</th>
            </tr>
        </thead>
        <tbody>
            @if (Model == null)
            {
                <tr>
                    <td colspan="7" class="text-center">No Data found</td>
                </tr>
            }
            else
            {
                @foreach (var p in Model)
                {
                    <tr>
                        <td>@p.Id</td>
                        <td>@p.ProductName</td>
                        <td>@p.ProductDescription</td>
                        <td>@p.ProductCost</td>
                        <td>@p.Stock</td>
                        <td><a asp-action="ProductUpdate" asp-route-id="@p.Id">Update</a></td>
                        <td>
                            <form asp-action="ProductDelete" method="post" asp-route-id="@p.Id">
                                <button>Delete</button>
                            </form>
                        </td>
                    </tr>
                }
            }
        </tbody>
    </table>
</div>

Code to create a new record

Get and Post Action methods to create records.
The action method name is ProductCreate()

public IActionResult ProductCreate()
        {
            return View();
        }
        [HttpPost]
        public IActionResult ProductCreate(Product product)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    var dbconfig = new ConfigurationBuilder()
                   .SetBasePath(Directory.GetCurrentDirectory())
                   .AddJsonFile("appsettings.json").Build();
                    if (!string.IsNullOrEmpty(dbconfig.ToString()))
                    {
                        dbconnectionStr = dbconfig["ConnectionStrings:DefaultConnection"];
                        using (SqlConnection connection = new SqlConnection(dbconnectionStr))
                        {
                            string sql = "SP_Add_New_Product";
                            using (SqlCommand cmd = new SqlCommand(sql, connection))
                            {
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@ProductName", product.ProductName);
                                cmd.Parameters.AddWithValue("@ProductDescription", product.ProductDescription);
                                cmd.Parameters.AddWithValue("@ProductCost", product.ProductCost);
                                cmd.Parameters.AddWithValue("@Stock", product.Stock);
                                connection.Open();
                                cmd.ExecuteNonQuery();
                                connection.Close();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return RedirectToAction("Product");
        }

Below is the View code of ProductCreate

@model DotNetCoreApp.Models.Product
@{
    ViewData["Title"] = "ProductCreate";
}
<h2>ProductCreate</h2>
<h4>Product</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form asp-action="ProductCreate">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <div class="form-group">
                <label asp-for="ProductName" class="control-label"></label>
                <input asp-for="ProductName" class="form-control" />
                <span asp-validation-for="ProductName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ProductDescription" class="control-label"></label>
                <input asp-for="ProductDescription" class="form-control" />
                <span asp-validation-for="ProductDescription" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ProductCost" class="control-label"></label>
                <input asp-for="ProductCost" class="form-control" />
                <span asp-validation-for="ProductCost" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Stock" class="control-label"></label>
                <input asp-for="Stock" class="form-control" />
                <span asp-validation-for="Stock" 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="Product">Back to List</a>
</div>

Code to update a record

Get and Post Action method for ProductUpdate-

public IActionResult ProductUpdate(int id)
        {
            var dbconfig = new ConfigurationBuilder()
                  .SetBasePath(Directory.GetCurrentDirectory())
                  .AddJsonFile("appsettings.json").Build();
            dbconnectionStr = dbconfig["ConnectionStrings:DefaultConnection"];
            Product product = new Product();
            using (SqlConnection connection = new SqlConnection(dbconnectionStr))
            {
                string sql = "SP_Get_Product_By_Id";
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@id", id);
                    connection.Open();
                    using (SqlDataReader dataReader = cmd.ExecuteReader())
                    {
                        while (dataReader.Read())
                        {
                            product.Id = Convert.ToInt32(dataReader["Id"]);
                            product.ProductName = Convert.ToString(dataReader["ProductName"]);
                            product.ProductDescription = Convert.ToString(dataReader["ProductDescription"]);
                            product.ProductCost = Convert.ToDecimal(dataReader["ProductCost"]);
                            product.Stock = Convert.ToInt32(dataReader["Stock"]);
                        }
                    }
                }
                connection.Close();
            }
            return View(product);
        }
        [HttpPost]
        public IActionResult ProductUpdate(Product product)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    var dbconfig = new ConfigurationBuilder()
                   .SetBasePath(Directory.GetCurrentDirectory())
                   .AddJsonFile("appsettings.json").Build();
                    if (!string.IsNullOrEmpty(dbconfig.ToString()))
                    {
                        dbconnectionStr = dbconfig["ConnectionStrings:DefaultConnection"];
                        using (SqlConnection connection = new SqlConnection(dbconnectionStr))
                        {
                            string sql = "SP_Update_Product";
                            using (SqlCommand cmd = new SqlCommand(sql, connection))
                            {
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@id", product.Id);
                                cmd.Parameters.AddWithValue("@ProductName", product.ProductName);
                                cmd.Parameters.AddWithValue("@ProductDescription", product.ProductDescription);
                                cmd.Parameters.AddWithValue("@ProductCost", product.ProductCost);
                                cmd.Parameters.AddWithValue("@Stock", product.Stock);
                                connection.Open();
                                cmd.ExecuteNonQuery();
                                connection.Close();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return RedirectToAction("Product");
        }

Code of ProductUpdate View-

@model DotNetCoreApp.Models.Product
@{
    ViewData["Title"] = "ProductUpdate";
}
<h2>ProductUpdate</h2>
<h4>Product</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form asp-action="ProductUpdate">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <div class="form-group">
                <label asp-for="Id" class="control-label"></label>
                <input asp-for="Id" class="form-control" />
                <span asp-validation-for="Id" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ProductName" class="control-label"></label>
                <input asp-for="ProductName" class="form-control" />
                <span asp-validation-for="ProductName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ProductDescription" class="control-label"></label>
                <input asp-for="ProductDescription" class="form-control" />
                <span asp-validation-for="ProductDescription" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ProductCost" class="control-label"></label>
                <input asp-for="ProductCost" class="form-control" />
                <span asp-validation-for="ProductCost" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Stock" class="control-label"></label>
                <input asp-for="Stock" class="form-control" />
                <span asp-validation-for="Stock" 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>

Code to Delete a record

ProductDelete action method code –
For Delete operation we don’t have Get Action. After deletion I am not returning the View instead, I am redirecting it.

[HttpPost]
        public IActionResult ProductDelete(int id)
        {
            var dbconfig = new ConfigurationBuilder()
                  .SetBasePath(Directory.GetCurrentDirectory())
                  .AddJsonFile("appsettings.json").Build();
            dbconnectionStr = dbconfig["ConnectionStrings:DefaultConnection"];
            using (SqlConnection connection = new SqlConnection(dbconnectionStr))
            {
                string sql = "SP_Delete_Product_By_Id";
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@id", id);
                    connection.Open();
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        
                    }
                    connection.Close();
                }
            }
            return RedirectToAction("Product");
        }

Output –
Below is the output screen

To design the pages, I have used bootstrap for this demonstration. I have used ADO.Net to interact with MS-SQL Database, you may try with entity framework as well.

I hope this might be interesting and useful to you. If you have practiced .Net Core CRUD example well then it will be easy for you to learn .Net Core further.

You may like other blogs –

Interview Questions and Answers Series –

2 thoughts on “Creating a High-Performance ASP.Net Core CRUD App with ADO.Net”

Rajesh
April 22, 2021 at 4:53 pm
Hello
Is it possible for you to update the example with cascading dropdown list boxes, please? Or minimum give an idea about what is the best approach for populating dropdown lists for CREATE action?
I am stuck with java script & document.ready to load the default dropdown list and more java scripts to handle the list change actions.
Quiz Dev
May 29, 2021 at 10:43 am
Very basic CRUD, it so good for a beginner like me!!! Thank many.