Simplifying Dynamic SQL Queries with Dapper

Mishael Ogochukwu — Sun, 03 Dec 2023 20:44:48 +0000

Introduction

Dapper is an open-source, lightweight Object-Relational Mapping (ORM) library for .NET. Dapper simplifies building and executing SQL queries, offering a rich set of tools for seamless integration with databases.

In this article, we will delve into practical examples of using Dapper to interact with an SQLite database, highlighting its strengths. Additionally, we will introduce a valuable companion library, Dapper.SimpleSqlBuilder, designed to enhance the Dapper experience by streamlining the process of constructing dynamic SQL queries.

Getting Started with Dapper

Let us begin by examining a common scenario. Suppose you have a table named Users in an SQLite database, and you want to retrieve users with a UserTypeId of 4 and a Role of Admin. The following Dapper code accomplishes this:

using Dapper;
using Microsoft.Data.Sqlite;

var userTypeId = 4;
var role = "Admin";

var sql = @"
SELECT * FROM Users
WHERE UserTypeId = @userTypeId
AND Role = @role";

using var connection = new SqliteConnection("Data Source=database.db");
var users = connection.Query<User>(sql, new { userTypeId, role });

This concise code demonstrates the power and simplicity of Dapper for executing SQL queries and retrieving data from a database.

Introducing Dapper.SimpleSqlBuilder

What is Dapper.SimpleSqlBuilder?

Dapper.SimpleSqlBuilder is a library that enhances the Dapper experience by providing a simple, efficient, and fluent way to build both static and dynamic SQL queries. Leveraging string interpolation and fluent API, this library allows developers to construct safe and parameterized SQL queries with ease.

This is not a novel idea, as there have been articles written about this, and there are other libraries that do similar things. Still, I wanted to build something that was simple, easy to use, memory efficient, fast, safe and created parametrized SQL queries.

Key Features

Provides a simple and natural way to write SQL queries using string interpolation.
Chainable methods and fluent APIs for building SQL queries.
Supports parameter reuse in queries.
Dependency injection support.
Conditional methods for building dynamic SQL queries.
Performant and memory efficient. Performs similarly or better when compared to Dapper's SqlBuilder.

The library provides two builders for building SQL queries:

Builder — for building static, dynamic and complex SQL queries.
Fluent Builder — for building dynamic SQL queries using fluent API.

The Builder

Let us revisit the earlier scenario but this time using the Dapper.SimpleSqlBuilder's Builder.

using Dapper;
using Dapper.SimpleSqlBuilder;
using Microsoft.Data.Sqlite;

var userTypeId = 4;
var role = "Admin";

var builder = SimpleBuilder.Create($@"
SELECT * FROM Users
WHERE UserTypeId = {userTypeId}
AND Role = {role}");

using var connection = new SqliteConnection("Data Source=database.db");
var users = connection.Query<User>(builder.Sql, builder.Parameters);

I know some of you are already thinking, this is not safe, you are using string interpolation, and this can lead to SQL injection.

Your concerns are valid; however, the library mitigates this by capturing the values passed into the interpolated string and creating the parametrized SQL statement as seen below.

All values passed into the interpolated string are put into Dapper's DynamicParameters collection.

SELECT * FROM Users
WHERE UserTypeId = @p0
AND Role = @p1

Using the same scenario let us make the query dynamic.

using Dapper;
using Dapper.SimpleSqlBuilder;
using Microsoft.Data.Sqlite;

var users = GetUsers(null, "Admin");

IEnumerable<User> GetUsers(int? userTypeId = null, string role = null)
{

    var builder = SimpleBuilder.Create($"SELECT * FROM Users")
      .AppendNewLine(userTypeId.HasValue || !string.IsNullOrWhiteSpace(role), $"WHERE 1 = 1")
      .Append(userTypeId.HasValue, $"AND UserTypeId = {userTypeId}")
      .Append(!string.IsNullOrWhiteSpace(role), $"AND Role = {role}");

    using var connection = new SqliteConnection("Data Source=database.db");
    var users = connection.Query<User>(builder.Sql, builder.Parameters);
}

The generated SQL will be:

SELECT * FROM Users
WHERE 1 = 1 AND Role = @p0

You can check out the complete feature set of the Builder in the documentation.

Let's quickly talk about the SQL injection

We are all aware of the dangers of SQL injection, however, if you are not, I suggest you read up on it here.

So how does the library prevent this?

The library mitigates this by forcing you to write all your SQL queries using string interpolation, this is to ensure that values passed into the interpolated string are captured and parametrized. Due to this constraint, the code below won't compile.

// Scenario 1: Won't compile
var builder = SimpleBuilder.Create("SELECT * FROM User");

// Scenario 2: Won't compile
var sql = "SELECT * FROM User";
builder = SimpleBuilder.Create(sql);

// Scenario 3: Won't compile
builder = SimpleBuilder.Create(sql + " WHERE ROLE IS NOT NULL");

// Scenario 4: Won't compile
sql = $"SELECT * FROM User WHERE UserTypeId = {userTypeId}";
builder = SimpleBuilder.Create(sql);

// Scenario 5: Won't compile
builder = SimpleBuilder.Create(sql + $" AND Role = {role}");

The Fluent Builder

The Fluent Builder offers a more expressive way to build dynamic SQL queries with fluent APIs and supports SELECT, INSERT, UPDATE and DELETE operations.

We will only look at the Select operation to keep this article short and sweet. However, you can learn more about the other operations and features of the Fluent Builder in the documentation.

Select Builder

Using the same scenario as mentioned earlier, we will use the Fluent Builder to build our SQL query.

var userTypeId = 4;
var role = "Admin";

var builder = SimpleBuilder.CreateFluent()
    .Select($"*")
    .From($"Users")
    .Where($"UserTypeId = {userTypeId}")
    .Where($"Role = {role}");

using var connection = new SqliteConnection("Data Source=database.db");
var users = connection.Query<User>(builder.Sql, builder.Parameters);

The generated SQL will be:

SELECT *
FROM Users
WHERE UserTypeId = @p0 AND Role = @p1

Let us look at another example but this time we will make the SQL query dynamic.

var filter = new Filter { UserTypeId = null, Roles = new [] { "Admin", "User" }, IncludeUsersWithoutRole = true };
var users = GetUsers(filter);

IEnumerable<User> GetUsers(Filter? filter = null)
{
    var builder = SimpleBuilder.CreateFluent()
        .Select($"*")
        .From($"User")
        .Where(filter?.UserTypeId.HasValue == true, $"UserTypeId = {filter.UserTypeId}")
        .OrWhere(filter?.Roles?.Length > 0, $"Role IN {filter.Roles}")
        .OrWhere(filter?.IncludeUsersWithoutRole == true, $"Role IS NULL");

    using var connection = new SqliteConnection("Data Source=database.db");
    return connection.Query<User>(builder.Sql, builder.Parameters);
}

The generated SQL will be:

SELECT *
FROM Users
WHERE Role IN @pc0_ OR Role IS NULL

When the query is executed, Dapper will expand the parameter pc0_ into individual parameters (pc0_1, pc0_2, etc.) for each value in the collection.

The Select Builder also has support for:

Distinct, Joins, OrderBy, Having and GroupBy clauses.
Pagination: Limit, Offset and Fetch clauses.
Where Filters (Complex filter statements).

How does it compare to Dapper?

The code below shows how the library compares to Dapper and Dapper's SqlBuilder.

using var connection = new SqliteConnection("Data Source=database.db");

// Building and executing SQL query with Dapper

var sql = @"
SELECT * FROM Users
WHERE UserTypeId = @userTypeId 
AND Role = @role";

var users = connection.Query<User>(sql, new { userTypeId, role })

// Building and executing SQL query with Dapper's SqlBuilder

var sqlBuilder = new SqlBuilder()
    .Where("UserTypeId = @userTypeId", new { userTypeId })
    .Where("Role = @role", new { role });

var template = sqlBuilder.AddTemplate("SELECT * FROM Users /**where**/");
users = connection.Query<User>(template.RawSql, template.Parameters);

// Building and executing SQL query with the Builder (Dapper.SimpleSqlBuilder)

var builder = SimpleBuilder.Create($@"
SELECT * FROM Users
WHERE UserTypeId = {userTypeId}
AND Role = {role}");

users = connection.Query<User>(builder.Sql, builder.Parameters);

// Building and executing SQL query with the Fluent Builder (Dapper.SimpleSqlBuilder)

var fluentBuilder = SimpleBuilder.CreateFluent()
    .Select($"*")
    .From($"Users")
    .Where($"UserTypeId = {userTypeId}")
    .Where($"Role = {role}");

users = connection.Query<User>(fluentBuilder.Sql, fluentBuilder.Parameters);

As you can see, the library alleviates some of the ceremonies required when using Dapper and provides a simple and natural way of writing SQL queries.

Performance

Performance is always relative and depends on the scenario and other factors (e.g., hardware, OS, etc), however, the results below give a good indication of how the library performs.

The benchmark shows the performance of the Builder and Fluent Builder compared to Dapper's SqlBuilder for building queries only (this does not benchmark SQL execution).


BenchmarkDotNet v0.13.12, Windows 11 (10.0.26100.2454)
Intel Core i7-8750H CPU 2.20GHz (Coffee Lake), 1 CPU, 12 logical and 6 physical cores
.NET SDK 9.0.100
  [Host]     : .NET 9.0.0 (9.0.24.52809), X64 RyuJIT AVX2
  Job-AYCPZN : .NET 9.0.0 (9.0.24.52809), X64 RyuJIT AVX2
  Job-FCIXJG : .NET Framework 4.8.1 (4.8.9290.0), X64 RyuJIT VectorSize=256

Legends:
  Categories  : All categories of the corresponded method, class, and assembly
  Mean        : Arithmetic mean of all measurements
  Allocated   : Allocated memory per single operation (managed only, inclusive, 1KB = 1024B)
  1 μs        : 1 Microsecond (0.000001 sec)

Method	Runtime	Categories	Mean	Allocated
SqlBuilder (Dapper)	.NET 9.0	Simple query	1.614 us	2.93 KB
Builder	.NET 9.0	Simple query	1.222 us	4.5 KB
FluentBuilder	.NET 9.0	Simple query	1.390 us	4.59 KB
Builder (Reuse parameters)	.NET 9.0	Simple query	1.842 us	4.77 KB
FluentBuilder (Reuse parameters)	.NET 9.0	Simple query	1.987 us	4.86 KB

SqlBuilder (Dapper)	.NET Framework 4.8	Simple query	3.485 us	3.44 KB
Builder	.NET Framework 4.8	Simple query	4.378 us	5.32 KB
FluentBuilder	.NET Framework 4.8	Simple query	4.830 us	5.25 KB
Builder (Reuse parameters)	.NET Framework 4.8	Simple query	5.134 us	5.89 KB
FluentBuilder (Reuse parameters)	.NET Framework 4.8	Simple query	5.799 us	5.82 KB


SqlBuilder (Dapper)	.NET 9.0	Large query	27.432 μs	42.42 KB
Builder	.NET 9.0	Large query	16.493 μs	49.05 KB
FluentBuilder	.NET 9.0	Large query	18.964 μs	48.89 KB
Builder (Reuse parameters)	.NET 9.0	Large query	12.842 μs	29.41 KB
FluentBuilder (Reuse parameters)	.NET 9.0	Large query	14.713 μs	29.3 KB

SqlBuilder (Dapper)	.NET Framework 4.8	Large query	46.692 μs	53.32 KB
Builder	.NET Framework 4.8	Large query	58.544 μs	61.96 KB
FluentBuilder	.NET Framework 4.8	Large query	68.833 μs	68.43 KB
Builder (Reuse parameters)	.NET Framework 4.8	Large query	44.878 μs	37.13 KB
FluentBuilder (Reuse parameters)	.NET Framework 4.8	Large query	55.460 μs	43.63 KB

The benchmark results are valid at the time of writing. To view the latest benchmark results, refer to the benchmark page for more details.

Conclusion

I hope you enjoyed reading this article and learned something new. Dapper.SimpleSqlBuilder is a cool library (I would hope it is 😄) that meets a specific need and I hope you find it useful.

If you like the library, use it, share it, and give it a ⭐️ on GitHub. For any questions, comments, or feedback, please feel free to reach out to me on GitHub.

DEV Community: Mishael Ogochukwu

Simplifying Dynamic SQL Queries with Dapper

Introduction

Getting Started with Dapper

Introducing Dapper.SimpleSqlBuilder

What is Dapper.SimpleSqlBuilder?

Key Features

The Builder

Let's quickly talk about the SQL injection

The Fluent Builder

Select Builder

How does it compare to Dapper?

Performance

Conclusion