Simplifying Dynamic SQL Queries with Dapper

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 @p0 OR Role IS NULL

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.5, OS=Windows 11 (10.0.22621.1778)
Intel Core i7-8750H CPU 2.20GHz (Coffee Lake), 1 CPU, 12 logical and 6 physical cores
.NET SDK=7.0.302
  [Host]     : .NET 7.0.5 (7.0.523.17405), X64 RyuJIT AVX2
  Job-UDVULW : .NET 7.0.5 (7.0.523.17405), X64 RyuJIT AVX2
  Job-ZBHUIE : .NET Framework 4.8.1 (4.8.9139.0), X64 RyuJIT VectorSize=256

Method	Runtime	Categories	Mean	Allocated
SqlBuilder (Dapper)	.NET 7.0	Simple query	1.865 μs	2.92 KB
Builder	.NET 7.0	Simple query	1.531 μs	4.43 KB
FluentBuilder	.NET 7.0	Simple query	2.001 μs	4.5 KB
Builder (Reuse parameters)	.NET 7.0	Simple query	2.195 μs	4.7 KB
FluentBuilder (Reuse parameters)	.NET 7.0	Simple query	2.755 μs	4.77 KB
SqlBuilder (Dapper)	.NET Framework 4.6.1	Simple query	3.237 μs	3.43 KB
Builder	.NET Framework 4.6.1	Simple query	3.821 μs	4.7 KB
FluentBuilder	.NET Framework 4.6.1	Simple query	4.493 μs	5.2 KB
Builder (Reuse parameters)	.NET Framework 4.6.1	Simple query	4.607 μs	5.27 KB
FluentBuilder (Reuse parameters)	.NET Framework 4.6.1	Simple query	5.260 μs	5.77 KB
SqlBuilder (Dapper)	.NET 7.0	Large query	28.193 μs	42.19 KB
Builder	.NET 7.0	Large query	21.475 μs	48.79 KB
FluentBuilder	.NET 7.0	Large query	26.700 μs	48.62 KB
Builder (Reuse parameters)	.NET 7.0	Large query	14.929 μs	29.34 KB
FluentBuilder (Reuse parameters)	.NET 7.0	Large query	20.039 μs	29.18 KB
SqlBuilder (Dapper)	.NET Framework 4.6.1	Large query	43.275 μs	53.1 KB
Builder	.NET Framework 4.6.1	Large query	52.571 μs	62.15 KB
FluentBuilder	.NET Framework 4.6.1	Large query	63.775 μs	68.61 KB
Builder (Reuse parameters)	.NET Framework 4.6.1	Large query	39.589 μs	37.42 KB
FluentBuilder (Reuse parameters)	.NET Framework 4.6.1	Large query	50.712 μs	43.87 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.