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.

 

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.