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.

CREATE TABLE [dbo].[ContactMaster](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](250) NULL,
[LastName] [varchar](250) NULL,
[CompanyName] [varchar](350) NULL,
[JobTitle] [varchar](150) NULL,
[Email] [varchar](250) NULL,
[Notes] [varchar](500) NULL,
[PhoneNo] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Create a stored procedure for CRUD operations.

CREATE PROCEDURE [dbo].[ContactFunc](
@Mode VARCHAR(10),
@Id INT = NULL,
@FirstName VARCHAR(250)= NULL,
@LastName VARCHAR(250)= NULL,
@CompanyName VARCHAR(350)= NULL,
@JobTitle VARCHAR(150)= NULL,
@Email VARCHAR(250)= NULL,
@Notes VARCHAR(500)= NULL,
--@ContactPhoto VARCHAR(250)= NULL,
@PhoneNo VARCHAR(MAX)= NULL
)
AS
BEGIN
SET NOCOUNT ON;
IF(@Mode='GETALL')
BEGIN
SELECT
Id , FirstName , LastName , CompanyName ,
JobTitle, Email , Notes , --ContactPhoto,
PhoneNo
FROM
ContactMaster
END
ELSE IF(@Mode='GETBYID')
BEGIN
SELECT
Id , FirstName , LastName , CompanyName ,
JobTitle, Email , Notes , --ContactPhoto,
PhoneNo
FROM
ContactMaster CM
WHERE
CM.ID=@Id
END
ELSE IF(@Mode='EDIT')
BEGIN
IF NOT EXISTS(SELECT 1 FROM ContactMaster WHERE ID=@Id)
BEGIN
INSERT INTO ContactMaster(
FirstName , LastName , CompanyName ,
JobTitle, Email , Notes , --ContactPhoto,
PhoneNo
)
VALUES (
@FirstName , @LastName , @CompanyName ,
@JobTitle, @Email , @Notes , --@ContactPhoto,
@PhoneNo
)
END
ELSE
BEGIN
UPDATE
ContactMaster
SET
FirstName=@FirstName , LastName =@LastName, CompanyName=@CompanyName ,
JobTitle=@JobTitle, Email=@Email , Notes=@Notes , --ContactPhoto=@ContactPhoto,
PhoneNo=@PhoneNo
WHERE
ID=@Id
END
END
ELSE IF(@Mode='DELETE')
BEGIN
DELETE FROM ContactMaster WHERE ID=@Id
END
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.

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

Configure your new project and click “Create”.

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

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.

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.

Create a model class with ContactMaster table column.

public class ContactMaster
{
public Int32 Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string CompanyName { get; set; }
public string JobTitle { get; set; }
public string Email { get; set; }
public string Notes { get; set; }
public string PhoneNo { get; set; }
}

Create an Interface in Repository using Async keyword.

public interface IContactMasterRepository
{
Task<ContactMaster> GetByID(int id);
Task<List<ContactMaster>> GetAll();
Task<ContactMaster> Edit(ContactMaster contactMaster);
Task<ContactMaster> Delete(int id);
}

Create the data access layer class by implementing the interface.

public class ContactMasterRepository : IContactMasterRepository
{
private readonly IConfiguration _config;
public ContactMasterRepository(IConfiguration config)
{
_config = config;
}
public IDbConnection Connection
{
get
{
return new SqlConnection(_config.GetConnectionString("DefaultConnection"));
}
}
public async Task<ContactMaster> GetByID(int id)
{
try
{
using (IDbConnection con = Connection)
{
string sQuery = "ContactFunc";
con.Open();
DynamicParameters param = new DynamicParameters();
param.Add("@Mode", "GETBYID");
param.Add("@Id", id);
var result = await con.QueryAsync<ContactMaster>(sQuery, param, commandType: CommandType.StoredProcedure);
return result.FirstOrDefault();
}
}
catch (Exception ex)
{
throw ex;
}
}
public async Task<List<ContactMaster>> GetAll()
{
try
{
using (IDbConnection con = Connection)
{
string sQuery = "ContactFunc";
con.Open();
DynamicParameters param = new DynamicParameters();
param.Add("@Mode", "GETALL");
var result = await con.QueryAsync<ContactMaster>(sQuery, param, commandType: CommandType.StoredProcedure);
return result.ToList();
}
}
catch (Exception ex)
{
throw ex;
}
}
public async Task<ContactMaster> Edit(ContactMaster contactMaster)
{
try
{
using (IDbConnection con = Connection)
{
string sQuery = "ContactFunc";
con.Open();
DynamicParameters param = new DynamicParameters();
param.Add("@Mode", "EDIT");
param.Add("@Id", contactMaster.Id);
param.Add("@FirstName", contactMaster.FirstName);
param.Add("@LastName", contactMaster.LastName);
param.Add("@CompanyName", contactMaster.CompanyName);
param.Add("@JobTitle", contactMaster.JobTitle);
param.Add("@Email", contactMaster.Email);
param.Add("@Notes", contactMaster.Notes);
param.Add("@PhoneNo", contactMaster.PhoneNo);
var result = await con.QueryAsync<ContactMaster>(sQuery, param, commandType: CommandType.StoredProcedure);
return result.FirstOrDefault();
}
}
catch (Exception ex)
{
throw ex;
}
}
public async Task<ContactMaster> Delete(int id)
{
try
{
using (IDbConnection con = Connection)
{
string sQuery = "ContactFunc";
con.Open();
DynamicParameters param = new DynamicParameters();
param.Add("@Mode", "DELETE");
param.Add("@Id", id);
var result = await con.QueryAsync<ContactMaster>(sQuery, param, commandType: CommandType.StoredProcedure);
return result.FirstOrDefault();
}
}
catch (Exception ex)
{
throw ex;
}
}
}

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

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

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

[Route("api/[controller]")]
[ApiController]
public class ContactMasterController : Controller
{
private readonly IContactMasterRepository _contactMasterRepo;
public ContactMasterController(IContactMasterRepository contactMasterRepo)
{
_contactMasterRepo = contactMasterRepo;
}
[HttpGet("{id}")]
public async Task<ActionResult<ContactMaster>> GetByID(int id)
{
return await _contactMasterRepo.GetByID(id);
}
[HttpGet]
public async Task<ActionResult<List<ContactMaster>>> GetAll()
{
return await _contactMasterRepo.GetAll();
}
[HttpPost]
public async Task<ActionResult<ContactMaster>> Edit([FromBody]ContactMaster contactMaster)
{
if (contactMaster == null || !ModelState.IsValid)
{
return BadRequest("Invalid State");
}
return await _contactMasterRepo.Edit(contactMaster);
}
[HttpDelete("{id}")]
public async Task<ActionResult<ContactMaster>> DeleteById(int id)
{
return await _contactMasterRepo.Delete(id);
}
}

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.

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

Add the below line in your configure method.

public void Configure(IApplicationBuilder app) {
app.UseSwagger();
app.UseSwaggerUI(c => {
c.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1");
});
app.UseMvc();
}

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

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

Pass the parameters to get the values using Dapper ORM.

We can see the result after debugging.

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

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.

2 Comments

Join the conversation! Your thoughts help the community grow.

Guest User6y ago
Start to finish in less than a day, and the project works. i had to correct myself and add the repository to the services which i missed. thank you it's good to have such a short minimalist walk through.
ram saila7y ago
I am getting InvalidOperationException: Unable to resolve service for type 'WebAPIWithDapperAndSwagger.Repository.IContactMasterRepository' while attempting to activate 'WebAPIWithDapperAndSwagger.Controllers.ContactMasterController'. Do you have any idea what it is for?