Click here to Skip to main content
15,940,246 members
Articles / Programming Languages / SQL

Simplifying Dynamic SQL Queries with Dapper

Rate me:
Please Sign up or sign in to vote.
5.00/5 (9 votes)
16 Apr 2024CPOL6 min read 30K   23   11
A look at a simple SQL builder for Dapper
This article discusses practical examples of using Dapper to interact with an SQLite database, and highlights its strengths. Additionally, a valuable companion library, Dapper.SimpleSqlBuilder, is introduced.

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:

C#
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.

C#
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.

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

Using the same scenario, let us make the query dynamic.

C#
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");
    return 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.

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.

C#
// 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.

C#
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.

C#
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.

C#
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 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.

History

  • 11th December, 2023: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
South Africa South Africa
Hi 👋🏾 there, I am a Software Engineer and an open-source contributor with expertise in the .NET ecosystem, Azure and mobile application development.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA18-Apr-24 19:46
professionalȘtefan-Mihai MOGA18-Apr-24 19:46 
GeneralRe: My vote of 5 Pin
Mishael Ogo20-Apr-24 4:01
Mishael Ogo20-Apr-24 4:01 
QuestionError in First Example Pin
Member 941710116-Apr-24 9:55
Member 941710116-Apr-24 9:55 
AnswerRe: Error in First Example Pin
Mishael Ogo16-Apr-24 21:57
Mishael Ogo16-Apr-24 21:57 
GeneralStored procedures instead Pin
Fly Gheorghe14-Apr-24 22:33
Fly Gheorghe14-Apr-24 22:33 
GeneralRe: Stored procedures instead Pin
Mishael Ogo15-Apr-24 8:42
Mishael Ogo15-Apr-24 8:42 
GeneralRe: Stored procedures instead Pin
Fly Gheorghe15-Apr-24 23:08
Fly Gheorghe15-Apr-24 23:08 
GeneralRe: Stored procedures instead Pin
Mishael Ogo16-Apr-24 0:50
Mishael Ogo16-Apr-24 0:50 
GeneralRe: Stored procedures instead Pin
Fly Gheorghe16-Apr-24 22:43
Fly Gheorghe16-Apr-24 22:43 
Questioncheck this out its awesome Pin
Sacha Barber11-Dec-23 6:09
Sacha Barber11-Dec-23 6:09 
AnswerRe: check this out its awesome Pin
Mishael Ogo12-Dec-23 8:28
Mishael Ogo12-Dec-23 8:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.