Important Notice

DapperQueryBuilder was fully rewritten into a new library InterpolatedSql which is faster, much more extensible, and does not depend on Dapper (you can use our magic string interpolation with any ORM or Micro-ORM or even bare ADO.NET).
The Dapper-specific features (Dapper extensions and mapping of Dapper types) were moved into InterpolatedSql.Dapper, which is our "batteries-included library", and it still supports all databases supported by Dapper.

The current release of DapperQueryBuilder (2.x.x) is just a copy of InterpolatedSql.Dapper with some minor facades added for backward compatibility (e.g. you can still use the old DapperQueryBuilder namespace). For long-term support (new features and bug fixes) please consider moving to InterpolatedSql.Dapper, which will get more frequent updates.

Please consider starring :star: both repositories.

Dapper Query Builder

Dapper Query Builder using String Interpolation and Fluent API

We all love Dapper and how Dapper is a minimalist library.

This library is a tiny wrapper around Dapper to help manual building of dynamic SQL queries and commands. It's based on 2 fundamentals:

Fundamental 1: Parameters are passed using String Interpolation (but it's safe against SQL injection!)

By using interpolated strings we can pass parameters directly (embedded in the query) without having to use anonymous objects and without worrying about matching the property names with the SQL parameters. We can just build our queries with regular string interpolation and this library will automatically "parameterize" our interpolated objects (sql-injection safe).

With plain Dapper we would write a parameterized query like this:

string productName = "%Computer%";
int subCategoryId = 10;

// Note that the SQL parameter names (@productName and @subCategoryId)...
var products = cn
    .Query<Product>($@"
    SELECT * FROM Product
    WHERE
    Name LIKE @productName
    AND ProductSubcategoryID = @subCategoryId
    ORDER BY ProductId",
    new { productName, subCategoryId }); // ... must match the anonymous object

With Dapper Query Builder we can just embed variables inside the query:

string productName = "%Computer%";
int subCategoryId = 10;

var products = cn
    .QueryBuilder($@"
    SELECT * FROM Product
    WHERE
    Name LIKE {productName}
    AND ProductSubcategoryID = {subCategoryId}
    ORDER BY ProductId"
    ).Query<Product>();

When .Query<T>() is invoked QueryBuilder will basically invoke Dapper equivalent method (Query<T>()) and pass a fully parameterized query (without risk of SQL-injection) even though it looks like you're just building dynamic sql.

Dapper would receive a fully parameterized query, but without the risk of having mismatches in the names or number of parameters. Dapper would get this sql:

SELECT * FROM Product
WHERE
Name LIKE @p0
AND ProductSubcategoryID = @p1
ORDER BY ProductId

and these parameters: new { p0 = productName, p1 = subCategoryId }

Fundamental 2: Query and Parameters walk side-by-side

QueryBuilder basically wraps 2 things that should always stay together: the query which you're building, and the parameters which must go together with our query. This is a simple concept but it allows us to dynamically add new parameterized SQL clauses/conditions in a single statement.

This is how we would build a query with a variable number of conditions using plain Dapper:

var dynamicParams = new DynamicParameters();
string sql = "SELECT * FROM Product WHERE 1=1";
sql += " AND Name LIKE @productName"; 
dynamicParams.Add("productName", productName);
sql += " AND ProductSubcategoryID = @subCategoryId"; 
dynamicParams.Add("subCategoryId", subCategoryId);
var products = cn.Query<Product>(sql, dynamicParams);

With Dapper Query Builder the SQL statement and the associated Parameters are kept together, making it easy to append dynamic conditions:

var query = cn.QueryBuilder($"SELECT * FROM Product WHERE 1=1");
query += $"AND Name LIKE {productName}"; 
query += $"AND ProductSubcategoryID = {subCategoryId}"; 
var products = query.Query<Product>(); 

Our classes (QueryBuilder and CommandBuilder) wrap the SQL statement and the associated Parameters, and when we invoke the Query (or run the Command) the underlying statement and parameters are just passed to Dapper. So we don't have to keep statement and parameters separated and we don't have to manually use DynamicParameters.

Quickstart / NuGet Package

1. Install the NuGet package Dapper-QueryBuilder (don't forget the dash to get the right package!) or NuGet package DapperQueryBuilder.StrongName
2. Start using like this:

using DapperQueryBuilder;
// ...

cn = new SqlConnection(connectionString);

// Build your query with interpolated parameters
// which are automagically converted into safe SqlParameters
var products = cn.QueryBuilder($@"
    SELECT ProductId, Name, ListPrice, Weight
    FROM Product
    WHERE ListPrice <= {maxPrice}
    AND Weight <= {maxWeight}
    AND Name LIKE {search}
    ORDER BY ProductId").Query<Product>();

Or building dynamic conditions like this:

using DapperQueryBuilder;
// ...

cn = new SqlConnection(connectionString);

// Build initial query
var q = cn.QueryBuilder($@"
    SELECT ProductId, Name, ListPrice, Weight
    FROM Product
    WHERE 1=1");

// and dynamically append extra filters
q += $"AND ListPrice <= {maxPrice}";
q += $"AND Weight <= {maxWeight}";
q += $"AND Name LIKE {search}";
q += $"ORDER BY ProductId";

var products = q.Query<Product>();

Full Documentation and Features

Static Query

// Create a QueryBuilder with a static query.
// QueryBuilder will automatically convert interpolated parameters to Dapper parameters (injection-safe)
var q = cn.QueryBuilder($@"
    SELECT ProductId, Name, ListPrice, Weight FROM Product 
    WHERE ListPrice <= {maxPrice}
    ORDER BY ProductId");

// Query<T>() will automatically pass our query and injection-safe SqlParameters to Dapper
var products = q.Query<Product>();
// all other Dapper extensions are also available: QueryAsync, QueryMultiple, ExecuteScalar, etc..

So, basically you pass parameters as interpolated strings, but they are converted to safe SqlParameters.

This is our mojo :-)

Dynamic Query

One of the top reasons for dynamically building SQL statements is to dynamically append new filters (where statements).

// create a QueryBuilder with initial query
var q = cn.QueryBuilder($"SELECT ProductId, Name, ListPrice, Weight FROM Product WHERE 1=1");

// Dynamically append whatever statements you need, and QueryBuilder will automatically 
// convert interpolated parameters to Dapper parameters (injection-safe)
q += $"AND ListPrice <= {maxPrice}";
q += $"AND Weight <= {maxWeight}";
q += $"AND Name LIKE {search}";
q += $"ORDER BY ProductId";

var products = q.Query<Product>(); 

Static Command

var cmd = cn.CommandBuilder($"DELETE FROM Orders WHERE OrderId = {orderId};");
int deletedRows = cmd.Execute();

cn.CommandBuilder($@"
   INSERT INTO Product (ProductName, ProductSubCategoryId)
   VALUES ({productName}, {ProductSubcategoryID})
").Execute();

Command with Multiple statements

In a single roundtrip we can run multiple SQL commands:

var cmd = cn.CommandBuilder();
cmd += $"DELETE FROM Orders WHERE OrderId = {orderId}; ";
cmd += $"INSERT INTO Logs (Action, UserId, Description) VALUES ({action}, {orderId}, {description}); ";
cmd.Execute();

Dynamic Query with /**where**/ keyword

If you don't like the idea of using WHERE 1=1 (even though it doesn't hurt performance), you can use the special keyword /**where**/ that act as a placeholder to render dynamically-defined filters.

QueryBuilder maintains an internal list of filters (property called Filters) which keeps track of all filters you've added using .Where() method. Then, when QueryBuilder invokes Dapper and sends the underlying query it will search for the keyword /**where**/ in our query and if it exists it will replace it with the filters added (if any), combined using AND statements.

Example:

// We can write the query structure and use QueryBuilder to render the "where" filters (if any)
var q = cn.QueryBuilder($@"
    SELECT ProductId, Name, ListPrice, Weight
    FROM Product
    /**where**/
    ORDER BY ProductId
    ");
    
// You just pass the parameters as if it was an interpolated string, 
// and QueryBuilder will automatically convert them to Dapper parameters (injection-safe)
q.Where($"ListPrice <= {maxPrice}");
q.Where($"Weight <= {maxWeight}");
q.Where($"Name LIKE {search}");

// Query() will automatically render your query and replace /**where**/ keyword (if any filter was added)
var products = q.Query<Product>();

// In this case Dapper would get "WHERE ListPrice <= @p0 AND Weight <= @p1 AND Name LIKE @p2" and the associated values

When Dapper is invok