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 ;
- Visual Studio 2017
- MSSQL server
STEPS TO CREATE A WEB API WITH CRUD OPERATIONS
- First create a database and a table using mssql.
2.Open VS 2017 and create a new project (Empty ASP.NET core web application)
Lets choose Web API
3. In order to use Entity Framework core we have to install the package
- open package manager console
- Install this following package
- To enable the reverse engineering to the existing database we have to install the following packages.
Install-Package Microsoft.EntityFrameworkCore.Tools –Pre
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.
7. This model classes contains the fields as same as the fields you have in your User table.
8. For the creation of Data Access Layer we have to install Dapper package through 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.)
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.
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
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.
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.
15.Everything has been updated in the database also.
Now we have created our CRUD application using Dapper package for creating Data Access Layer.
you can download the source code from GitHub