Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI

Introduction

In this article, we will learn how to build an ASP.NET Core MVC application with MSSQL using dapper asynchronously in the data access layer and then, we will test the API in Swagger UI.

What is Dapper?

Dapper is a simple object mapper for .NET and it is virtually as fast as using a raw ADO.NET data reader. An ORM is an Object Relational Mapper, which is responsible for mapping between the database and programming language. For that reason, it is called King of micro ORM

What is swagger UI?

Swagger is a tooling application for APIs build with ASP.NET Core and it generates beautiful API Documentation, including UI to explore and test operations directly from your routes, Controllers, and Models. It requires minimal coding and maintenance, allowing you to focus on building an awesome API.

We are going to follow the points below.

  • Create Table & Stored procedure
  • Create ASP.NET Core Web API Application
  • Installing Dapper ORM & Swagger
  • Using Dapper Async method
  • Using Swagger code for API

Create Table and Stored Procedure

Open SQL Server Management Studio. Create a new database and create a new table named ContactMaster.
  1. CREATE TABLE [dbo].[ContactMaster](  
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [FirstName] [varchar](250) NULL,  
  4.     [LastName] [varchar](250) NULL,  
  5.     [CompanyName] [varchar](350) NULL,  
  6.     [JobTitle] [varchar](150) NULL,  
  7.     [Email] [varchar](250) NULL,  
  8.     [Notes] [varchar](500) NULL,  
  9.     [PhoneNo] [varchar](maxNULL  
  10. ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  

Create a stored procedure for CRUD operations.

  1. CREATE PROCEDURE [dbo].[ContactFunc](  
  2.    
  3.     @Mode           VARCHAR(10),  
  4.     @Id             INT =   NULL,  
  5.     @FirstName      VARCHAR(250)=   NULL,  
  6.     @LastName       VARCHAR(250)=   NULL,  
  7.     @CompanyName    VARCHAR(350)=   NULL,  
  8.     @JobTitle       VARCHAR(150)=   NULL,  
  9.     @Email          VARCHAR(250)=   NULL,  
  10.     @Notes          VARCHAR(500)=   NULL,  
  11.     --@ContactPhoto VARCHAR(250)=   NULL,  
  12.     @PhoneNo        VARCHAR(MAX)=   NULL  
  13.   
  14. )     
  15. AS  
  16. BEGIN  
  17.     SET NOCOUNT ON;  
  18.         IF(@Mode='GETALL')  
  19.         BEGIN  
  20.             SELECT   
  21.                 Id ,        FirstName ,     LastName ,      CompanyName ,  
  22.                 JobTitle,   Email ,         Notes   ,       --ContactPhoto,  
  23.                 PhoneNo  
  24.             FROM   
  25.                 ContactMaster  
  26.         END  
  27.         ELSE IF(@Mode='GETBYID')  
  28.         BEGIN  
  29.             SELECT   
  30.                 Id ,        FirstName ,     LastName ,      CompanyName ,  
  31.                 JobTitle,   Email ,         Notes   ,       --ContactPhoto,  
  32.                 PhoneNo  
  33.             FROM   
  34.                 ContactMaster CM  
  35.             WHERE   
  36.                 CM.ID=@Id  
  37.         END  
  38.         ELSE IF(@Mode='EDIT')  
  39.         BEGIN  
  40.             IF NOT EXISTS(SELECT 1 FROM ContactMaster WHERE ID=@Id)  
  41.             BEGIN  
  42.                 INSERT INTO ContactMaster(  
  43.                     FirstName ,     LastName ,      CompanyName ,  
  44.                     JobTitle,       Email ,         Notes   ,       --ContactPhoto,  
  45.                     PhoneNo  
  46.                     )  
  47.                     VALUES (  
  48.                     @FirstName ,        @LastName ,     @CompanyName ,  
  49.                     @JobTitle,          @Email ,            @Notes  ,       --@ContactPhoto,  
  50.                     @PhoneNo  
  51.                     )  
  52.             END  
  53.             ELSE  
  54.             BEGIN  
  55.                 UPDATE   
  56.                     ContactMaster  
  57.                 SET  
  58.                     FirstName=@FirstName ,      LastName =@LastName,        CompanyName=@CompanyName ,  
  59.                     JobTitle=@JobTitle,     Email=@Email ,          Notes=@Notes    ,       --ContactPhoto=@ContactPhoto,  
  60.                     PhoneNo=@PhoneNo  
  61.                 WHERE  
  62.                     ID=@Id  
  63.             END  
  64.         END  
  65.         ELSE IF(@Mode='DELETE')  
  66.         BEGIN  
  67.             DELETE FROM ContactMaster WHERE ID=@Id  
  68.         END  
  69. END  

Create an ASP.NET Core Web API Application

Open Visual Studio.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

Create a new project and choose a project template.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

Select ASP.NET Core Application from this list and click “Next”.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

Configure your new project and click “Create”.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

You need to select ASP.NET Core Web API template.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

Now, the project is ready to use.

Installing Dapper ORM & Swagger

Right-click on your project. Select "Manage NuGet Packages". We need to add those two packages, so search for Dapper & Swagger in the browse tab. Once you have installed these in your project, you can use them in your code.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

Install over the Package Manager console.

Dapper

Install-Package Dapper -Version 1.50.5

Swagger

Install-Package Swashbuckle.AspNetCore.Swagger -Version 4.0.1

Dapper Feature

  • Dapper will expand your IDbConnection interface with normal and Async multiple methods.
  • Execute and queries method can use parameters from multiple different ways.
  • The result returned by queries method can be mapped to multiple types.

Using Dapper Async method

Let’s create a solution as Repository.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

Create a model class with ContactMaster table column.

  1. public class ContactMaster  
  2.     {  
  3.         public Int32 Id { get; set; }  
  4.         public string FirstName { get; set; }  
  5.         public string LastName { get; set; }  
  6.         public string CompanyName { get; set; }  
  7.         public string JobTitle { get; set; }  
  8.         public string Email { get; set; }  
  9.         public string Notes { get; set; }  
  10.         public string PhoneNo { get; set; }  
  11.     }  

Create an Interface in Repository using Async keyword.

  1. public interface IContactMasterRepository  
  2.     {  
  3.         Task<ContactMaster> GetByID(int id);  
  4.         Task<List<ContactMaster>> GetAll();  
  5.         Task<ContactMaster> Edit(ContactMaster contactMaster);  
  6.         Task<ContactMaster> Delete(int id);  
  7.     }  

Create the data access layer class by implementing the interface.

  1. public class ContactMasterRepository : IContactMasterRepository  
  2.     {  
  3.          private readonly IConfiguration _config;  
  4.   
  5.         public ContactMasterRepository(IConfiguration config)  
  6.         {  
  7.             _config = config;  
  8.         }  
  9.   
  10.         public IDbConnection Connection  
  11.         {  
  12.             get  
  13.             {  
  14.                 return new SqlConnection(_config.GetConnectionString("DefaultConnection"));  
  15.             }  
  16.         }  
  17.   
  18.         public async Task<ContactMaster> GetByID(int id)  
  19.         {  
  20.             try  
  21.             {  
  22.                 using (IDbConnection con = Connection)  
  23.                 {  
  24.                     string sQuery = "ContactFunc";  
  25.                     con.Open();  
  26.                     DynamicParameters param = new DynamicParameters();  
  27.                     param.Add("@Mode""GETBYID");  
  28.                     param.Add("@Id", id);  
  29.                     var result = await con.QueryAsync<ContactMaster>(sQuery, param, commandType: CommandType.StoredProcedure);  
  30.                     return result.FirstOrDefault();  
  31.                 }  
  32.             }  
  33.             catch (Exception ex)  
  34.             {  
  35.                 throw ex;  
  36.             }  
  37.         }  
  38.   
  39.         public async Task<List<ContactMaster>> GetAll()  
  40.         {  
  41.             try  
  42.             {  
  43.                 using (IDbConnection con = Connection)  
  44.                 {  
  45.                     string sQuery = "ContactFunc";  
  46.                     con.Open();  
  47.                     DynamicParameters param = new DynamicParameters();  
  48.                     param.Add("@Mode""GETALL");  
  49.                     var result = await con.QueryAsync<ContactMaster>(sQuery, param, commandType: CommandType.StoredProcedure);  
  50.                     return result.ToList();  
  51.                 }  
  52.             }  
  53.             catch (Exception ex)  
  54.             {  
  55.                 throw ex;  
  56.             }  
  57.   
  58.         }  
  59.   
  60.         public async Task<ContactMaster> Edit(ContactMaster contactMaster)  
  61.         {  
  62.             try  
  63.             {  
  64.                 using (IDbConnection con = Connection)  
  65.                 {  
  66.                     string sQuery = "ContactFunc";  
  67.                     con.Open();  
  68.                     DynamicParameters param = new DynamicParameters();  
  69.                     param.Add("@Mode""EDIT");  
  70.                     param.Add("@Id", contactMaster.Id);  
  71.                     param.Add("@FirstName", contactMaster.FirstName);  
  72.                     param.Add("@LastName", contactMaster.LastName);  
  73.                     param.Add("@CompanyName", contactMaster.CompanyName);  
  74.                     param.Add("@JobTitle", contactMaster.JobTitle);  
  75.                     param.Add("@Email", contactMaster.Email);  
  76.                     param.Add("@Notes", contactMaster.Notes);  
  77.                     param.Add("@PhoneNo", contactMaster.PhoneNo);  
  78.                     var result = await con.QueryAsync<ContactMaster>(sQuery, param, commandType: CommandType.StoredProcedure);  
  79.                     return result.FirstOrDefault();  
  80.                 }  
  81.             }  
  82.             catch (Exception ex)  
  83.             {  
  84.                 throw ex;  
  85.             }  
  86.         }  
  87.   
  88.         public async Task<ContactMaster> Delete(int id)  
  89.         {  
  90.             try  
  91.             {  
  92.                 using (IDbConnection con = Connection)  
  93.                 {  
  94.                     string sQuery = "ContactFunc";  
  95.                     con.Open();  
  96.                     DynamicParameters param = new DynamicParameters();  
  97.                     param.Add("@Mode""DELETE");  
  98.                     param.Add("@Id", id);  
  99.                     var result = await con.QueryAsync<ContactMaster>(sQuery, param, commandType: CommandType.StoredProcedure);  
  100.                     return result.FirstOrDefault();  
  101.                 }  
  102.             }  
  103.             catch (Exception ex)  
  104.             {  
  105.                 throw ex;  
  106.             }  
  107.         }  
  108.     }  

Add the SQL Server connection string from the appsetting.json file.

  1. "ConnectionStrings": {  
  2.     "DefaultConnection""Server=DESKTOP-52S5J7J;Database=DotnetBlog;User Id=sa;password=Thiru@123"  
  3.   }  

Now, we are creating a controller to inject the ContactMasterRepository.

  1. [Route("api/[controller]")]  
  2.     [ApiController]  
  3.     public class ContactMasterController : Controller  
  4.     {  
  5.         private readonly IContactMasterRepository _contactMasterRepo;  
  6.   
  7.         public ContactMasterController(IContactMasterRepository contactMasterRepo)  
  8.         {  
  9.             _contactMasterRepo = contactMasterRepo;  
  10.         }  
  11.   
  12.         [HttpGet("{id}")]  
  13.         public async Task<ActionResult<ContactMaster>> GetByID(int id)  
  14.         {  
  15.             return await _contactMasterRepo.GetByID(id);  
  16.         }  
  17.   
  18.         [HttpGet]  
  19.         public async Task<ActionResult<List<ContactMaster>>> GetAll()  
  20.         {  
  21.             return await _contactMasterRepo.GetAll();  
  22.         }  
  23.   
  24.         [HttpPost]  
  25.         public async Task<ActionResult<ContactMaster>> Edit([FromBody]ContactMaster contactMaster)  
  26.         {  
  27.             if (contactMaster == null || !ModelState.IsValid)  
  28.             {  
  29.                 return BadRequest("Invalid State");  
  30.             }  
  31.   
  32.             return await _contactMasterRepo.Edit(contactMaster);  
  33.         }  
  34.   
  35.         [HttpDelete("{id}")]  
  36.         public async Task<ActionResult<ContactMaster>> DeleteById(int id)  
  37.         {  
  38.             return await _contactMasterRepo.Delete(id);  
  39.         }  
  40.     }  

We also need to update the project's startup.cs file to include the new Repository in the Services layer.

services.AddSingleton<IContactMasterRepository, ContactMasterRepository>();

Using Swagger code for API

Open startup.cs file to add swagger service method.

  1. services.AddSwaggerGen(c =>  
  2.             {  
  3.                 c.SwaggerDoc("v1"new Info { Title = "My API", Version = "v1" });  
  4.             });  

Add the below line in your configure method.

  1. public void Configure(IApplicationBuilder app) {  
  2.     app.UseSwagger();  
  3.     app.UseSwaggerUI(c => {  
  4.         c.SwaggerEndpoint("/swagger/v1/swagger.json""My API V1");  
  5.     });  
  6.     app.UseMvc();  
  7. }  

Right-click on your project -> select Properties and go to “Debug” tab. Change the URL from default to swagger.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

Now, run the application and you will see the Swagger UI based result.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

Pass the parameters to get the values using Dapper ORM.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

We can see the result after debugging.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

We are even able to see the result in Swagger in JSON format.

Learn ASP.NET Core MVC With MSSQL Using Dapper ORM (RESTful) And Swagger UI 

You can download this solution from GitHub.

Calculation

In this article, we have learned ASP.NET Core MVC with MSSQL using dapper asynchronously in the Data Access Layer & testing the API in swagger UI. If you have any queries, please tell me through the comments section. Your comments are very valuable.