Maheshi Gunarathne
Dec 8, 2017 · 4 min read

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


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

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

Image for post
Figure 2. New project Menu VS 2017

Lets choose Web API

Image for post
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.

Image for post
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.

Image for post
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.


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.

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

15.Everything has been updated in the database also.

Image for post
Figure 16. Results of the Database table


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

you can download the source code from GitHub

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store