Table of Contents

Quick Start

A simple SQL builder for Dapper, using string interpolation and a fluent API to build safe, static, and dynamic SQL queries.

Installation

The example below shows how to install the Dapper.SimpleSqlBuilder package. To install other packages, see the Packages section.

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 a fluent API.

The library also provides an alternative to static classes via Dependency Injection.

Create SQL query with the Builder

using Dapper.SimpleSqlBuilder;

var userTypeId = 4;
var role = "Admin";

var builder = SimpleBuilder.Create($@"
SELECT * FROM User
WHERE UserTypeId = {userTypeId} AND Role = {role}");
Note

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 Id = @p0 AND Role = @p1

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);

See the Builder section for more information.

Create SQL query with the Fluent Builder

using Dapper.SimpleSqlBuilder;

var userTypeId = 4;
var roles = new[] { "Admin", "User" };

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

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

The generated SQL will be:

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

See the Fluent Builder section for more information.

Builder Settings

See the Builder Settings section to learn about configuring the builders.

Next Steps

Edit this page