Create Customer Microservice with ASP.NET 5 Using Dapper and Swagger

Create Customer Microservice with ASP.NET 5 Using Dapper and Swagger

Follow Me Here

Introduction

In this post, I am going to create a Customer Microservice WebAPI in ASP.NET 5 using a micro ORM ( Object Relational Mapping) called Dapper. Since Dapper is the focal point of this blog, I shall not take up Fluent Validation here although in a production scenario validation is a must before sending the data to the server. I shall take up the Fluent validation in some other forthcoming post.

What is Dapper?

Dapper is an object-relational mapping (ORM) for the .NET platform. It provides a framework for mapping an object-oriented domain model to a traditional relational database. Dapper is created by the experts behind the Stackoverflow

  • Dapper provides a mapping between databases and .NET objects.
  • It owns the title of King of Micro ORM in terms of speed and is as fast as using a raw ADO.NET data reader.
  • It extends the IDbConnection by providing useful extension methods to query your database.

Dapper is classed as a “micro” ORM. That is, it’s an ORM but is extremely lightweight and gives only very basic functionality. Whereas we might class something like Entity Framework or NHibernate as a fully featured heavyweight ORM, Dapper provides minimal overhead and really only helps you out with some core basics.

When to use Dapper?

When deciding whether to use Dapper or not, one should bear in mind the primary reason for its existence – performance. The original developers of Dapper were using Entity Framework Core’s predecessor – Linq to SQL. They found that query performance wasn’t good enough for the increasing traffic that the site in question (Stackoverflow) was experiencing, so they wrote their own micro ORM.

Dapper is therefore a good choice in scenarios where read-only data changes frequently and is requested often. It is particularly good in stateless scenarios (e.g. the web) where there is no need to persist complex object graphs in memory for any duration.

Dapper does not translate queries written in .NET languages to SQL like a full blown ORM. So you need to be comfortable writing queries in SQL, or have someone write them for you.

Swagger Tool

This project will also have a Swagger tool from Swashbuckle to test the responses corresponding to user requests (instead of Postman). Swagger is a tool for documentation as well testing of responses integrated with the application without having to use an external tool such as Postman. It is very easy to handle and easily integrates with your project in Visual Studio 2019.

Swagger UI allows anyone — be it your development team or your end consumers — to visualize and interact with the API’s resources without having any of the implementation logic in place. It’s automatically generated from your OpenAPI (formerly known as Swagger) Specification, with the visual documentation making it easy for back end implementation and client side consumption.

Create a Web API Project

With the introduction out of the way, let’s jump to Visual Studio and start creating the project. Accordingly, we create an ASP.NET Core Web API project following the built-in template, picking up ASP.NET 5 as the platform.

Open Visual Studio 2019. On Add a new project dialog, highlight ASP.NET Core Web Application (C#) as below and click Next

On the following dialog that opens up, highlight ASP.NET Core Web API and selecting ASP.NET Core 5.0 from the drop down

NOTE: Swagger will be automatically installed and configured for you if you keep the default checked for Enable OpenAPI support

On the Configure your new project dialog, give a suitable project name and location. Click Create to create the project.

I added this project to an existing solution named “Microservices.WebApi” that I created earlier. This solution has two existing projects 1) Product.Microservice Web API project that builds a Product Microservice using EF Core and CQRS (Command Query Responsibility Segregation) pattern and EF Core, and 2) A Gateway.WebApi project that would use Ocelot API Gateway. The Solution Explorer looks like this:

Add the NuGet Packages

I shall discuss these two projects in a separate blog post. For now let’s move on an add the Dapper NuGet package by  executing the following command in Package Manager Console

PM> Install-Package Dapper

Create the Database

Open the SQL Server Management Studio (SSMS) and create a database let’s say CustomerDb. Create a Customer table by executing the following script

CREATE TABLE [dbo].[Customer](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] nvarchar NOT NULL,
[Address] nvarchar NULL,
[Telephone] nvarchar NULL,
[Email] nvarchar NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)

Create the Customer Model

Create a Models folder in the Customer.Microservice project. Add a class Customer.cs and paste the following code to the Customer class. The Customer class will interact with the SQL database.

 public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string  Telephone { get; set; }
        public string EMail { get; set; }
    }

Create the CustomerController

Now in the Controllers folder add an API Controller with read/write actions and name it CustomerController

Create the ConnectionString in appsettings.json

Now, we need to define the connection string of our database, so that the application can connect to our Database for performing CRUD operations. Open up the appsettings.json file in the Customer.Microservice project and add the following lines:

"ConnectionStrings": {

    "Defaultconnection": "Data Source=LAPTOP-LHM7GE2K\\SQLEXPRESS;Initial Catalog=CustomerDb;Integrated Security=True"

  },

The above ConnectionString is specific to my scenario and will vary from yours. Make sure you change the server name (Data Source) and database (Initial Catalog) to reflect your actual connection string.

Create CustomerController Action Methods for Web Api CRUD Operation

Before creating the functional action methods, we have to install the NuGet package: System.Data.SqlClient from the Manage NuGet Packages menu (by right clicking the project), browsing for and installing the package. This package, as usual, can also be installed through the PM console by the following command:

PM> Install-Package System.Data.SqlClient

Getting the Data from the Database Table

Now paste the following code to get all customers. Since its a get operation hence we decorate it with HttpGet attribute.

[HttpGet]
        public async Task<IEnumerable<Models.Customer>> GetAllCustomers()
        {
            IEnumerable<Models.Customer> customers; 
           
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                var sqlQuery = "Select * From Customer";
                customers = await connection.QueryAsync<Models.Customer>(sqlQuery);
            }
            return customers; 

        }

In this method, we are returning a List or IEnumerable of Customer type which basically iterates over all the records in the Customer table in the database and queries them with the SQL query. The customers are returned as a result of passing the SQL query as a string to the QueryAsync method (of dapper) which does the magic (of reading the data from the table assigning it to the IEnumerable instance). The using statement ensures that the SqlConnection object (opened earlier) is disposed of by calling connection.Dispose( ) when we have done with the connection before it exits the using….. block. In line 8, we explicitly open the connection by calling OpenAsync.

NOTE: It is not necessary to call connection.OpenAsync() as Dapper will call it for you if the connection is not already opened. However I believe the thinking that whosoever created the connection, should be responsible for opening it, hence I have stuck to opening the connection before calling Dapper in all the Action methods to follow.

A typical run with F5 with the SwaggerUI configured gives the following screenshot: (Note the Request URL)

To get a single customer by id, paste the following code in the CustomerController:

[HttpGet("{id}")]
        public async Task<Models.Customer> GetCustomerById(int id)
        {
            Models.Customer customer = new Models.Customer();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                var sqlQuery = "Select * From Customer Where Id = @Id";
                customer = await connection.QuerySingleAsync<Models.Customer>(sqlQuery, new {Id = id });
            }

            return customer;
        }

In this case, we are calling the QuerySingleAsync method of the connection object and specifying a Customer type parameter. The type parameter tells Dapper which class type to return. Dapper will take the results of the query that gets executed and map the column values to properties of the specified type. We also pass in two arguments. The first is the query that will return a single row based on a specified @Id parameter. The second parameter is an anonymous class containing properties that will map to the parameters of the query.

An F5 (IIS Express) run will show the following screenshot

Insert a Record to the Database Table

The insert operation is a post type operation, so we use HttpPost verb as in the code below to create a new record in the Customer table in the database.

[HttpPost]
        public async Task<IActionResult> Create([FromBody] Models.Customer customer)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                var sqlQuery = "Insert Into Customer (Name, Address, Telephone, Email) Values(@Name, @Address, @Telephone, @Email)";
                await connection.ExecuteAsync(sqlQuery, customer);
            }
            return Ok();

        }

In the Create action method, the type parameter is passed through the request body (In our case, it is done through the SwaggerUI embedded with the WebAPI project ). All the properties of the Customer type are mapped to the database Customer table fields. In this case, ExecuteAsync method is called and two parameters are passed. The first parameter is the Insert query and the second one is the customer type parameter. This is an extension method used by Dapper and returns the number of rows affected. Outside the using block, an empty Status OK (200) object is created which is returned back from the method as a response.

The swagger UI Post request is formed as shown below. The id field is left blank as the database is configured to have the Customer table create id automatically for a new record which will be seen when we fetch all the records to check our inserted record. Note the post request endpoint (/api/Customer) and all the properties accepting a JSON string. On Clicking the Execute button, a new record is inserted into the Customer table.

The following is the response from the server on the insert (post) operation as performed above We get a 200 Success code.

To verify the insert action, let’s call the list of all the customers again as done previously. We see the created record within the red border.

Update a Record in the Customer Table

The update operation is a put operation, so we use HttpPut verb as in the code below to update a record in the Customer table in the database.

 [HttpPut("{id}")]
        public async Task<IActionResult> Update(int id, [FromBody] Models.Customer customer)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                var sqlQuery = "UPDATE Customer SET Name = @Name, Address = @Address, Email = @Email WHERE Id = @Id";
                await connection.ExecuteAsync(sqlQuery, customer);
            }
            return Ok();
        }

An update action method accepts a Customer type parameter from the request UI and an Id parameter to search for the record that needs to be updated. As in the insert, the connection object calls the ExecuteAsync method that takes as one parameter, the update query, and the model type as the second parameter.

The swagger UI screenshot for a PUT request is as follows;

Now let’s check the updated record with the provided id in the request body.

NOTE: Even though the id provided in the text field is different (5) from that in the request body (3), the update is done with the id provided in the request body, ignoring the value in the text field (in case it is different inadvertently or intentionally)

Deleting a Record

The Delete action method code is as follows:

[HttpDelete("{id}")]
        public async Task<IActionResult> Delete(int id)
        {
            using(SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                var sqlQuery = "Delete Customer Where Id = @Id";
                await connection.ExecuteAsync(sqlQuery, new { Id = id });
            }
            return Ok();
        }

Finally, the Delete action method also calls ExecuteAsync method to which it passes two parameters. The first parameter is the SQL statement which is a simple DELETE with a WHERE clause on the Id column. The second parameter is an anonymous object containing the Id to delete.

To see the result in the Swagger UI let’s first get all the records from the Customer table in the database as shiwn below:

Now let’s delete a record with an id = 1 as below

On clicking the Execute button the record with id =1 is deleted from the database. The result of the delete action is shown below in the response body, where the record with id =1 no more exists.

Follow Me Here

 

One Response

  1. Mark says:

    Thanks for your blog, nice to read. Do not stop.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.