DEV Community

Jaydeep Patil
Jaydeep Patil

Posted on

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

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

Step 2
Configure the application
Image description

Step 3
Provide additional information
Image description

Step 4
Project Structure
Image description

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; }
    }
}
Enter fullscreen mode Exit fullscreen mode

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; }
    }
}
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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}"));
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

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;"
  }
}
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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;
            }
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Step 13
Finally, run the application

Image description

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

Image description

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

Image description

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

Image description

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

Image description

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

Image description

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!

Top comments (0)