ASP.NET Core Web API With Dapper And VS 2017

I will create an ASP.NET Core Web API from scratch, using Visual Studio 2017 ,and you can follow along or skip some steps, if you are already aware of how to create ASP.NET Core Web API.

I will start from a very basic concept and then go to the advanced concepts. In this project, I will be using 3 layers: API Layer, Business Layer, and Repository Layer, and some separate projects for the entities, then I will use Dapper at the Repository Layer as Micro ORM. I will show you how to add assembly (*.DLL) references in .NET Core API and how to add .NET Core Class Library (.NET Standard). I will also discuss about skipping Business Layer and directly calling Repository Layer class from Web API Layer and using Generic Repository interface.

I am going to use Visual Studio 2017 RC and IDE, but you can also use Visual Studio 2015 as IDE. If you didn’t have the template for ASP.NET Core, you need to install this template.

Step 1

1. Open “Visual Studio 2017” -> go to “File” menu -> New -> Project
   
   Or
   
   Press “Ctrl + Shift + N” after opening Visual Studio Application
   
   
2. Select project template.
   
   
   
   Select the project template, as displayed in the preceding screenshot and click OK to continue. Here, I am selecting ASP.NET Core Web Application with .NET Core Framework because I would like to create a project, which can run on multiple operating systems including Windows, Mac and Linux.
   
   
3. Select a template from ASP.NET Core templates.
   
   
   
   

Please select the “Web API” template from ASP.NET Core templates list. Right now, I would like to focus only on Web API and Dapper, so I would recommend that you do not select any authentication and also enable Docker Support. In the next article, I will explain how can we enable Docker support and Authentication mechanism. Preceding is the screenshot for the same.

After selecting “Web API” template from ASP.NET Core templates list, click OK to continue. Visual Studio will create a sample Web API Service for you.



As you can see in the preceding screenshot it has added a default controller. “ValuesController.cs”. You can delete this file later on because I am not going to use this default controller but right now, this file is useful for us and we can at least test whether our web application is running or not.

Step 2

Just press F5 or Ctrl+F5 and you will find that it will open a Web API URL in a new Browser Window.



As you can see in the preceding screenshot, it has taken a URL and also displayed some values in the browser.

If you look closely at the Visual Studio Run button, then you will find that there is one more option available apart from IIS hosting and it is self-hosting. Following is the screenshot for the same for your reference.



You may have noticed that the base class (parent controller) for “ValuesController” is different than the earlier versions of Web API. Because in ASP.NET Core Framework, ASP.NET MVC & ASP.NET Web API has been merged and both have the parent controller as Controller (Microsoft.AspNetCore.Mvc.Controller).



Step 3

Add Business logic project for Web API.

Right click on Solution Explorer -> Add -> New Project.



Expand the installed template and select .NET Core and inside it, select Class Library (.NET Standard).

.NET standard library is a formal specification of .NET APIs, which are intended to be available on all .NET runtimes. The motivation behind the standard library is establishing greater uniformity in the .NET ecosystem. You can read more about .NET standard library here .

Give the project name “DataManagement.Business” and press OK to continue. Afterwards, Visual Studio will add a new project of type “.NET Standard Library”.

Step 4

Add a project for Entities

Repeat the same step mentioned in step 3 and add a project with the name “DataManagement.Entities” of type “.NET Standard Library”.

After adding all the projects mentioned above, your Solution Explorer will look as shown below in the screenshot.



Step 8

1. Add reference of project “DataManagement.Entities”inside “DataManagement.Repository.Interfaces”.
   
   For ASP.NET Core you will find it a little bit different as compared to earlier versions while checking for added references. Following is a snapshot for the same.
   
   
   
   
2. Create an Interface “IUserRepository.cs” inside the solution “DataManagement.Repository.Interfaces”.
   
   1. IUserRepository.cs Complete Code
   2. using DataManagement.Entities;
   3. using System.Collections.Generic;
   4. namespace DataManagement.Repository.Interfaces {
   5. publicinterfaceIUserRepository {
   6. bool AddUser(User user);
   7. bool UpdateUser(User user);
   8. bool DeleteUser(int userId);
   9. IList < User > GetAllUser();
   10. User GetUserById(int userId);
   11. }
   12. }
3. Add reference of projects
   
   “DataManagement.Entities”&“DataManagement.Repository.Interfaces” inside “DataManagement.Repository”.
   
   
4. Create a class“BaseRepository.cs” inside the project“DataManagement.Repository”.
   
   1. using System;
   2. using System.Data;
   3. using System.Data.SqlClient;
   4. namespace DataManagement.Repository {
   5. publicclassBaseRepository IDisposable {
   6. protectedIDbConnection con;
   7. public BaseRepository() {
   8. string connectionString = "Data Source=****;Initial Catalog=DataManagement;Integrated Security=True";
   9. con = newSqlConnection(connectionString);
   10. }
   11. publicvoid Dispose() {
   12. //throw new NotImplementedException();
   13. }
   14. }
   15. }
5. Install some packages from NuGet Package manager.
   
   
   
   In the preceding screenshot, you can see that at Repository Layer is displaying that 4 packages have been installed from NuGet Package Manager. The project “DataManagement.Repository” is of .NET Core Class Library(.NET Standard) refers to the screenshot of step 3 for .NET Standard library.
   
   As “DataManagement.Repository” is .NET standard library, so you will find that NuGet package “NETStandard.Library” is installed already, but you will require to install 3 other packages.
   
   
   1. System.Data.SqlClient
      
      System.Data.SqlClient is NuGet Package from Microsoft. I have installed this package, because I am including “System.Data” & “System.Data.SqlClient” in using block.
      
      I would like to clarify that whatever packages we need for ASP.NET Core Cross Platform Application, we will have to install them from NuGet Package Manager and you will not find any installed DLL on the machine for the same. The screenshot is given below to display the message that “No Framework assemblies were found on the machine.”
      
      
      
      
   2. System.Runtime
      
      System.Runtime is also a NuGet Package from Microsoft. I have used “IDisposable” interface for which I need to install “System.Runtime”.
      
      
   3. Dapper
      
      Dapper is a high performance Micro-ORM. As stated earlier, I am not using Entity Framework in this project, as I am using Dapper instead.
      
      
6. Create a class“UserRepository.cs” inside the project “DataManagement.Repository”.
   
   1. using Dapper;
   2. using DataManagement.Entities;
   3. using System;
   4. using System.Collections.Generic;
   5. using System.Data.SqlClient;
   6. using System.Linq;
   7. usingstatic System.Data.CommandType;
   8. using DataManagement.Repository.Interfaces;
   9. namespace DataManagement.Repository {
   10. publicclassUserRepositoryBaseRepository,
   11. IUserRepository {
   12. publicbool AddUser(User user) {
   13. try {
   14. DynamicParameters parameters = newDynamicParameters();
   15. parameters.Add("@UserName", user.UserName);
   16. parameters.Add("@UserMobile", user.UserMobile);
   17. parameters.Add("@UserEmail", user.UserEmail);
   18. parameters.Add("@FaceBookUrl", user.FaceBookUrl);
   19. parameters.Add("@LinkedInUrl", user.LinkedInUrl);
   20. parameters.Add("@TwitterUrl", user.TwitterUrl);
   21. parameters.Add("@PersonalWebUrl", user.PersonalWebUrl);
   22. SqlMapper.Execute(con, "AddUser", param parameters, commandType StoredProcedure);
   23. returntrue;
   24. } catch (Exception ex) {
   25. throw ex;
   26. }
   27. }
   28. publicbool DeleteUser(int userId) {
   29. DynamicParameters parameters = newDynamicParameters();
   30. parameters.Add("@UserId", userId);
   31. SqlMapper.Execute(con, "DeleteUser", param parameters, commandType StoredProcedure);
   32. returntrue;
   33. }
   34. publicIList < User > GetAllUser() {
   35. IList < User > customerList = SqlMapper.Query < User > (con, "GetAllUsers", commandType StoredProcedure).ToList();
   36. return customerList;
   37. }
   38. publicUser GetUserById(int userId) {
   39. try {
   40. DynamicParameters parameters = newDynamicParameters();
   41. parameters.Add("@CustomerID", userId);
   42. returnSqlMapper.Query < User > ((SqlConnection) con, "GetUserById", parameters, commandType StoredProcedure).FirstOrDefault();
   43. } catch (Exception) {
   44. throw;
   45. }
   46. }
   47. publicbool UpdateUser(User user) {
   48. try {
   49. DynamicParameters parameters = newDynamicParameters();
   50. parameters.Add("@UserId", user.UserId);
   51. parameters.Add("@UserName", user.UserName);
   52. parameters.Add("@UserMobile", user.UserMobile);
   53. parameters.Add("@UserEmail", user.UserEmail);
   54. parameters.Add("@FaceBookUrl", user.FaceBookUrl);
   55. parameters.Add("@LinkedInUrl", user.LinkedInUrl);
   56. parameters.Add("@TwitterUrl", user.TwitterUrl);
   57. parameters.Add("@PersonalWebUrl", user.PersonalWebUrl);
   58. SqlMapper.Execute(con, "UpdateUser", param parameters, commandType StoredProcedure);
   59. returntrue;
   60. } catch (Exception ex) {
   61. throw ex;
   62. }
   63. }
   64. }
   65. }