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 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.
  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 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
    
    You may love to read -

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


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

    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