CRUD Operation using Entity Framework Core and Stored Procedure in .NET Core 6 Web API

#csharp #framework #dotnet #entity

In this article, we are going to discuss the implementation of Web API using entity framework core and stored procedure

Agenda

Implementation of .NET Core 6 Web API
Implementation of Stored Procedures

Prerequisites

.NET Core SDK 6
SQL Server
Visual Studio 2022

Implementation of .NET Core 6 Web API

Step 1
Create a new .NET Core Web API application

Step 2
Configure the application

Step 3
Provide additional information

Step 4
Project Structure

Step 5
Create a Product class inside the Entities folder

using System.ComponentModel.DataAnnotations;

namespace EntityFrameworkSP_Demo.Entities
{
    public class Product
    {
        public int ProductId { get; set; }
        public string ProductName { get; set; }
        public string ProductDescription { get; set; }
        public int ProductPrice { get; set; }
        public int ProductStock { get; set; }
    }
}

Step 6
Next, add a new DbContextClass inside the Data folder

using EntityFrameworkSP_Demo.Entities;
using Microsoft.EntityFrameworkCore;

namespace EntityFrameworkSP_Demo.Data
{
    public class DbContextClass : DbContext
    {
        protected readonly IConfiguration Configuration;

        public DbContextClass(IConfiguration configuration)
        {
            Configuration = configuration;
        }
        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"));
        }

        public DbSet<Product> Product { get; set; }
    }
}

Step 7
Later on, create IProductService and ProductService inside the Repositories folder

IProductService

using EntityFrameworkSP_Demo.Entities;

namespace EntityFrameworkSP_Demo.Repositories
{
    public interface IProductService
    {
        public Task<List<Product>> GetProductListAsync();
        public Task<IEnumerable<Product>> GetProductByIdAsync(int Id);
        public Task<int> AddProductAsync(Product product);
        public Task<int> UpdateProductAsync(Product product);
        public Task<int> DeleteProductAsync(int Id);
    }
}

ProductService

using EntityFrameworkSP_Demo.Data;
using EntityFrameworkSP_Demo.Entities;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

namespace EntityFrameworkSP_Demo.Repositories
{
    public class ProductService : IProductService
    {
        private readonly DbContextClass _dbContext;

        public ProductService(DbContextClass dbContext)
        {
            _dbContext = dbContext;
        }

        public async Task<List<Product>> GetProductListAsync()
        {
            return await _dbContext.Product
                .FromSqlRaw<Product>("GetPrductList")
                .ToListAsync();
        }

        public async Task<IEnumerable<Product>> GetProductByIdAsync(int ProductId)
        {
            var param = new SqlParameter("@ProductId", ProductId);

            var productDetails = await Task.Run(() => _dbContext.Product
                            .FromSqlRaw(@"exec GetPrductByID @ProductId", param).ToListAsync());

            return productDetails;
        }

        public async Task<int> AddProductAsync(Product product)
        {
            var parameter = new List<SqlParameter>();
            parameter.Add(new SqlParameter("@ProductName", product.ProductName));
            parameter.Add(new SqlParameter("@ProductDescription", product.ProductDescription));
            parameter.Add(new SqlParameter("@ProductPrice", product.ProductPrice));
            parameter.Add(new SqlParameter("@ProductStock", product.ProductStock));

            var result = await Task.Run(() =>  _dbContext.Database
           .ExecuteSqlRawAsync(@"exec AddNewProduct @ProductName, @ProductDescription, @ProductPrice, @ProductStock", parameter.ToArray()));

            return result;
        }

        public async Task<int> UpdateProductAsync(Product product)
        {
            var parameter = new List<SqlParameter>();
            parameter.Add(new SqlParameter("@ProductId", product.ProductId));
            parameter.Add(new SqlParameter("@ProductName", product.ProductName));
            parameter.Add(new SqlParameter("@ProductDescription", product.ProductDescription));
            parameter.Add(new SqlParameter("@ProductPrice", product.ProductPrice));
            parameter.Add(new SqlParameter("@ProductStock", product.ProductStock));

            var result = await Task.Run(() => _dbContext.Database
            .ExecuteSqlRawAsync(@"exec UpdateProduct @ProductId, @ProductName, @ProductDescription, @ProductPrice, @ProductStock", parameter.ToArray()));
            return result;
        }
        public async Task<int> DeleteProductAsync(int ProductId)
        {
            return await Task.Run(() => _dbContext.Database.ExecuteSqlInterpolatedAsync($"DeletePrductByID {ProductId}"));
        }
    }
}

FromSqlRaw method is used to execute SQL commands against the database and returns the instance of DbSet
ExecuteSqlRawAsync is used to execute the SQL commands and returns the number of rows affected
ExecuteSqlInterpolatedAsync executes SQL command and returns the number of affected rows

Step 8
Add database connection string inside the appsettings.json file

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=DESKTOP;Initial Catalog=StoredProcedureEFDemo;User Id=sa;Password=database;"
  }
}

Step 9
Register services inside the Program class

using EntityFrameworkSP_Demo.Data;
using EntityFrameworkSP_Demo.Repositories;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddScoped<IProductService, ProductService>();
builder.Services.AddDbContext<DbContextClass>();

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseAuthorization();

app.MapControllers();

app.Run();

Step 10

Next, create a new Product controller

using EntityFrameworkSP_Demo.Entities;
using EntityFrameworkSP_Demo.Repositories;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;

namespace EntityFrameworkSP_Demo.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductsController : ControllerBase
    {
        private readonly IProductService productService;

        public ProductsController(IProductService productService)
        {
            this.productService = productService;
        }

        [HttpGet("getproductlist")]
        public async Task<List<Product>> GetProductListAsync()
        {
            try
            {
                return await productService.GetProductListAsync();
            }
            catch
            {
                throw;
            }
        }

        [HttpGet("getproductbyid")]
        public async Task<IEnumerable<Product>> GetProductByIdAsync(int Id)
        {
            try
            {
                var response = await productService.GetProductByIdAsync(Id);

                if(response == null)
                {
                    return null;
                }

                return response;
            }
            catch
            {
                throw;
            }
        }

        [HttpPost("addproduct")]
        public async Task<IActionResult> AddProductAsync(Product product)
        {
            if(product == null)
            {
                return BadRequest();
            }

            try
            {
                var response = await productService.AddProductAsync(product);

                return Ok(response);
            }
            catch
            {
                throw;
            }
        }

        [HttpPut("updateproduct")]
        public async Task<IActionResult> UpdateProductAsync(Product product)
        {
            if (product == null)
            {
                return BadRequest();
            }

            try
            {
                var result =  await productService.UpdateProductAsync(product);
                return Ok(result);
            }
            catch
            {
                throw;
            }
        }

        [HttpDelete("deleteproduct")]
        public async Task<int> DeleteProductAsync(int Id)
        {
            try
            {
                var response = await productService.DeleteProductAsync(Id);
                return response;
            }
            catch
            {
                throw;
            }
        }
    }
}

Step 11
Execute the following command to create migration and update the database in the package manager console

add-migration "Initial"
update-databse

Step 12
Implementation of Stored Procedures

GetPrductList

USE [StoredProcedureEFDemo]
GO

/****** Object:  StoredProcedure [dbo].[GetPrductList]    Script Date: 10/16/2022 11:08:29 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[GetPrductList]
AS
BEGIN
    SELECT * FROM dbo.Product
END
GO

GetPrductByID

USE [StoredProcedureEFDemo]
GO

/****** Object:  StoredProcedure [dbo].[GetPrductByID]    Script Date: 10/16/2022 11:09:04 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[GetPrductByID]
@ProductId int
AS
BEGIN
    SELECT
        ProductId,
        ProductName,
        ProductDescription,
        ProductPrice,
        ProductStock
    FROM dbo.Product where ProductId = @ProductId
END
GO

AddNewProduct

USE [StoredProcedureEFDemo]
GO

/****** Object:  StoredProcedure [dbo].[AddNewProduct]    Script Date: 10/16/2022 11:09:20 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER PROCEDURE [dbo].[AddNewProduct]
@ProductName [nvarchar](max),
@ProductDescription [nvarchar](max),
@ProductPrice int,
@ProductStock int
AS
BEGIN
    INSERT INTO dbo.Product
        (
            ProductName,
            ProductDescription,
            ProductPrice,
            ProductStock
        )
    VALUES
        (
            @ProductName,
            @ProductDescription,
            @ProductPrice,
            @ProductStock
        )
END
GO

UpdateProduct

USE [StoredProcedureEFDemo]
GO

/****** Object:  StoredProcedure [dbo].[UpdateProduct]    Script Date: 10/16/2022 11:09:38 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER PROCEDURE [dbo].[UpdateProduct]
@ProductId int,
@ProductName [nvarchar](max),
@ProductDescription [nvarchar](max),
@ProductPrice int,
@ProductStock int
AS
BEGIN
    UPDATE dbo.Product
    SET
        ProductName = @ProductName,
        ProductDescription = @ProductDescription,
        ProductPrice = @ProductPrice,
        ProductStock = @ProductStock
    WHERE ProductId = @ProductId
END
GO

DeletePrductByID

USE [StoredProcedureEFDemo]
GO

/****** Object:  StoredProcedure [dbo].[DeletePrductByID]    Script Date: 10/16/2022 11:09:50 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[DeletePrductByID]
@ProductId int
AS
BEGIN
    DELETE FROM dbo.Product where ProductId = @ProductId
END
GO

Step 13
Finally, run the application