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 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
http://localhost:5002/api/Products/getproductlist
http://localhost:5002/api/Products/getproductbyid?Id=16
http://localhost:5002/api/Products/addproduct
http://localhost:5002/api/Products/updateproduct
http://localhost:5002/api/Products/deleteproduct?Id=19
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)