CRUD Operation using Entity Framework Core and Stored Procedure in .NET Core 6 Web API
Tags: csharp, framework, dotnet, entity
Published: 2022-10-16 07:01:23

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
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/402fhjt7646bzxpn6ber.png)

Step 2
Configure the application
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yw7n1l63bkwrrsae4xhi.png)

Step 3
Provide additional information
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xprljsguk32wu9nobb5i.png)

Step 4
Project Structure
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cl1wkguznhx8ap6bcjg6.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4hrgg2onxa7yn3l34tsu.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sxcq43d6pwn0wdwue6r6.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vjz0stvp4fxj75bpskbv.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0ba0s86mhfxsj2ls7hrv.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nsm8rhy2n2uknxmgknod.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t9a8o0va1epm8x763hdx.png)

## GITHUB URL

https://github.com/Jaydeep-007/EntityFrameworkSP_Demo.git

## Conclusion

Here we discussed implementation of Web API using Entity Framework Core and Stored Procedure

**Happy Learning!**