How To Use Stored Procedure By Getting Database Connection Using DbContext In Repository

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

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.