ASP.NET Core API with SQL Server Using Dapper ORM (RESTful)

In this article we will learn ASP.NET Core API with SQL Server using Dapper ORM asynchronously instead Data access layer and Test the API using Postman.

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.

In this article we discuss below points.

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

Here in this example we create a Customer Information. And create a CRUD operation using DAPPER API and test in POSTMAN.

➤ Create Table & Stored procedure

 ➥ Customer Table withe below Script.

➥ Stored Procedure to perform the CRUD Operation. Several Action code is use to perform different operation like Insert, Update, Delete and Retrieve etc. 

➤ Create a ASP .NET Core API Application

 ➥ Open VS ⟹ File New Project ⟹ ASP .NET Core Web Application ⟹ API as template

Fig-1

➤ Installing Dapper ORM

After creating the ASP .NET Core API application we need to install Dapper through Nuget OR you can install using below command in Nuget package manager console.

Install-Package Dapper -Version 2.0.35

Fig-2

➤ Using Dapper Async method

 ➥ Add the connection string inside appsettings.json file

 ➥ Add the Model class of Customer.cs inside the Models folder.

 ➥ We add a Repository folder and add a Interface ICustomerRepository.cs

 ➥ Then add the CustomerRepository.cs class, ICustomerRepository.cs implement into it

Code Snippet Explanation
 ⇢ In CustomerRepository we call the stored procedure using Dapper ORM.
 ⇢ using Dapper namespace is use to add the dapper class into it.
 ⇢ using Microsoft.Extensions.Configuration is responsible to inject the interface into controller.
 ⇢ Other details are shared in below image diagram [Fig-3]

Fig-3 Here we use Action Code "A" to fetch data from stored procedure

Like this we did the method ADDEditCustomer(),DeleteCustomer() and GetCustomerByID() in CustomerRepository.cs. In ADDEditCustomer() we manage in procedure level if the customer data is not in the table it insert else it update.

➥ Add the Interface and class into the ConfigureServices inside the startup.cs class. Adding services to this container will make them available for dependency injection.  That means we can inject those services anywhere in our application. 

➥ Now create a Controller named as "CustomerController" and here inject ICustomerRepository interface like below and here we define the GET,POST, DELETE method to add/edit/delete customer.

Code Snippet Explanation:

⇢ [ApiController] filter attribute is use to specify that the controller is a Web API Controller.
⇢ Here we inject constructor injection of ICustomerRepository.
⇢ GetCustomers() is call to the interface and associate method is called then using Dapper it call to the procedure and give back to us the result.
⇢ GetByCustomerByID() is use to return of one customer into the customers list.
⇢ AddEditCustomer() is used for to Insert and update the Customers.
⇢ DeleteById() is used for to delete particular customer by Id.

➤ Postman Tool for testing the API

Let's open Postman tool to test the API. Run the DapperAPI application mind that we have set the route as  [Route("api/[controller]/[action]")] so our URL is http://localhost:59003/api/controller/action.

 ➥ Add Customer Using Postman

Using the below API method and the parameters you can add the customer information.

Fig-4

You can see the data is inserted into the table as expected.

Fig-5

➥ Edit Customer Using Postman

As we are using same method AddEditCustomer() so just make change in parameter value and check.

Fig-6

After submitting you can see the changed customer information.

Fig-7

 ➥ Retrieve Customer Using Postman

To retrieve the customer we need to call the Get method using Postman like below;
http://localhost:59003/api/Customer/GetCustomers

Fig-8

➥ Retrieve Customer By ID Using Postman

Let's add manually another customer in database so we need to call the Get method by Id using Postman like below;
http://localhost:59003/api/Customer/GetByCustomerByID/3

Fig-9

➥ Delete Customer By ID Using Postman

Like retrieve by customer Id we also delete the data by Customer by ID to follow this method.

http://localhost:59003/api/Customer/DeleteById/3

Fig-10

When you refresh the table data you can find that the Customer ID 3 is deleted.

Fig-11

So far this article we see how to use Dapper in ASP .NET Core Web API and done the CRUD operation.

</> Source Code in Github.com/CoreProgramm/

    Summary

 In this tutorial we discussed ASP.NET Core API with SQL Server Using Dapper ORM (RESTful). If have any question related to this topic then give your feedback.