CRUD Operation Using Entity Framework Core and Stored Procedure in .Net Core 6 Web API

A step-by-step tutorial on the CRUD operation implementation of Web API using entity framework core and stored procedure with guide pictures and code.

Jaydeep Patil

Oct. 27, 22 · Tutorial

Likes (2)

Comment

Save

5.4K Views

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

Prerequisites

.NET Core SDK 6
SQL Server
Visual Studio 2022

Agenda

Implementation of .NET Core 6 Web API
Implementation of stored procedures

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.

     C# 
   
   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

Add a new DbContextClass inside the "Data" folder.

     C# 
   
   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

Create IProductService and ProductService inside the "Repositories" folder.

IProductService:

     C# 
   
   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:

     C# 
   
   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}"));

        }

    }

}

FromSqlRawmethod is used to execute SQL commands against the database and returns the instance of DbSet.

ExecuteSqlRawAsyncis used to execute the SQL commands and returns the number of rows affected.

ExecuteSqlInterpolatedAsyncexecutes SQL command and returns the number of affected rows.

Step 8

Add database connection string inside the appsettings.json file.

     JSON 
   
   {

  "Logging": {

    "LogLevel": {

      "Default": "Information",

      "Microsoft.AspNetCore": "Warning"

    }

  },

  "AllowedHosts": "*",

  "ConnectionStrings": {

    "DefaultConnection": "Data Source=DESKTOP;Initial Catalog=StoredProcedureEFDemo;User Id=sa;Password=database;"

  }

}

Step 9

     C# 
   
   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

Create a new product controller.

     C# 
   
   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-database

Step 12

Implementation of stored procedures

GetPrductList:

     SQL 
   
   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:

     SQL 
   
   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:

     SQL 
   
   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:

     SQL 
   
 
 
   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:

     SQL 
   
   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