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

Create ASP.NET Core Web API Project

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

Checking if Web API service is running fine.

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 Business Layer project for the interfaces.

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

Step 5

 

Add Repository Layer project for interfaces.

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

Step 6

 

Add Repository Layer project for classes.

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

Step 7

 

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

 

Create database, table & stored procedure for CRUD operation

SQL Script

Script 1

 

Script to create database

createdatabase DataManagement

Script 2

 

Script to create table

CREATETABLE[dbo].[Users](
    [UserId][int] IDENTITY(1, 1) NOT NULL, [UserName][varchar](50) NULL, [UserMobile][varchar](50) NULL, [UserEmail][varchar](50) NULL, [FaceBookUrl][varchar](50) NULL, [LinkedInUrl][varchar](50) NULL, [TwitterUrl][varchar](50) NULL, [PersonalWebUrl][varchar](50) NULL, [IsDeleted][bit] NULL) ON[PRIMARY]
GO
ALTERTABLE[dbo].[Users] ADDCONSTRAINT[DF_User_IsDeleted] DEFAULT((0)) FOR[IsDeleted]
GO

Script 3

 

Script to add user

createPROCEDURE[dbo].[AddUser]
@UserName varchar(50),
    @UserMobile varchar(50),
    @UserEmail varchar(50),
    @FaceBookUrl varchar(50),
    @LinkedInUrl varchar(50),
    @TwitterUrl varchar(50),
    @PersonalWebUrl varchar(50)
AS
BEGIN
SETNOCOUNTON;
insertinto Users(UserName, UserMobile, UserEmail, FaceBookUrl, LinkedInUrl, TwitterUrl, PersonalWebUrl)
values(@UserName, @UserMobile, @UserEmail, @FaceBookUrl, @LinkedInUrl, @TwitterUrl, @PersonalWebUrl)
END
GO

Script 4

 

Script to delete user

CREATEPROCEDURE[dbo].[DeleteUser]
@UserId int
AS
BEGIN
SETNOCOUNTON;
update Users set IsDeleted = 1 where UserId = @UserId
END
Script 5 Script to get all users
CREATEPROCEDURE[dbo].[GetAllUsers]
AS
BEGIN
SETNOCOUNTON;
select * from Users
END

Script 6

 

Script to get user by Id

CREATEPROCEDURE[dbo].[GetUserById]
@UserId int
AS
BEGIN
SETNOCOUNTON;
select * from Users where UserId = @UserId;
END
GO

Script 7

 

Script to update user

CREATEPROCEDURE[dbo].[UpdateUser]
@UserId int,
@UserName varchar(50),
    @UserMobile varchar(50),
    @UserEmail varchar(50),
    @FaceBookUrl varchar(50),
    @LinkedInUrl varchar(50),
    @TwitterUrl varchar(50),
    @PersonalWebUrl varchar(50)
AS
BEGIN
SETNOCOUNTON;
update Users set
UserName = @UserName,
    UserMobile = @UserMobile,
    UserEmail = @UserEmail,
    FaceBookUrl = @FaceBookUrl,
    LinkedInUrl = @LinkedInUrl,
    TwitterUrl = @TwitterUrl,
    PersonalWebUrl = @PersonalWebUrl
where UserId = @UserId
END
GO

Step 9

 

Add an Entity for User

Create a class“ User.cs” inside“ DataManagement.Entities” project.
Complete Code
namespace DataManagement.Entities {
    publicclassUser {
        publicint UserId {
            get;
            set;
        }
        publicstring UserName {
            get;
            set;
        }
        publicstring UserMobile {
            get;
            set;
        }
        publicstring UserEmail {
            get;
            set;
        }
        publicstring FaceBookUrl {
            get;
            set;
        }
        publicstring LinkedInUrl {
            get;
            set;
        }
        publicstring TwitterUrl {
            get;
            set;
        }
        publicstring PersonalWebUrl {
            get;
            set;
        }
        publicbool IsDeleted {
            get;
            set;
        }
    }
}

Step 10

 

Write Repository Layer Code for User

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

   IUserRepository.cs Complete Code
   using DataManagement.Entities;
   using System.Collections.Generic;
   namespace DataManagement.Repository.Interfaces {
       publicinterfaceIUserRepository {
           bool AddUser(User user);
           bool UpdateUser(User user);
           bool DeleteUser(int userId);
           IList < User > GetAllUser();
           User GetUserById(int userId);
       }
   }

3. Add reference of projects
   
   “DataManagement.Entities”&“DataManagement.Repository.Interfaces” inside “DataManagement.Repository”.
   
   
4. Create a class“BaseRepository.cs” inside the project“DataManagement.Repository”.
   

   using System;
   using System.Data;
   using System.Data.SqlClient;
   namespace DataManagement.Repository {
       publicclassBaseRepository IDisposable {
           protectedIDbConnection con;
           public BaseRepository() {
               string connectionString = "Data Source=****;Initial Catalog=DataManagement;Integrated Security=True";
               con = newSqlConnection(connectionString);
           }
           publicvoid Dispose() {
               //throw new NotImplementedException();
           }
       }
   }

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

   using Dapper;
   using DataManagement.Entities;
   using System;
   using System.Collections.Generic;
   using System.Data.SqlClient;
   using System.Linq;
   usingstatic System.Data.CommandType;
   using DataManagement.Repository.Interfaces;
   namespace DataManagement.Repository {
       publicclassUserRepositoryBaseRepository,
       IUserRepository {
           publicbool AddUser(User user) {
               try {
                   DynamicParameters parameters = newDynamicParameters();
                   parameters.Add("@UserName", user.UserName);
                   parameters.Add("@UserMobile", user.UserMobile);
                   parameters.Add("@UserEmail", user.UserEmail);
                   parameters.Add("@FaceBookUrl", user.FaceBookUrl);
                   parameters.Add("@LinkedInUrl", user.LinkedInUrl);
                   parameters.Add("@TwitterUrl", user.TwitterUrl);
                   parameters.Add("@PersonalWebUrl", user.PersonalWebUrl);
                   SqlMapper.Execute(con, "AddUser", param parameters, commandType StoredProcedure);
                   returntrue;
               } catch (Exception ex) {
                   throw ex;
               }
           }
           publicbool DeleteUser(int userId) {
               DynamicParameters parameters = newDynamicParameters();
               parameters.Add("@UserId", userId);
               SqlMapper.Execute(con, "DeleteUser", param parameters, commandType StoredProcedure);
               returntrue;
           }
           publicIList < User > GetAllUser() {
               IList < User > customerList = SqlMapper.Query < User > (con, "GetAllUsers", commandType StoredProcedure).ToList();
               return customerList;
           }
           publicUser GetUserById(int userId) {
               try {
                   DynamicParameters parameters = newDynamicParameters();
                   parameters.Add("@CustomerID", userId);
                   returnSqlMapper.Query < User > ((SqlConnection) con, "GetUserById", parameters, commandType StoredProcedure).FirstOrDefault();
               } catch (Exception) {
                   throw;
               }
           }
           publicbool UpdateUser(User user) {
               try {
                   DynamicParameters parameters = newDynamicParameters();
                   parameters.Add("@UserId", user.UserId);
                   parameters.Add("@UserName", user.UserName);
                   parameters.Add("@UserMobile", user.UserMobile);
                   parameters.Add("@UserEmail", user.UserEmail);
                   parameters.Add("@FaceBookUrl", user.FaceBookUrl);
                   parameters.Add("@LinkedInUrl", user.LinkedInUrl);
                   parameters.Add("@TwitterUrl", user.TwitterUrl);
                   parameters.Add("@PersonalWebUrl", user.PersonalWebUrl);
                   SqlMapper.Execute(con, "UpdateUser", param parameters, commandType StoredProcedure);
                   returntrue;
               } catch (Exception ex) {
                   throw ex;
               }
           }
       }
   }

Step 11

Write Business Layer Code for User

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
   

   using DataManagement.Entities;
   using System.Collections.Generic;
   namespace DataManagement.Business.Interfaces {
       publicinterfaceIUserManager {
           bool AddUser(User user);
           bool UpdateUser(User user);
           bool DeleteUser(int userId);
           IList < User > GetAllUser();
           User GetUserById(int userId);
       }
   }

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
   

   using DataManagement.Business.Interfaces;
   using DataManagement.Entities;
   using DataManagement.Repository.Interfaces;
   using System.Collections.Generic;
   namespace DataManagement.Business {
       publicclassUserManager IUserManager {
           IUserRepository _userRepository;
           public UserManager(IUserRepository userRepository) {
               _userRepository = userRepository;
           }
           publicbool AddUser(User user) {
               return _userRepository.AddUser(user);
           }
           publicbool DeleteUser(int userId) {
               return _userRepository.DeleteUser(userId);
           }
           publicIList < User > GetAllUser() {
               return _userRepository.GetAllUser();
           }
           publicUser GetUserById(int userId) {
               return _userRepository.GetUserById(userId);
           }
           publicbool UpdateUser(User user) {
               return _userRepository.UpdateUser(user);
           }
       }
   }

Step 12

Write API Layer Code.

1. Add dependencies and project references.
   
   
   
   Add Controller “UserController.cs”.
   
   Complete code for UserController.cs
   

   using System.Collections.Generic;
   using Microsoft.AspNetCore.Mvc;
   using DataManagement.Business.Interfaces;
   using DataManagement.Entities;
   // For more information on enabling Web API for empty projects, visit https//go.microsoft.com/fwlink/?LinkID=397860
   namespace DataManagement.WebAPI.Controllers {
       [Route("api/[controller]")]
       publicclassUserController Controller {
           IUserManager _userManager;
           public UserController(IUserManager userManager) {
               _userManager = userManager;
           }
           // GET<td style="border<td style="border: 1px dashed #ababab;"> 1px dashed #ababab;"> api/user
           [HttpGet]
           publicIEnumerable < User > Get() {
               return _userManager.GetAllUser();
           }
           // GET api/user/5
           [HttpGet("{id}")]
           publicUser Get(int id) {
               return _userManager.GetUserById(id);
           }
           // POST api/user
           [HttpPost]
           publicvoid Post([FromBody] User user) {
               _userManager.AddUser(user);
           }
           // PUT api/user/5
           [HttpPut("{id}")]
           publicvoid Put(int id, [FromBody] User user) {
               _userManager.UpdateUser(user);
           }
           // DELETE api/user/5
           [HttpDelete("{id}")]
           publicvoid Delete(int id) {
               _userManager.DeleteUser(id);
           }
       }
   }

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

using System.Collections.Generic;
namespace DataManagement.Repository.Interfaces {
    publicinterfaceIRepository < T > whereT class {
        IEnumerable < T > Get();
        T Get(int id);
        void Add(T entity);
        void Delete(int id);
        void Update(T entity);
    }
}

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

using System;
using System.Collections.Generic;
using System.Linq;
using DataManagement.Repository.Interfaces;
using DataManagement.Entities;
using Dapper;
usingstatic System.Data.CommandType;
using System.Data.SqlClient;
namespace DataManagement.Repository {
    publicclassCustomerRepositoryBaseRepository,
    IRepository < Customer > {
        publicvoid Add(Customer entity) {
            try {
                DynamicParameters parameters = newDynamicParameters();
                parameters.Add("@CustomerName", entity.CustomerName);
                parameters.Add("@CustomerEmail", entity.CustomerEmail);
                parameters.Add("@CustomerMobile", entity.CustomerMobile);
                SqlMapper.Execute(con, "AddCustomer", param parameters, commandTypeStoredProcedure);
            } catch (Exception ex) {
                throw ex;
            }
        }
        publicvoid Delete(int id) {
            DynamicParameters parameters = newDynamicParameters();
            parameters.Add("@CustomerId", id);
            SqlMapper.Execute(con, "DeleteCustomer", param parameters, commandTypeStoredProcedure);
        }
        publicIEnumerable < Customer > Get() {
            IList < Customer > customerList = SqlMapper.Query < Customer > (con, "GetAllCustomer", commandTypeStoredProcedure).ToList();
            return customerList;
        }
        publicCustomer Get(int id) {
            DynamicParameters parameters = newDynamicParameters();
            parameters.Add("@CustomerID", id);
            returnSqlMapper.Query < Customer > ((SqlConnection) con, "GetCustomerById", parameters, commandTypeStoredProcedure).FirstOrDefault();
        }
        publicvoid Update(Customer entity) {
            DynamicParameters parameters = newDynamicParameters();
            parameters.Add("@CustomerID", entity.CustomerName);
            parameters.Add("@CustomerName", entity.CustomerName);
            parameters.Add("@CustomerEmail", entity.CustomerEmail);
            parameters.Add("@CustomerMobile", entity.CustomerMobile);
            SqlMapper.Execute(con, "UpdateCustomer", param parameters, commandType StoredProcedure);
        }
    }
}

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

using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using DataManagement.Entities;
using DataManagement.Repository.Interfaces;
// For more information on enabling Web API for empty projects, visit https//go.microsoft.com/fwlink/?LinkID=397860
namespace DataManagement.API.Controllers {
    [Route("api/[controller]")]
    publicclassCustomerController Controller {
        IRepository < Customer > _customerRepository;
        public CustomerController(IRepository < Customer > customerRepository) {
                _customerRepository = customerRepository;
            }
            [HttpGet]
        publicIEnumerable < Customer > Get() {
                return _customerRepository.Get();
            }
            [HttpGet("{id}")]
        publicCustomer Get(int id) {
                return _customerRepository.Get(id);
            }
            [HttpPost]
        publicvoid Post([FromBody] Customer customer) {
                _customerRepository.Add(customer);
            }
            [HttpPut("{id}")]
        publicvoid Put(int id, [FromBody] Customer customer) {
                _customerRepository.Update(customer);
            }
            [HttpDelete("{id}")]
        publicvoid Delete(int id) {
            _customerRepository.Delete(id);
        }
    }
}

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.

publicvoid InsertMultipleUsers() {
    object myObj = new [] {
        new {
            name = "B Narayan", email = "[email protected]"
        },
        new {
            name = "Manish Sharma", email = "manish.sharma**@outlook.com"
        },
        new {
            name = "Rohit Kumar", email = "rohit.kumar**@outlook.com"
        }
    };
    con.Execute(@ "insert Users(UserName, UserEmail) values (@name, @email)", myObj);
}

Mapping executed result to strongly Type List.

publicIList < User > GetAllUser() => SqlMapper.Query < User > (con, "GetAllUsers", commandType StoredProcedure).ToList();

Mapping executed result to dynamic objects.

publicIList < dynamic > GetAllUser() => SqlMapper.Query < dynamic > (con, "GetAllUsers", commandType StoredProcedure).ToList();

Execute a command without returning anything

DynamicParameters parameters = newDynamicParameters();
parameters.Add("@UserId", userId);
SqlMapper.Execute(con, "DeleteUser", param parameters, commandType StoredProcedure);

Select Multiple Results

(List < Customer > customers, List < User > users) GetUsersAndCustomers() {
    using(var multi = con.QueryMultiple("select * from Customers;select * from Users")) {
        var customers = multi.Read < Customer > ().ToList();
        var users = multi.Read < User > ().ToList();
        return (customers, users);
    }
}

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

internal class ColumnMap {
    private readonly Dictionary < string, string > forward = new Dictionary < string, string > ();
    private readonly Dictionary < string, string > reverse = new Dictionary < string, string > ();
    public void Add(string t1, string t2) {
        forward.Add(t1, t2);
        reverse.Add(t2, t1);
    }
    public string this[string index] {
        get {
            // Check for a custom column map.
            if (forward.ContainsKey(index)) return forward[index];
            if (reverse.ContainsKey(index)) return reverse[index];
            // If no custom mapping exists, return the value passed in.
            return index;
        }
    }
}

In Repository class map it easily. Following the code snippet for the same.

publicIEnumerable < Product > Get() {
    var columnMap = newColumnMap();
    columnMap.Add("Id", "ProductId");
    columnMap.Add("Name", "ProductName");
    columnMap.Add("Price", "ProductPrice");
    SqlMapper.SetTypeMap(typeof(Product), newCustomPropertyTypeMap(typeof(Product), (type, columnName) => type.GetProperty(columnMap[columnName])));
    List < Product > products = SqlMapper.Query < Product > (
        (SqlConnection) con, "select * from Products", commandTypeText).ToList();
    return products;
}

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#