Introduction
Sometimes we need to create APIs and perform custom operation with database where we can perform create, insert, update and delete on entity/ties or table/s according to our requirement.
Then we can use Store Procedure.
DbContext will provide us Connection String and Connection String will provide Database Connection.
And using SqlCommand we can execute the Stored Procedure which will give us SqlDataReader in which we will get result/response/output of Stored Procedure.
Using DbContext we can get Database Connection and then Connection String to perform needful operations using stored procedure.
We will see the complete tutorial with steps to know how to use Stored Procedure in respository by using DbContext.
Here, we will see single READ/RETRIEVE/GET operation using stored procedure.
By following the below steps you will come to know how to use stored procedure.
1. Create Table
Consider the Entity/Table fields as follows
Need to create table.
CREATE TABLE [dbo].[Students](
[RollNo] [int] IDENTITY(1,1) NOT NULL,
[CreatedBy] [nvarchar](max) NULL,
[CreatedDate] [datetime2](7) NOT NULL,
[LastModifiedBy] [nvarchar](max) NULL,
[LastModifiedDate] [datetime2](7) NULL,
[FirstName] [nvarchar](max) NULL,
[MiddleName] [nvarchar](max) NULL,
[LastName] [nvarchar](max) NULL,
[First_LastName] [nvarchar](max) NULL,
[FullName] [nvarchar](max) NULL,
[Address] [nvarchar](max) NULL,
[Mobile] [nvarchar](max) NULL,
[Email] [nvarchar](max) NULL,
[Standard] [nvarchar](max) NULL,
[MediumType] [int] NOT NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
[RollNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
2. Stored Procedure
We can write Stored Procedure as per the requirement.
a. Stored Procedure Code
CREATE PROCEDURE [dbo].[GetAllStudentsByMediumType]
@mediumType int=0
AS
BEGIN
SELECT *
FROM Students student
WHERE student.MediumType = @mediumType
END
b. Stored Procedure Execution
Command used to execute the stored procedure.
EXEC [dbo].[GetAllStudentsByMediumType] @mediumType=1;
![Stored Procedure and Its Execution]()
3. Contract | Interface
This is method declaration inside contract/interface which will get implemented in the interface IStudentRepository.
using ILogicWebAPI.Domain.Entities;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace ILogicWebAPI.Application.Contracts.Persistence
{
public interface IStudentRepository : IAsyncRepository<Student>
{
Task<List<Student>> GetStudentsByMediumTypeAsync(int MediumType);
}
}
4. Repository
StudentRepository where we will implement the method declared inside contract/interface
using ILogicWebAPI.Application.Contracts.Persistence;
using ILogicWebAPI.Domain.Entities;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
namespace ILogicWebAPI.Persistence.Repositories
{
public class StudentRepository : BaseRepository<Student>, IStudentRepository
{
private readonly ILogger _logger;
public StudentRepository(ILogicApplicationDbContext dbContext, ILogger<Student> logger) : base(dbContext, logger)
{
_logger = logger;
}
public async Task<List<Student>> GetStudentsByMediumTypeAsync(int MediumType)
{
List<Student> lstStudents = new List<Student>();
var conString = _dbContext.Database.GetDbConnection();
using (SqlConnection con = new SqlConnection(conString.ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetAllStudentsByMediumType", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@mediumType", MediumType);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Student student = new Student();
student.RollNo = Convert.ToInt32(rdr["RollNo"]);
student.FirstName = rdr["FirstName"].ToString();
student.MiddleName = rdr["MiddleName"].ToString();
student.LastName = rdr["LastName"].ToString();
student.First_LastName = rdr["First_LastName"].ToString();
student.FullName = rdr["FullName"].ToString();
student.Address = rdr["Address"].ToString();
student.Mobile = rdr["Mobile"].ToString();
student.Email = rdr["Email"].ToString();
student.Standard = rdr["Standard"].ToString();
student.MediumType = Convert.ToInt32(rdr["MediumType"]);
student.CreatedBy = rdr["CreatedBy"].ToString();
student.CreatedDate = Convert.ToDateTime(rdr["CreatedDate"].ToString());
student.LastModifiedBy = rdr["LastModifiedBy"].ToString();
student.LastModifiedDate = Convert.ToDateTime(rdr["LastModifiedDate"].ToString());
lstStudents.Add(student);
}
con.Close();
}
return lstStudents;
}
}
}
5. Query, ViewModel and Handler
a. Query- GetStudentListByMediumTypeQuery
This is query class which will get used while call the Handler using MediumType request parameter.
using MediatR;
using System.Collections.Generic;
using System;
using ILogicWebAPI.Application.Responses;
namespace ILogicWebAPI.Application.Features.Students.Queries.GetStudentListByMediumType
{
public class GetStudentListByMediumTypeQuery : IRequest<Response<IEnumerable<GetStudentListByMediumTypeVm>>>
{
public int MediumType { get; set; }
}
}
b. ViewModel- GetStudentListByMediumTypeVm
Below is view model, the result of stored procedure will have these fields with its values.
using System;
using System.Collections.Generic;
using System.Text;
namespace ILogicWebAPI.Application.Features.Students.Queries.GetStudentListByMediumType
{
public class GetStudentListByMediumTypeVm
{
public int RollNo { get; set; }
public string FirstName { get; set; }
public string MiddleName { get; set; }
public string LastName { get; set; }
public string First_LastName { get; set; }
public string FullName { get; set; }
public string Address { get; set; }
public string Mobile { get; set; }
public string Email { get; set; }
public string Standard { get; set; }
public int MediumType { get; set; }
public string CreatedBy { get; set; }
public DateTime CreatedDate { get; set; }
public string LastModifiedBy { get; set; }
public DateTime? LastModifiedDate { get; set; }
}
}
c. Handler- GetStudentListByMediumTypeQueryHandler
In handler we will call the method which we have implemented in respository.
using AutoMapper;
using ILogicWebAPI.Application.Contracts.Persistence;
using ILogicWebAPI.Application.Responses;
using MediatR;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
namespace ILogicWebAPI.Application.Features.Students.Queries.GetStudentListByMediumType
{
class GetStudentListByMediumTypeQueryHandler : IRequestHandler<GetStudentListByMediumTypeQuery, Response<IEnumerable<GetStudentListByMediumTypeVm>>>
{
private readonly IStudentRepository _studentRepository;
private readonly IMapper _mapper;
private readonly ILogger _logger;
public GetStudentListByMediumTypeQueryHandler(IMapper mapper, IStudentRepository studentRepository, ILogger<GetStudentListByMediumTypeQueryHandler> logger)
{
_mapper = mapper;
_studentRepository = studentRepository;
_logger = logger;
}
public async Task<Response<IEnumerable<GetStudentListByMediumTypeVm>>> Handle(GetStudentListByMediumTypeQuery request, CancellationToken cancellationToken)
{
_logger.LogInformation("Handle Initiated");
var allStudents = (await _studentRepository.GetStudentsByMediumTypeAsync(request.MediumType));
var students = _mapper.Map<IEnumerable<GetStudentListByMediumTypeVm>>(allStudents);
_logger.LogInformation("Handle Completed");
if (students == null)
{
var resposeObject = new Response<IEnumerable<GetStudentListByMediumTypeVm>>("No record is not available");
return resposeObject;
}
return new Response<IEnumerable<GetStudentListByMediumTypeVm>>(students, "success");
}
}
}
6. Controller
Through this StudentController will call the respective handler by passing parameter to Send() method.
using ILogicWebAPI.Application.Features.Students.Queries.GetStudentListByMediumType;
using MediatR;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace ILogicWebAPI.Api.Controllers.v1
{
[Route("api/[controller]")]
[ApiController]
public class StudentController : ControllerBase
{
private readonly IMediator _mediator;
private readonly ILogger _logger;
public StudentController(IMediator mediator, ILogger<StudentController> logger)
{
_mediator = mediator;
_logger = logger;
}
//GetAllStudentsByMediumType
[HttpGet("GetAllStudentsByMediumType/{MediumType}", Name = "GetAllStudentsByMediumType")]
[ProducesResponseType(StatusCodes.Status200OK)]
public async Task<ActionResult> GetAllStudentsByMediumType(int MediumType)
{
_logger.LogInformation("GetAllStudentsByMediumType Initiated");
var dtos = await _mediator.Send(new GetStudentListByMediumTypeQuery() { MediumType = MediumType});
_logger.LogInformation("GetAllStudentsByMediumType Completed");
return Ok(dtos);
}
}
}
7. Swagger Request and Response
a. Request
![Request]()
b. Response
![Response]()
Summary
By using this article we have seen how to use stored procedure in repository by following these steps.
We can achieve the execution of stored procedure by getting Database Connection and Connection String through DbContext.
Thank you for reading.