Donut Functions - Hole in the middle - Higher Order Function in Dapper
I’m writing articles on developing a website broken link checker in C#. This is part of that series.
There is a intro article on Donut Functions using timers, which caused come controvosy (see comments at the bottom).
TL;DR - I don’t Use This Strategy
Dapper
For years I’ve been connecting to MSSQL static Utility helper method. Here is a recent ETL article
I like my functions to be short and it’s annoying to write duplicate code in each function eg
public static void Main()
{
var actors = GetActors();
foreach (var actor in actors) Console.WriteLine(actor);
}
public static IEnumerable<Actor> GetActors()
{
// this line is duplicated in each function
using var connection = GetOpenConnection();
return connection.Query<Actor>(
@"SELECT TOP 10 *
FROM Actors");
}
public static IDbConnection GetOpenConnection()
{
var connection = new SqlConnection(ConnectionString);
// Dont need as dapper will open
// connection.Open();
return connection;
}
public class Actor
{
public int actorid { get; set; }
public string name { get; set; }
public string sex { get; set; }
public override string ToString() => $"{actorid} {name} {sex}";
}
And I really should use async all the way up be using async these days.
Dapper Async
public static async Task Main()
{
var actors = await GetActors();
foreach (var actor in actors) Console.WriteLine(actor);
}
public static async Task<IEnumerable<Actor>> GetActors()
{
using var connection = GetOpenConnection();
return await connection.QueryAsync<Actor>(
@"SELECT TOP 10 *
FROM Actors");
}
// This could be async but would gain no advantage
// https://stackoverflow.com/a/46802157/26086
public static IDbConnection GetOpenConnection()
{
var connection = new SqlConnection(ConnectionString);
// don't need to connection.Open as Dapper will manage.
// https://stackoverflow.com/a/12629170/26086
return connection;
}
Non Async Donut
Here is a stripped down version. Similar to the orange book chapter 1
// Function
public static IEnumerable<Actor> GetActors()
// calling a static function
// passing a multi line lambda (essentially another function) for the WithConnection to run
=> WithConnection(conn =>
{
var result = conn.Query<Actor>(
@"SELECT TOP 10 *
FROM Actors");
return result;
});
// Wrapper returns a generic T eg IEnumerable<Actor>
// It takes as arguments: A Func takes an IDbConnection (which is what we make here) and returns a T of the same type
public static T WithConnection<T>(
Func<IDbConnection, T> func)
{
using var conn = new SqlConnection(ConnectionString);
conn.Open();
return func(conn);
}
Inject the connection string
Let’s pass through the connection string:
// Function
public static IEnumerable<Actor> GetActors(string connectionString)
=> WithConnection(connectionString, conn =>
{
var result = conn.Query<Actor>(
@"SELECT TOP 10 *
FROM Actors");
return result;
});
// Wrapper returns a generic T eg IEnumerable<Actor>
// It takes as arguments: A Func takes an IDbConnection (which is what we make here) and returns a T of the same type
public static T WithConnection<T>(
string connectionString,
Func<IDbConnection, T> func)
{
using var conn = new SqlConnection(connectionString);
conn.Open();
return func(conn);
}
Pure functions are nice with nothing hidden ie configuration. Which means that testing should be easier.
Async Donut
public static async Task Main()
{
Console.WriteLine("Experimenting with a HOF for db connection and using statements");
Console.WriteLine("so don't have code duplication");
var actors = await GetActorsAsync(ConnectionString);
foreach (var actor in actors) Console.WriteLine(actor);
}
// Function
public static async Task<IEnumerable<Actor>> GetActorsAsync(string connectionString)
=> await WithConnection(connectionString, async conn =>
{
var result = await conn.QueryAsync<Actor>(
@"SELECT TOP 10 *
FROM Actors");
return result;
});
// Wrapper returns a generic T eg IEnumerable<Actor>
// It takes as arguments: A Func takes an IDbConnection (which is what we make here) and returns a T of the same type
public static async Task<T> WithConnection<T>(
string connectionString,
Func<IDbConnection, Task<T>> func)
{
await using var conn = new SqlConnection(connectionString);
await conn.OpenAsync();
return await func(conn);
}
TardisBank uses a similar pattern.
For me this is a step too far, and I find that it tricky to reason about. Especially if I should be using async on the conn.Open:
Dapper difference between conn.OpenAsync and conn.Open
Exception handling and Fault Tolerance
Please see Dapper and Polly article from 29th Aug 2021.
In general any sort of SQL exception in my application should bubbble up to the top level exception handler as it will be exceptional, unless it is fault tolerance handling:
As I’m using SQL Azure, and sometimes it just wont be there (it does happen), I like to have automatic retries and use an excellent library called Polly
https://hyr.mn/dapper-and-polly/ has a good tutorial where he uses an extension method.
public static async Task<IEnumerable<Actor>> GetTop10ActorsWithRetry(string connectionString)
=> await WithConnection(connectionString, async x =>
{
// Using an extension method to call the polly retry code
var result = await x.QueryAsyncWithRetry<Actor>(
@"SELECT TOP 10 *
FROM Actors");
return result;
});
We can make the code more terse using our wrapper function. This means we don’t need the extension methods.
public static async Task<T> WithConnection<T>(
string connectionString,
Func<IDbConnection, Task<T>> func)
{
await using var conn = new SqlConnection(connectionString);
//return await func(conn);
return await DapperExtensions.RetryPolicy.ExecuteAsync(() => func(conn));
}
Miniprofiler
I’ve used Miniprofiler extensively to profile apps in production with the biggest usage being to find slow SQL queries. Here is a guide on setting it up and my MPActors source on Github using ASP.NET Core 3.1
Click on the sql and you’ll see the raw query
I always find it tricky to setup Miniprofiler so will leave this simple working example in the MPActors repo for my future self :-)
Microsoft.Data.SqlClient or System.Data.SqlClient. We are using the older System.Data.SqlClient
Conclusion
You can use Donut / Wrapper / Higher Order Functions in C# to connect with Dapper, however I’m choosing not to in my current project.. just because it adds a bit too much complexity for hardly any gain (IMO).
I’ve since written an article on using Dapper with Postgres where I use the non-HOF strategy.
Hope this helps :-)