Simplifying Dynamic SQL Queries with Dapper
Tags: dotnet, csharp, dapper, sql
Published: 2023-12-03 20:44:48

## Introduction

[Dapper](https://github.com/DapperLib/Dapper) is an open-source, lightweight Object-Relational Mapping (ORM) library for [.NET](https://dotnet.microsoft.com/). 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](https://github.com/mishael-o/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:

```csharp
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](https://github.com/mishael-o/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](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/tokens/interpolated) 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](https://github.com/DapperLib/Dapper/tree/main/Dapper.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**.

```csharp
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](https://github.com/DapperLib/Dapper/blob/main/Dapper/DynamicParameters.cs) collection.

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

Using the same scenario let us make the query dynamic.

```csharp
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:

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

You can check out the complete feature set of the **Builder** in the [documentation](https://mishael-o.github.io/Dapper.SimpleSqlBuilder/docs/builders/builder.html).

### 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](https://owasp.org/www-community/attacks/SQL_Injection).

**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.

```csharp
// 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](https://mishael-o.github.io/Dapper.SimpleSqlBuilder/docs/builders/fluent-builder/fluent-builder.html).

#### Select Builder

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

```csharp
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:

```sql
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.

```csharp
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:

```sql
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.

```csharp
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**).

``` ini

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](https://mishael-o.github.io/Dapper.SimpleSqlBuilder/docs/miscellaneous/performance.html) 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](https://github.com/mishael-o/).