RESTFUL WEB API USING ASP.NET CORE 2.0 WITH MSSQL(using Dapper)

In here I am going to create a CRUD application by using Dapper for the Data Access Layer, ASP.NET CORE 2.0 for the Rest API layer, and SQL Server for the database. The IDE used was Visual Studio Community 2017.

When creating a WEB API using ASP.NET CORE and Entity Framework CORE, These are the following tools we want ;

  1. Visual Studio 2017
  2. MSSQL server
  3. POSTMAN

STEPS TO CREATE A WEB API WITH CRUD OPERATIONS

  1. First create a database and a table using mssql.
Figure 1. Table creation

2.Open VS 2017 and create a new project (Empty ASP.NET core web application)

Figure 2. New project Menu VS 2017

Lets choose Web API

Figure 3. Select Web API template.

3. In order to use Entity Framework core we have to install the package

  • open package manager console
  • Install this following package
Install-Package Microsoft.EntityFrameworkCore.SqlServer
  • To enable the reverse engineering to the existing database we have to install the following packages.
Install-Package Microsoft.EntityFrameworkCore.Tools –Pre
Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design

5. To create models for the existing database we have to use Scaffold-DbContext command.

Scaffold-DbContext “Server=your server name;Database=Login;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

6. This will create models according to the tables in the database as follows.

Figure 4. Model class Creation

7. This model classes contains the fields as same as the fields you have in your User table.

Figure 5. Model class for User entity

8. For the creation of Data Access Layer we have to install Dapper package through NuGet.

Figure 6. Dapper Package in NuGet

Dapper builds on top of the ADO.NET layer which contains a set of extensions methods to the SqlConnection class.

In this example I have used several stored procedures to retrieve some data from the database.

Dapper will automatically executes these stored procedures , read or return results sets and construct the User Objects.

Dapper contains several methods ,

ExecuteAsync - This method won’t return any sql result set.

QueryAsync - This method will return a result set.

QueryAsyncFirstOrDefaultAsync- If we want to return a result set contains only one row or else you can use the method QuerySingleOrDefaultAsync.

9. After install the Dapper package we can create a class which can be serve as a Data Access Layer.

We will name it as UserDataProvider.cs under DataProvider folder.

10. Before we do any coding inside the UserDataProvider.cs class we have to create an interface as IUserDataProvider.cs(Interface defines a contract between our application and other objects. This indicates what sort of methods, properties and events are exposed by our user object.)

Figure 7. Interface IUserDataProvider

11. In UserDataProvider.cs we have to implement all the functionalities which can provide the code to fulfill the interface contract. For that do inherit our interface IUserDataProvider.cs.

In my Data Access Layer I have implemented functions for creating an user, updating an user, delete an user and retrieve all the users.

Figure 8. UserDataProvider .cs

12. After we create all the functions inside the Data Access Layer we should hook up the functions in the UserDataProvider.cs with the corresponding stubs methods in the UserController.cs class.

Controller is basically handle all the browser requests(route data and query-string values). It retrieve the data in the model and call the view which returns a response.

[Route(“api/[controller]”)]

We have to make the controller to use attribute routing. In here we are using an attribute like Route. This attribute defines the initial URI segments for all the methods in the controller .

HttpGet- Read, HttpPost- Create, HttpPut- Update, HttpDelete- Delete

Figure 9. UserController.cs

13. MVC invokes controller classes depending on the requesting/incoming URL. We can use the default URL routing logic to format by determining the code to be invoked.

This formatting part for routing should include in the Startup.cs file as follows.

Figure 10. Default URL routing Logic

14.Now we can test our app

We have implemented several endpoint like (HttpGet, HttpPost, HttpPut,HttpDelete).

To test this app we can use Http Client applications like POSTMAN.

After you run your application we can see the results by sending API requests to postman.

Figure 11. Result from get action
Figure 12. Result from get action by passing UserID
Figure 13. Update an user from PUT action and the Results from GET action
Figure 14. Delete user from DELETE action.
Figure 15. Create an user from POST action.

15.Everything has been updated in the database also.

Figure 16. Results of the Database table

Summary

Now we have created our CRUD application using Dapper package for creating Data Access Layer.

you can download the source code from GitHub