Third Party Library - Dapper Simple SQL Builder (A simple SQL builder for Dapper using string interpolation)

Hi Dapper team,

Your library has been a gift to many of us and is loved by the community.

I just wanted to share a library I wrote for Dapper, that is an alternative to Dapper.SqlBuilder.

Dapper Simple SQL Builder

https://github.com/mishael-o/Dapper.SimpleSqlBuilder

This library provides a simple and easy way to build dynamic SQL and commands, that can be executed using the Dapper library. This is achieved by leveraging FormattableString and interpolated string handlers to capture parameters and produce parameterized SQL.

Some key features:

• Provides a natural way for building SQL queries
• Is as performant as SqlBuilder, in the creation of SQL queries. benchmark results here
• Enables parameter reuse
• Provides chained methods and fluent API for building queries
• Supports dependency injection

Example Usage

The library provides two simple builders, a Builder for manually building SQL queries which provides a lot of flexibility and a FluentBuilder for building queries using fluent API, which supports DELETE, INSERT, SELECT and UPDATE operations.

The Builder

using Dapper.SimpleSqlBuilder;

string name = "John";

var builder = SimpleBuilder.Create($@"
SELECT * FROM User
WHERE Name = {name}");

The concern you might have here is the issue of SQL injection, however this is mitigated by the library as the SQL statement is converted to this.

SELECT * FROM User
WHERE Name = @p0

And all values passed into the interpolated string are taken out and replaced with parameter placeholders. The parameter values are put into Dapper's DynamicParameters collection.

To execute the query with Dapper is as simple as this.

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

The Builder also supports chained methods

var builder = SimpleBuilder.Create($"SELECT * FROM User")
    .AppendNewLine($"WHERE UserTypeId = {id}")
    .Append($"AND Age >= {age}")
    .AppendNewLine($"ORDER BY Age ASC");

The generated SQL will be.

SELECT * FROM User
WHERE UserTypeId = @p0 AND Age >= @p1
ORDER BY Name ASC

The FluentBuilder

The sample below shows a SELECT operation.

using Dapper.SimpleSqlBuilder;

var roles = new[] { "Admin", "User" };
int age = 25;

var builder = SimpleBuilder.CreateFluent()
    .Select($"*")
    .From($"User")
    .Where($"Role IN {roles}")
    .Where($"Age >= {age}");

The generated SQL will be.

SELECT *
FROM User
WHERE Role IN @p0 AND Age >= @p1

The FluentBuilder also supports complex filters.

This means that you can add WHERE, AND, and OR clauses with complex filter statements.

Example 1:

var builder = SimpleBuilder.CreateFluent()
    .Select($"Name, Age, Role")
    .From($"User")
    .WhereFilter($"Age >= {minAge}").WithFilter($"Age < {maxAge}")
    .Where($"UserTypeId = {userTypeId}")
    .WhereFilter($"Role = {adminRole}").WithOrFilter($"Role = {userRole}").WithOrFilter($"Role IS NULL");
    

The generated SQL will be.

SELECT Name, Age, Role
FROM User
WHERE (Age >= @p0 AND Age < @p1) AND UserTypeId = @p2 AND (Role = @p3 OR Role = @p4 OR Role IS NULL)

Example 2:

var builder = SimpleBuilder.CreateFluent()
    .Select($"Name, Age, Role")
    .From($"User")
    .WhereFilter($"Role = {adminRole}").WithOrFilter($"Role = {userRole}")
    .OrWhereFilter($"Age >= {minAge}").WithFilter($"Age < {maxAge}")
    .OrWhere($"UserTypeId = {userTypeId}");

The generated SQL will be.

SELECT Name, Age, Role
FROM User
WHERE (Role = @p0 OR Role = @p1) OR (Age >= @p2 AND Age < @p3) OR UserTypeId = @p4

The issue is an announcement linking to Dapper.SimpleSqlBuilder and does not identify a Dapper file, test, or requested change. Read the linked library and the Dapper.SqlBuilder reference only if a concrete integration or documentation task is later defined; completion criteria are currently unspecified.