Learn ASP.NET Core MVC with MSSQL Using Dapper ORM (RESTful)

Share:

In this blog, we will learn ASP.NET Core MVC with MSSQL using dapper asynchronously instead Data access layer & testing the API using google ARC tool

What is Dapper?

Dapper is 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 database and programming language. For that reason it is called King of micro ORM

We are going to follow points below

  • Create Table & Store procedure
  • Create ASP.NET Core Web API Application
  • Installing Dapper ORM
  • Using Dapper Async method
  • Using ARC Tool for testing the API

Create table & Store Procedure

Open SQL Server management studio. Create a new database & create a new ContactMaster table.

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]

Creating a store procedure for CURD operation

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 ASP.NET Core Web API Application

Open Visual Studio tool.

Create a new project & choose project temple

Select ASP.NET Core Application in this list then click “Next”

Configure your new project and click “Create”

You will select ASP.NET Core Web API template

Now project has been ready to use

Installing Dapper ORM

Right click on your project. Select Manage NuGet Packages. We need to add this packages so search Dapper. Once you installed in your project you can able to use you code.

Install over the package manager console

Dapper:

Install-Package Dapper -Version 1.50.5

Dapper Feature:

  • Dapper will expend your IDbConnection interface with normal & 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 creating 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 data access layer class with implement 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 appsetting.json file

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

Creating a controller to injected 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 startup.cs file to include the new Repository in the services layer.

services.AddSingleton<IContactMasterRepository, ContactMasterRepository>();

Using ARC Tool for testing the API

Open ARC tool & send the URl “https://localhost:44309/api/ContactMaster”

We can able to see the result over the debug

You can download this solution from GitHub URL

Calculation

In this article, we have learned ASP.NET Core MVC with MSSQL using dapper asynchronously instead Data access layer & testing the API using google ARC tool

Share:

Leave a reply