.NET Core web API + Dapper – Less is more

Over the last few months I have been working on a series of .Net Core Web API projects for customers which have been connecting to existing on premises Microsoft SQL Server or Azure SQL databases I didn’t want to use the term “legacy” databases as they are part of large systems which are providing useful functionality to my customers and their clients.

One of the systems has in operation for a decade and the evolution of the database has been thoughtfully managed by the developers. They have always had to balance the business’s requirements, while trying to minimise new, and chip away at any existing technical debt.

This is the first in a longish series about my “brownfields” experiences and the non-functional requirements trade-offs we had to make. These included reliability, scalability, supportability, testability, availability, maintainability, securability extensibility, robustness and time to market considerations.

Often the applications had large existing code bases in VB.Net, C# or C++ which used ADO.Net and/or other Object Relational Mappers(ORMs) like Entity Framework(EF) and nHibernate. Over the years as developers had “come and gone” the mix of technologies had grown to the point where the codebases were difficult to maintain and to understand how the technologies interacted in production.

In a couple of organisations access to database(s) was managed by a Database Administrator(DBA) who defined the approach used (often with stored procedures) and vetted all access to data for performance, compliance and/or security considerations.

Unless it is something important these posts won’t have lots of screen grabs from Visual Studio with buttons to press highlighted, or details of how to use app.settings.json files etc.

In the beginning

The first step was creating a Visual Studio 2019 solution, adding an empty Web API project then adding an “API Controller with read/write actions.(most of which I have deleted).

using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace devMobile.WebAPIDapper.Lists.Controllers
{
	[ApiController]
	[Route("api/[controller]")]
	public class ReadWriteController : ControllerBase
	{
		// GET: api/<ReadWriteController>
		[HttpGet]
		public IEnumerable<string> Get()
		{
			return new string[] { "value1", "value2" };
		}

		// GET api/<ReadWriteController>/5
		[HttpGet("{id}")]
		public string Get(int id)
		{
			return "value";
		}

		// POST api/<ReadWriteController>
		[HttpPost]
		public void Post([FromBody] string value)
		{
		}

		// PUT api/<ReadWriteController>/5
		[HttpPut("{id}")]
		public void Put(int id, [FromBody] string value)
		{
		}

		// DELETE api/<ReadWriteController>/5
		[HttpDelete("{id}")]
		public void Delete(int id)
		{
		}
	}
}

Several of the existing codebases used ADO.Net so Dapper the lightweight ORM(NuGet) developed by the Stackoverflow team has been a good fit. The developers were comfortable with ADO.Net unlike EF which has a pretty steep learning curve especially when retrofitting it to an existing database.

Dapper in Nuget Package Manager

Microsoft samples always use the Adventure works, Northwind, Pet Store or World Wide Importers sample databases so for my code I’m using World Wide Importers. This was the simplest sample I could come up with, a controller retrieves a list of StockItems which are “automagically” mapped to StockItemListDto instances.

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;

using Dapper;

namespace devMobile.WebAPIDapper.SimpleList.Controllers
{
	public class StockItemListDto
	{
		public int Id { get; set; }
		public string Name { get; set; }
		public decimal RecommendedRetailPrice { get; set; }
		public decimal TaxRate { get; set; }
	}

	[Route("api/[controller]")]
	[ApiController]
	public class StockItemController : ControllerBase
	{
		private readonly string connectionString;

		public StockItemController(IConfiguration configuration)
		{
			this.connectionString = configuration.GetSection("ConnectionStrings").GetSection("WideWorldImportersDatabase").Value;
		}

		public IEnumerable<StockItemListDto> Get()
		{
			IEnumerable<StockItemListDto> response = null;

			using (SqlConnection db = new SqlConnection(this.connectionString))
			{
				response = db.Query<StockItemListDto>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]", commandType: CommandType.Text);
			}

			return response;
		}
	}
}

To keep the code as small and simple as practical I have used embedded SQL (I’ll cover stored procedures in depth in future posts), the request is synchronous, the “baked in” appsettings.json configuration file support is used, the Data Transfer Object(DTO) is included with the controller implementation, the names of the columns returned by the SQL query match the DTO properties, and there is no logging or error handling.

[{"id":1,"name":"USB missile launcher (Green)","recommendedRetailPrice":37.38,"taxRate":15.000,"validFrom":"2016-05-31T23:11:00"}, {"id":2,"name":"USB rocket launcher (Gray)","recommendedRetailPrice":37.38,"taxRate":15.000,"validFrom":"2016-05-31T23:11:00"},{"id":3,"name":"Office cube periscope (Black)","recommendedRetailPrice":27.66,"taxRate":15.000,"validFrom":"2016-05-31T23:00:00"},{"id":4,"name":"USB food flash drive - sushi roll","recommendedRetailPrice":47.84,"taxRate":15.000,"validFrom":"2016-05-31T23:11:00"},{"id":5,"name":"USB food flash drive - hamburger","recommendedRetailPrice":47.84,"taxRate":15.000,"validFrom":"2016-05-31T23:11:00"},{"id":6,"name":"USB food flash drive - hot dog","recommendedRetailPrice":47.84,"taxRate":15.000,"validFrom":"2016-05-31T23:11:00"},{"id":7,"name":"USB food flash drive - pizza slice","recommendedRetailPrice":47.84,"taxRate":15.000,"validFrom":"2016-05-31T23:11:00"},{"id":8,"name":"USB food flash drive - dim sum 10 drive variety pack","recommendedRetailPrice":358.80,"taxRate":15.000,"validFrom":"2016-05-31T23:11:00"},{"id":9,"name":"USB food flash drive - banana","recommendedRetailPrice":47.84,"taxRate":15.000,"validFrom":"2016-05-31T23:11:00"},
...
{"id":217,"name":"Void fill 200 L bag (White) 200L","recommendedRetailPrice":37.38,"taxRate":15.000,"validFrom":"2016-05-31T23:12:00"},{"id":218,"name":"Void fill 300 L bag (White) 300L","recommendedRetailPrice":56.06,"taxRate":15.000,"validFrom":"2016-05-31T23:12:00"},{"id":219,"name":"Void fill 400 L bag (White) 400L","recommendedRetailPrice":74.75,"taxRate":15.000,"validFrom":"2016-05-31T23:12:00"},{"id":220,"name":"Novelty chilli chocolates 250g","recommendedRetailPrice":12.23,"taxRate":10.000,"validFrom":"2016-05-31T23:00:00"},{"id":221,"name":"Novelty chilli chocolates 500g","recommendedRetailPrice":20.74,"taxRate":10.000,"validFrom":"2016-05-31T23:00:00"},{"id":222,"name":"Chocolate beetles 250g","recommendedRetailPrice":12.23,"taxRate":10.000,"validFrom":"2016-05-31T23:00:00"},{"id":223,"name":"Chocolate echidnas 250g","recommendedRetailPrice":12.23,"taxRate":10.000,"validFrom":"2016-05-31T23:00:00"},{"id":224,"name":"Chocolate frogs 250g","recommendedRetailPrice":12.23,"taxRate":10.000,"validFrom":"2016-05-31T23:00:00"},{"id":225,"name":"Chocolate sharks 250g","recommendedRetailPrice":12.23,"taxRate":10.000,"validFrom":"2016-05-31T23:00:00"},{"id":226,"name":"White chocolate snow balls 250g","recommendedRetailPrice":12.23,"taxRate":10.000,"validFrom":"2016-05-31T23:00:00"},{"id":227,"name":"White chocolate moon rocks 250g","recommendedRetailPrice":12.23,"taxRate":10.000,"validFrom":"2016-05-31T23:00:00"}]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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