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