Dapper.SimpleSqlBuilder.DependencyInjection 3.3.1

Dapper Simple SQL Builder

A simple SQL builder for Dapper using string interpolation and fluent API for building dynamic SQL.

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.

The library provides a feature set for building and parametrizing SQL queries, however all of Dapper's features and quirks still apply for query parameters.

Packages

Dapper.SimpleSqlBuilder: A simple SQL builder for Dapper using string interpolation and fluent API.

Dapper.SimpleSqlBuilder.StrongName: A package that uses Dapper.StrongName.

Dapper.SimpleSqlBuilder.DependencyInjection: Dependency injection extension for Dapper.SimpleSqlBuilder.

Quick Start

Pick the Nuget package that best suits your needs and follow the instructions below.

Installation

The example below shows how to install the Dapper.SimpleSqlBuilder package.

Install via the NuGet Package Manager Console

Install-Package Dapper.SimpleSqlBuilder

Or via the .NET Core command line interface

dotnet add package Dapper.SimpleSqlBuilder

Usage

The library provides two builders for building SQL queries, which can be created via the static SimpleBuilder class.

• Builder - for building static, dynamic and complex SQL queries.
• Fluent Builder - for building SQL queries using fluent APIs.

The library also provides an alternative to static classes via dependency injection.

Create SQL query with 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);

To learn more about the builder, see the Builder section.

Create SQL query with the Fluent Builder

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

// Execute the query with Dapper
var users = dbConnection.Query<User>(builder.Sql, builder.Parameters);

To learn more about the fluent builder, see the Fluent Builder section.

Simple Builder Settings

To learn about configuring the simple builder, see the Configuring Simple Builder Settings section.

Builder

A simple builder for building static, dynamic and complex SQL queries.

Static SQL

using Dapper.SimpleSqlBuilder;

int userTypeId = 10;
int age = 25;

var builder = SimpleBuilder.Create($@"
SELECT * FROM User
WHERE UserTypeId = {userTypeId} AND AGE >= {age}");

The generated SQL will be.

SELECT * FROM User
WHERE UserTypeId = @p0 AND AGE >= @p1

For newer versions of C# you can also use raw string literals with string interpolation to build your SQL queries, instead of verbatim string literals. See the example below.

var builder = SimpleBuilder.Create($"""
SELECT * FROM User
WHERE UserTypeId = {userTypeId} AND AGE >= {age}
""");

Dynamic SQL

You can concatenate multiple interpolated strings to build your dynamic SQL.

var user = new User { TypeId = 10, Role = "Admin", Age = 20 };

var builder = SimpleBuilder.Create($"SELECT * FROM User");
builder += $" WHERE UserTypeId = {user.TypeId}";

if (user.Age is not null)
{
    builder += $" AND AGE >= {user.Age}"
}

The generated SQL will be.

SELECT * FROM User WHERE UserTypeId = @p0 AND AGE >= @p1

Builder Chaining

If you prefer an alternative to interpolated string concatenation, you can use the Append(...), AppendIntact(...) and AppendNewLine(...) methods, which can be chained.

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

You can also use it with conditional statements. The Append methods all have conditional overloads. This is useful when you want to append a statement only if a condition is met.

var builder = SimpleBuilder.Create()
    .AppendIntact($"SELECT * FROM User WHERE UserTypeId = {user.TypeId}")
    .Append(user.Age is not null, $"AND Age >= {user.Age}")
    .AppendNewLine($"ORDER BY Age ASC");

Note: The Append(...) method adds a space before the SQL text by default. You can use the AppendIntact(...) method if you don't want this behaviour.

INSERT, UPDATE and DELETE Statements

INSERT Statement

You can perform INSERT operations with the builder as seen in the example below.

var builder = SimpleBuilder.Create($@"
INSERT INTO User (Role, Age)
VALUES ({user.Role}, {user.Age}");

// Execute the query with Dapper
dbConnection.Execute(builder.Sql, builder.Parameters);

The generated SQL will be.

INSERT INTO User (Role, Age)
VALUES (@p0, @p1)

UPDATE Statement

You can perform UPDATE operations with the builder as seen in the example below.

var builder = SimpleBuilder.Create($@"
UPDATE User 
SET Role = {user.Role}
WHERE Id = {user.Id}");

The generated SQL will be.

UPDATE User
SET Role = @p0
WHERE Id = @p1

DELETE Statement

You can perform DELETE operations with the builder as seen in the example below.

var builder = SimpleBuilder.Create($"DELETE FROM User WHERE Id = {user.Id}");

The generated SQL will be.

DELETE FROM User WHERE Id = @p0

Stored Procedures

You can execute stored procedures with the builder as seen in the example below.

var builder = SimpleBuilder.Create($"UserResources.ProcessUserInformation")
    .AddParameter("UserRole", userRole)
    .AddParameter("UserAge", userAge)
    .AddParameter("UserId", dbType: DbType.Int32, direction: ParameterDirection.Output)
    .AddParameter("Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

// Execute the stored procedure with Dapper
dbConnection.Execute(builder.Sql, builder.Parameters, commandType: CommandType.StoredProcedure);

// Get the output and return values
int id = builder.GetValue<int>("UserId");
int result = builder.GetValue<int>("Result");

Fluent Builder

A fluent builder for building SQL queries via fluent APIs.

SELECT Builder

You can perform SELECT operations with the fluent builder as seen in the examples below.

Example 1: SELECT

int age = 20;
int userTypeId = 10;

var builder = SimpleBuilder.CreateFluent()
    .Select($"Name, Age, Role")
    .From($"User")
    .Where($"Age > {age}")
    .Where($"UserTypeId = {userTypeId}")
    .OrderBy($"Name ASC, Age DESC");

// The query can also be written as this
builder = SimpleBuilder.CreateFluent()
    .Select($"Name").Select($"Age").Select($"Role")
    .From($"User")
    .Where($"Age > {age}")
    .Where($"UserTypeId = {userTypeId}")
    .OrderBy($"Name ASC").OrderBy($"Age DESC");


// Execute the query with Dapper
var users = dbConnection.Query<User>(builder.Sql, builder.Parameters);

The generated SQL will be.

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

Example 2: SELECT DISTINCT

var builder = SimpleBuilder.CreateFluent()
    .SelectDistinct($"Name, Age, Role")
    .From($"User")
    .Where($"UserTypeId = {userTypeId}");

// The query can also be written as this
builder = SimpleBuilder.CreateFluent()
    .SelectDistinct($"Name").SelectDistinct($"Age").SelectDistinct($"Role")
    .From($"User")
    .Where($"UserTypeId = {userTypeId}");

The generated SQL will be.

SELECT DISTINCT Name, Age, Role
FROM User
WHERE UserTypeId = @p0

Example 3: SELECT

int age = 20;
string role = "Admin";

var builder = SimpleBuilder.CreateFluent()
    .Select($"Name, Age, Role")
    .From($"User u")
    .InnerJoin($"UserType ut ON u.UserTypeId = ut.Id")
    .RightJoin($"UserStatus us ON u.UserStatusId = us.Id")
    .LeftJoin($"UserAddress ua ON u.UserAddressId = ua.Id")
    .Where($"Age > {age}")
    .OrWhere($"Role = {role}")
    .OrderBy($"Age DESC");

The generated SQL will be.

SELECT Name, Age, Role
FROM User u
INNER JOIN UserType ut ON u.UserTypeId = ut.Id
RIGHT JOIN UserStatus us ON u.UserStatusId = us.Id
LEFT JOIN UserAddress ua ON u.UserAddressId = ua.Id
WHERE Age > @p0 OR Role = @p1
ORDER BY Age DESC

Example 4: SELECT

var roles = new[] { "Admin", "User" };
int minAge = 20;
int maxAge = 30;

var builder = SimpleBuilder.CreateFluent()
    .Select($"Role, Name, COUNT(Age) AS AgeCount")
    .From($"User")
    .Where($"Role IN {roles}")
    .GroupBy($"Role, Name")
    .Having($"Age >= {minAge}").Having($"Age < {maxAge}")
    .OrderBy($"Role ASC");

The generated SQL will be.

SELECT Role, Name, COUNT(Age) AS AgeCount
FROM User
WHERE Role IN @p0
GROUP BY Role, Name
HAVING Age >= @p1 AND Age < @p2
ORDER BY Role ASC

Pagination

The SELECT builder supports two popular ways of performing pagination. You should use the methods that are supported by your database.

Limit and Offset methods:

var builder = SimpleBuilder.CreateFluent()
    .Select($"Name, Age, Role")
    .From($"User")
    .OrderBy($"Name ASC")
    .Limit(10)
    .Offset(20);

The generated SQL will be.

SELECT Name, Age, Role
FROM User
ORDER BY Name ASC
LIMIT 10 OFFSET 20

OffsetRows and FetchNext methods:

var builder = SimpleBuilder.CreateFluent()
    .Select($"Name, Age, Role")
    .From($"User")
    .OrderBy($"Name ASC")
    .OffsetRows(20)
    .FetchNext(10);

The generated SQL will be.

SELECT Name, Age, Role
FROM User
ORDER BY Name ASC
OFFSET 20 ROWS 
FETCH NEXT 10 ROWS ONLY

INSERT Builder

You can perform INSERT operations with the fluent builder as seen in the examples below.

Example 1: INSERT

var user = new User { Id = 10, Name = "John" Role = "Admin", Age = 20 };

var builder = SimpleBuilder.CreateFluent()
    .InsertInto($"User")
    .Columns($"Id, Name, Role, Age")
    .Values($"{user.Id}, {user.Name}, {user.Role}, {user.Admin}");

// The query can also be written as this
builder = SimpleBuilder.CreateFluent()
   .InsertInto($"User")
   .Columns($"Id").Columns($"Role").Columns($"Age")
   .Values($"{user.Id}").Values($"{user.Name}").Values($"{user.Role}").Values($"{user.Admin}");

// Execute the query with Dapper
dbConnection.Execute(builder.Sql, builder.Parameters);

The generated SQL will be.

INSERT INTO User (Id, Role, Age)
VALUES (@p0, @p1, @p2)

Example 2: INSERT

var builder = SimpleBuilder.CreateFluent()
    .InsertInto($"User")
    .Values($"{user.Id}, {user.Name}, {user.Role}, {user.Admin}");

The generated SQL will be.

INSERT INTO User
VALUES (@p0, @p1, @p2)

UPDATE Builder

You can perform UPDATE operations with the fluent builder as seen in the example below.

var builder = SimpleBuilder.CreateFluent()
    .Update($"User")
    .Set($"Name = {user.Name}, Role = {user.Role}, Age = {user.Age}")
    .Where($"Id = {user.Id}");

// The query can also be written as below
builder = SimpleBuilder.CreateFluent()
    .Update($"User")
    .Set($"Name = {user.Name}").Set($"Role = {user.Role}").Set($"Age = {user.Age}")
    .Where($"Id = {user.Id}");

The generated SQL will be.

UPDATE User
SET Name = @p0, Role = @p1, Age = @p2
WHERE Id = @p3

DELETE Builder

You can perform DELETE operations with the fluent builder as seen in the example below.

int userTypeId = 10;
string role = "Admin";

var builder = SimpleBuilder.CreateFluent()
    .DeleteFrom($"User")
    .Where($"UserTypeId = {id}")
    .Where($"Role = {role}");

The generated SQL will be.

DELETE FROM User
WHERE UserTypeId = @p0 AND Role = @p1

Filters (Complex filter statements)

The fluent builder supports complex filters. This means that you can add WHERE, AND, and OR clauses with complex filter statements.

Example 1: Filters

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: Filters

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

The generated SQL will be.

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

Example 3: Filters

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

Conditional Methods (Clauses)

The fluent builder supports conditional methods (clauses). This is useful when you want to add a clause only if a condition is met. The Set(...), InnerJoin(...), RightJoin(...), LeftJoin(...), Where(...), OrWhere(...), WithFilter(...), WithOrFilter(...), GroupBy(...), Having(...) and OrderBy(...) methods all have conditional overloads.

Example 1: Conditional Methods

var user = new User { Id = 10, Name = "John" Role = null, GlobalId = "ed6da218-0883-4374-90b5-ceb009864de0" };

var builder = SimpleBuilder.CreateFluent()
    .Update($"User")
    .Set($"Name = {user.Name}")
    .Set(user.Role is not null, $"Role = {user.Role}")
    .Where($"Id = {user.Id}")
    .OrWhere(user.GlobalId is not null, $"GlobalId = {user.GlobalId}")
    .Where(user.Role is not null, $"Role = {user.Role}");

The generated SQL will be.

UPDATE User
SET Name = @p0
WHERE Id = @p1 OR GlobalId = @p2

Example 2: Conditional Methods

var user = new User { Id = 10, Name = "John" Role = null, Age = null, UserTypeId = 123 };

var builder = SimpleBuilder.CreateFluent()
    .Select($"*")
    .From($"User")
    .WhereFilter()
        .WithFilter(user.Role is not null, $"Role = {user.Role}")
        .WithFilter($"Name LIKE {user.Name + '%'}")
    .OrWhereFilter()
        .WithFilter(user.Age is not null, $"Age = {user.Age}")
        .WithOrFilter(user.UserTypeId > 0, $"UserTypeId = {user.UserTypeId}");

The generated SQL will be.

SELECT *
FROM User
WHERE (Name LIKE @p0) OR (UserTypeId = @p1)

Lower Case Clauses

The fluent builder supports using lower case clauses. This is applicable to the Delete, Insert, Update and Select fluent builders.

The example below shows how to use lower case clauses.

// Configuring globally. Can also be configured per fluent builder instance.
SimpleBuilderSettings.Configure(useLowerCaseClauses: true);

var builder = SimpleBuilder.CreateFluent()
    .Select($"Role, Name, COUNT(Age) AS AgeCount")
    .From($"User")
    .Where($"Role IN {roles}")
    .OrWhere($"Role IS NULL")
    .GroupBy($"Role, Name")
    .Having($"Age >= {minAge}").Having($"Age < {maxAge}")
    .OrderBy($"Role ASC");

The generated SQL will be.

select Role, Name, COUNT(Age) AS AgeCount
from User
where Role IN (@p0) or Role IS NULL
group by Role, Name
having Age >= @p1 and Age < @p2
order by Role ASC

Formattable Strings

The library supports passing formattable strings to the builders. This is useful for scenarios such as subqueries, and breaking complex queries into smaller ones.

int userTypeId = 10;
FormattableString subQuery = $"SELECT Description from UserType WHERE Id = {userTypeId}";

var builder = SimpleBuilder.Create($@"
SELECT x.*, ({subQuery}) AS Description
FROM User x
WHERE UserTypeId = {userTypeId}");

The generated SQL will be.

SELECT x.*, (SELECT Description from UserType WHERE Id = @p0) AS Description
FROM User x
WHERE UserTypeId = @p1;

Parameter Properties

The library enables you to configure parameter properties via the AddParameter(...) method. For example, you may want to define a DbType for a parameter, and the code below is how you will do this.

var builder = SimpleBuilder.Create($"SELECT * FROM User Where Id = @id")
    .AddParameter("id", value: user.Id, dbType: DbType.Int64);

However, the library also provides an extension method to easily achieve this while using interpolated strings.

using Dapper.SimpleSqlBuilder.Extensions;

// Define parameter properties
var idParam = user.Id.DefineParam(dbType: DbType.Int64);
var builder = SimpleBuilder.Create($"SELECT * FROM User Where Id = {idParam}");

// OR

// Defining parameter properties inline
var builder = SimpleBuilder.CreateFluent()
    .Select($"*")
    .From($"User")
    .Where($"Id = {user.Id.DefineParam(dbType: DbType.Int64)}");

The DefineParam(...) extension method enables you to define the DbType, Size, Precision and Scale of your parameter. This should only be used for parameters passed into the interpolated string, as the parameter direction is always set to Input for values in the interpolated string.

As an alternative to the extension method you can manually create the parameter object.

var idParam = new SimpleParameterInfo(dbType: DbType.Int64);

Configuring Simple Builder Settings

You can configure the simple builder settings through the SimpleBuilderSettings static class by calling the Configure(...) method. However, if you are using the dependency injection library refer to the Dependency Injection section on how to configure the global simple builder settings.

The code below shows how to do this.

SimpleBuilderSettings.Configure
(
    parameterNameTemplate: "param", // Optional. Default is "p"
    parameterPrefix: ":", // Optional. Default is "@"
    reuseParameters: true // Optional. Default is "false"
    useLowerCaseClauses: true // Optional. Default is "false". This is only applicable to the fluent builder.
);

Configuring Parameter Name Template

The default parameter name template is p, meaning when parameters are created they will be named p0 p1 p2 ... You can configure this by passing your desired value to the parameterNameTemplate parameter.

SimpleBuilderSettings.Configure(parameterNameTemplate: "param");

Configuring Parameter Prefix

The default parameter prefix is @, meaning when parameters are passed to the database they will be passed as @p0 @p1 @p2 ..., however this may not be applicable to all databases. You can configure this by passing your desired value to the parameterPrefix parameter.

SimpleBuilderSettings.Configure(parameterPrefix: ":");

This can also be configured per simple builder instance if you want to override the global settings.

// Builder
var builder = SimpleBuilder.Create(parameterPrefix: ":");

// Fluent builder
var builder = SimpleBuilder.CreateFluent(parameterPrefix: ":");

Configuring Parameter Reuse

The library supports parameter reuse, and the default is false. Go to the Reusing Parameters section to learn more. You can configure this by passing your desired value to the reuseParameters parameter.

SimpleBuilderSettings.Configure(reuseParameters: true);

This can also be configured per simple builder instance if you want to override the global settings.

// Builder
var builder = SimpleBuilder.Create(reuseParameters: true);

// Fluent builder
var builder = SimpleBuilder.CreateFluent(reuseParameters: true);

Configuring Fluent builder to use Lower Case Clauses

The library supports using lower case clauses for the fluent builder, and the default is false. You can configure this by passing your desired value to the useLowerCaseClauses parameter.

SimpleBuilderSettings.Configure(useLowerCaseClauses: true);

This can also be configured per fluent builder instance if you want to override the global settings.

var builder = SimpleBuilder.CreateFluent(useLowerCaseClauses: true);

Reusing Parameters

The library supports reusing the same parameter name for parameters with the same value, type, and properties. This is turned off by default, however can be enabled globally via the simple builder settings or per simple builder instance.

Note: Parameter reuse does not apply to null values.

The example below shows how.

// Configuring globally. Can also be configured per simple builder instance.
SimpleBuilderSettings.Configure(reuseParameters: true);

int maxAge = 30;
int userTypeId = 10;

var builder = SimpleBuilder.Create($@"
SELECT x.*, (SELECT Description from UserType WHERE Id = {userTypeId}) AS Description
FROM User x
WHERE UserTypeId = {userTypeId}
AND Age <= {maxAge}");

The generated SQL will be.

SELECT x.*, (SELECT Description from UserType WHERE Id = @p0) AS Description
FROM User x
WHERE UserTypeId = @p0
AND Age <= @p1"

Raw values (:raw)

There are scenarios where you may want to pass a raw value into the interpolated string and not parameterize the value. The raw format string is used to indicate that the value should not be parameterized.

Note: Do not use raw values if you don't trust the source or have not sanitized your value, as this can lead to SQL injection.

Example 1: Dynamic Data Retrieval

var tableData = GetTableData("User", DateTime.Now);

IEnumerable<dynamic> GetTableData(string tableName, DateTime createDate)
{
    var builder = SimpleBuilder.Create($"SELECT * FROM {tableName:raw} WHERE CreatedDate = {createDate}");
    return dbConnection.Query(builder.Sql, builder.Parameters);
}

The generated SQL will be.

SELECT * FROM User WHERE CreatedDate = @p0

Example 2 : Column and table names with nameof()

var builder = SimpleBuilder.CreateFluent()
    .Select($"{nameof(User.Id):raw}, {nameof(User.Name):raw}, {nameof(User.Age):raw}")
    .From($"{nameof(User):raw}");

The generated SQL will be.

SELECT Id, Name, Age
FROM User

Dependency Injection

An alternative to using the static classes to access the simple builder and settings is via dependency injection. Use the Dapper.SimpleSqlBuilder.DependencyInjection nuget package instead of the default package. The library supports the default dependency injection pattern in .Net Core.

using Dapper.SimpleSqlBuilder.DependencyInjection;

services.AddSimpleSqlBuilder();

Usage in a class.

class MyClass
{
    private readonly simpleBuilder;

    public MyClass(ISimpleBuilder simpleBuilder)
    {
        this.simpleBuilder = simpleBuilder;
    }

    public void MyMethod()
    {
        int id = 10;
        var builder = simpleBuilder.Create($"SELECT * FROM User WHERE ID = {id}");

        // Other code below .....
    }

    public void MyMethod2()
    {
        int id = 10;
        var builder = simpleBuilder.CreateFluent()
            .Select($"*")
            .From($"User")
            .Where($"ID = {id}");

        // Other code below .....
    }
}

Configuring Simple Builder Options

You can configure the simple builder settings and the ISimpleBuilder instance service lifetime. The various methods are described below.

Configuring Simple Builder Settings via appsettings.json

{
  "SimpleSqlBuilder": {
    "DatabaseParameterNameTemplate": "p",
    "DatabaseParameterPrefix": "@",
    "ReuseParameters": false,
    "UseLowerCaseClauses": false
  }
}

services.AddSimpleSqlBuilder(
    // Optional. Default is ServiceLifetime.Singleton
    serviceLifeTime = ServiceLifetime.Singleton);

Configuring Simple Builder Settings via code

services.AddSimpleSqlBuilder(
    configure =>
    {
        configure.DatabaseParameterNameTemplate = "param"; // Optional. Default is "p"
        configure.DatabaseParameterPrefix = ":"; // Optional. Default is "@"
        configure.ReuseParameters = true; // Optional. Default is "false"
        configure.UseLowerCaseClauses = true; // Optional. Default is "false". This is only applicable to the fluent builder
    },
    // Optional. Default is ServiceLifetime.Singleton
    serviceLifeTime = ServiceLifetime.Scoped);

Database Support

The library supports any database that Dapper supports. However, the library has been tested against the latest versions of MSSQL, MySQL and PostgreSQL databases. The integration test can be found here SimpleSqlBuilder.IntegrationTests.

Benchmark

The benchmark below 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

Refer to the benchmark project for more information.

Contributing

Refer to the Contributing guide for more details.

License

This project is licensed under the MIT License. See the LICENSE file for details.

Acknowledgements

• This project was inspired by these amazing libraries. DapperQueryBuilder and Dapper SqlBuilder.
• Thanks to JetBrians for their open source development support.