CRUD Operation Using Entity Framework Core And Stored Procedure In .NET Core 6 Web API

Introduction

In this article, we are going to discuss the implementation of Web API using entity framework core and Stored Procedure in SQL Server.

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

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

Step 2

Configure the application

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

Step 3

Provide additional information

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

Step 4

Project Structure

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

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

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

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

http://localhost:5002/api/Products/getproductlist

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

http://localhost:5002/api/Products/getproductbyid?Id=16

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

http://localhost:5002/api/Products/addproduct

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

http://localhost:5002/api/Products/updateproduct

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

http://localhost:5002/api/Products/deleteproduct?Id=19

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

Conclusion

Here we discussed the implementation of Web API using Entity Framework Core and Stored Procedure in SQL Server. Happy Learning!

5 Comments

Join the conversation! Your thoughts help the community grow.

jose lora3y ago
EXCELENT WORK
sivanathan Balasubramanian3y ago
What do I do when we have a foreign key situation.Like Product and Order.Kindly guide me
- Jaydeep Patil3y ago
  In that case you need to configure both models using key and relationships. Please the check the link that will be helpful for you. https://www.entityframeworktutorial.net/efcore/one-to-many-conventions-entity-framework-core.aspx
- sivanathan Balasubramanian3y ago
  Thanks for the prompt response.Will this be ideal when multiple person are working in Team.When each member pushes to git,What will happen to add-migration.Each will be having their own version of migration.Will this be ideal,since the very purpose of MVC(segregation) is not there
Nguyen Cuong3y ago
Hey Guy, you can make topic about Unit Of Work With Generic Repository Implementation Using Stored Procedure In .NET Core 6 Web API. Tks u