ASP.Net Core CRUD example using ADO.Net


In this blog, I am going to create a web application in ASP.Net Core to demonstrate CRUD operations using ADO.Net.
Those people who already know about ASP.NET, they will understand this example very well.
Those who are freshers, I would request to read my previous blog so that they don't get confused.

ASP.Net Core Blog

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.

We will follow the below steps to demonstrate the CRUD example in ASP.NET Core.
  1. Create a Database and Adding Tables and Stored Procedures.
  2. Create a new ASP.Net Core Application in Visual Studio 2017 or 2019.
  3. Add Database Connection String in appsettings JSON file.
  4. Add a Model class
  5. Create a Controller Class
  6. Create Action methods in Controller.
  7. Create a View with a List Template
  8. Create a View with a Create Template
  9. Create a View with Update Template
  10. Write code to get a list of record from Database
  11. Write Code to create a record in Database
  12. Write Code to update a record
  13. Write Code to delete a record
Let us see in detail how we can do 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


Add below Stored Procedures

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


2. Creating a .Net Core MVC Web Application
  • 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 structure

wwwroot folder
This 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.

3. 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
(Read my blog - How to manage Database Connection String in .Net Core?)

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


4. 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; }
    }

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

6. Now add Actions

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

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

8. Add Views for Create

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


9. Add Views for Update

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



10. 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<Product> productList = new List<Product>();

            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>


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


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


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

Previous Blog - Database connection string in .Net Core

You may like other blogs -

Interview Questions and Answers Series -

MVC Interview Questions and Answers
Web API interview questions and answers

Comments

Popular