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

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. }

Step 11

1. Add the project reference for “DataManagement.Entities” inside “DataManagement.Business.Interfaces”.
   
   
2. Add an interface “IUserManager.cs”inside .NET Standard Class Library “DataManagement.Business.Interfaces”.
   
   IUserManager.csComplete Code
   
   1. using DataManagement.Entities;
   2. using System.Collections.Generic;
   3. namespace DataManagement.Business.Interfaces {
   4. publicinterfaceIUserManager {
   5. bool AddUser(User user);
   6. bool UpdateUser(User user);
   7. bool DeleteUser(int userId);
   8. IList < User > GetAllUser();
   9. User GetUserById(int userId);
   10. }
   11. }
3. Add project reference of “DataManagement.Entities”, “DataManagement.Business.Interfaces”&“DataManagement.Repository.Interfaces” inside “DataManagement.Business”.
   
   
4. Add a class “UserManager.cs” inside the .NET Standard Class Library “DataManagement.Business”
   
   UserManager.cs Complete Code
   
   1. using DataManagement.Business.Interfaces;
   2. using DataManagement.Entities;
   3. using DataManagement.Repository.Interfaces;
   4. using System.Collections.Generic;
   5. namespace DataManagement.Business {
   6. publicclassUserManager IUserManager {
   7. IUserRepository _userRepository;
   8. public UserManager(IUserRepository userRepository) {
   9. _userRepository = userRepository;
   10. }
   11. publicbool AddUser(User user) {
   12. return _userRepository.AddUser(user);
   13. }
   14. publicbool DeleteUser(int userId) {
   15. return _userRepository.DeleteUser(userId);
   16. }
   17. publicIList < User > GetAllUser() {
   18. return _userRepository.GetAllUser();
   19. }
   20. publicUser GetUserById(int userId) {
   21. return _userRepository.GetUserById(userId);
   22. }
   23. publicbool UpdateUser(User user) {
   24. return _userRepository.UpdateUser(user);
   25. }
   26. }
   27. }

Step 12

1. Add dependencies and project references.
   
   
   
   Add Controller “UserController.cs”.
   
   Complete code for UserController.cs
   
   1. using System.Collections.Generic;
   2. using Microsoft.AspNetCore.Mvc;
   3. using DataManagement.Business.Interfaces;
   4. using DataManagement.Entities;
   5. // For more information on enabling Web API for empty projects, visit https//go.microsoft.com/fwlink/?LinkID=397860
   6. namespace DataManagement.WebAPI.Controllers {
   7. [Route("api/[controller]")]
   8. publicclassUserController Controller {
   9. IUserManager _userManager;
   10. public UserController(IUserManager userManager) {
   11. _userManager = userManager;
   12. }
   13. // GET<td style="border<td style="border: 1px dashed #ababab;"> 1px dashed #ababab;"> api/user
   14. [HttpGet]
   15. publicIEnumerable < User > Get() {
   16. return _userManager.GetAllUser();
   17. }
   18. // GET api/user/5
   19. [HttpGet("{id}")]
   20. publicUser Get(int id) {
   21. return _userManager.GetUserById(id);
   22. }
   23. // POST api/user
   24. [HttpPost]
   25. publicvoid Post([FromBody] User user) {
   26. _userManager.AddUser(user);
   27. }
   28. // PUT api/user/5
   29. [HttpPut("{id}")]
   30. publicvoid Put(int id, [FromBody] User user) {
   31. _userManager.UpdateUser(user);
   32. }
   33. // DELETE api/user/5
   34. [HttpDelete("{id}")]
   35. publicvoid Delete(int id) {
   36. _userManager.DeleteUser(id);
   37. }
   38. }
   39. }
2. Add code for dependency injection
   
   Go to the class startup.cs and inside the method “ConfigureServices(IServiceCollection services)”, add 2 lines of code given below.
   
   services.AddTransient<IUserManager, UserManager>();
   services.AddTransient<IUserRepository, UserRepository>();
   
   
   
   Testing the Web API Service Using Postman.
   
   Now, our Web API Service is ready. We can test it.
   
   To test the Service, we can use any Browser, but if we have to send any data in header or have to pass some authentication info then Postman is a very simple tool to test API Services. I am going to use Postman but you can use any other apps also.
   
   
   
   As you can see in the screenshot, shown above, the data is being displayed in JSON because JSON format is by default selected in Postman.
   
   So far, I have just explained how we are going to create a simple Web API Service, using ASP.NET Core.
   
   Since this application is not an optimized architecture, I have just explained how to create ASP.NET Core Web API Service. Following are some more modifications and optimizations that can be done with this application.

Creating generic repository interface.

Earlier, we have created IUserRepository.cs as repository interface but instead of that, now I am going to create a Generic Repository IRepository.cs.

Complete Code of IRepository.cs

Now, I am going to add a new Entity Customer in the project “DataManagement.Entities” with the name Customer.cs & also execute some stored procedure for CRUD operation. I am not writing those code snippets here, as those are very simple and you can write it by yourself.

Complete Code of CustomerRepository.cs
Skipping Business Layer

In the previous example, you have seen that Web API Layer is calling Business Layer and Business Layer was calling Repository layer but if you look closely at the code, you will find that in Business layer I am not doing anything, as I am just calling code of Repository Layer and returning it to Web API Layer.

Thus, if we do not need to write any manipulation logic, we can skip Business Layer. Now, I am directly calling Repository Layer from Web API.

CustomerController class complete code
Exploring Dapper

So far, we have seen how can we perform CRUD operations very easily using Dapper, but these are the basic things and Dapper can do a lot more.



Execute a command multiple times with Dapper.
Mapping executed result to strongly Type List.
Mapping executed result to dynamic objects.
Execute a command without returning anything
Select Multiple Results
Mapping your Columns

Apart from the features mentioned above, you can do many more things with Dapper. If you are thinking that your database column name is different than your C# class object, you can also do mapping of your columns.



To do Column mapping, you need to add a class ColumnMap.cs
In Repository class map it easily. Following the code snippet for the same.
You can also download NuGet package “Dapper.FluentMap” and it will provide mapping in a more convenient way. You can explore “Dapper.FluentMap” here.

I have attached the complete source code with this article for reference. Sometimes, I have used C# 6 & C# 7 syntax in the code, if you are not aware about C# 7 concepts, you can refer to the following articles for C# 7.

• Top 10 New Features Of C# 7 With Visual Studio 2017
• How to Compile & Test C# 7 Features
• Visual Studio 15 Preview First Look & C# 7

For C# basic concepts, you can refer to my recent article Basic Interview Tips in C#