Dapper-QueryBuilder 1.0.2

Dapper Query Builder

Dapper Query Builder using String Interpolation and Fluent API

This library is a wrapper around Dapper mostly for helping building dynamic SQL queries and commands.

String Interpolation instead of manually using DynamicParameters

By using interpolated strings we can pass parameters to Dapper without having to worry about creating and managing DynamicParameters manually.
You can build your queries with interpolated strings, and this library will automatically "parametrize" your values.

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

The underlying query will be fully parametrized ([Name] LIKE @p0 AND [ProductSubcategoryID] = @p1), without risk of SQL-injection, even though it looks like you're just building dynamic sql.

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 your query.
This is a simple concept but it allows us to add new sql clauses (parametrized) in a single statement.

Let's say you're building a query with a variable number of conditions:

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

QueryBuilder will wrap both the Query and the Parameters, so that you can easily append new sql statements (and parameters) easily.
When you invoke Query, the underlying query and parameters are passed to Dapper.

Filters as First-class citizen

As shown above, you'll still write plain SQL, which is what we all love about Dapper.
Since the most common use case for dynamic clauses is adding WHERE parameters, the library offers WHERE filters as a special structure:

• You can add filters to QueryBuilder using .Where() method, those filters are saved internally
• When you send your query to Dapper, QueryBuilder will search for a /**where**/ statement in your query and will replace with the filters you defined.

So you can still write your queries on your own, and yet benefit from string interpolation (which is our mojo and charm) and from dynamically building a list of filters.

int maxPrice = 1000;
int maxWeight = 15;
string search = "%Mountain%";

var cn = new SqlConnection(connectionString);

// You can build the query manually and just use QueryBuilder to replace "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 build your query and replace your /**where**/ (if any filter was added)
var products = q.Query<Product>();	

Combining Filters

var q = cn.QueryBuilder(@"SELECT ProductId, Name, ListPrice, Weight
	FROM [Product]
	/**where**/
	ORDER BY ProductId
	");

q.Where(new Filters()
{
	new Filter($"[ListPrice] >= {minPrice}"),
	new Filter($"[ListPrice] <= {maxPrice}")
});
q.Where(new Filters(Filters.FiltersType.OR)
{
	new Filter($"[Weight] <= {maxWeight}"),
	new Filter($"[Name] LIKE {search}")
});

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

// Query() will automatically build your SQL query, and will replace your /**where**/ (if any filter was added)
// "WHERE ([ListPrice] >= @p0 AND [ListPrice] <= @p1) AND ([Weight] <= @p2 OR [Name] LIKE @p3)"
// it will also pass an underlying DynamicParameters object, with all parameters you passed using string interpolation 
// (@p0 as minPrice, @p1 as maxPrice, etc..)

Raw command building

If you don't like the "magic" of replacing /**where**/ filters, you can do everything on your own.

// start your basic query
var q = cn.QueryBuilder(@"SELECT ProductId, Name, ListPrice, Weight FROM [Product] WHERE 1=1");

// append whatever statements you need (.Append instead of .Where!)
q.Append($" AND [ListPrice] <= {maxPrice}");
q.Append($" AND [Weight] <= {maxWeight}");
q.Append($" AND [Name] LIKE {search}");
q.Append($" ORDER BY ProductId");

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

IN lists

Dapper allows us to use IN lists magically. And it also works with our string interpolation:

var q = cn.QueryBuilder($@"
	SELECT c.[Name] as [Category], sc.[Name] as [Subcategory], p.[Name], p.[ProductNumber]
	FROM [Product] p
	INNER JOIN [ProductSubcategory] sc ON p.[ProductSubcategoryID]=sc.[ProductSubcategoryID]
	INNER JOIN [ProductCategory] c ON sc.[ProductCategoryID]=c.[ProductCategoryID]");

var categories = new string[] { "Components", "Clothing", "Acessories" };
q.Append($"WHERE c.[Name] IN {categories}");

Fluent API (Chained-methods)

var q = cn.QueryBuilder()
	.Select($"ProductId")
	.Select($"Name")
	.Select($"ListPrice")
	.Select($"Weight")
	.From($"[Product]")
	.Where($"[ListPrice] <= {maxPrice}")
	.Where($"[Weight] <= {maxWeight}")
	.Where($"[Name] LIKE {search}")
	.OrderBy($"ProductId");
	
var products = q.Query<Product>();	

Building joins dynamically using Fluent API:

var categories = new string[] { "Components", "Clothing", "Acessories" };

var q = cn.QueryBuilder()
	.SelectDistinct($"c.[Name] as [Category], sc.[Name] as [Subcategory], p.[Name], p.[ProductNumber]")
	.From($"[Product] p")
	.From($"INNER JOIN [ProductSubcategory] sc ON p.[ProductSubcategoryID]=sc.[ProductSubcategoryID]")
	.From($"INNER JOIN [ProductCategory] c ON sc.[ProductCategoryID]=c.[ProductCategoryID]")
	.Where($"c.[Name] IN {categories}");

There are also chained-methods for adding GROUP BY, HAVING, ORDER BY, and paging (OFFSET x ROWS / FETCH NEXT x ROWS ONLY).

nameof() and raw strings

For those who like strongly typed queries, you can also use nameof expression, but you have to define format "raw" such that the string is preserved and it's not converted into a @parameter.

var q = cn.QueryBuilder($@"
    SELECT 
        c.[{nameof(Category.Name):raw}] as [Category], 
        sc.[{nameof(Subcategory.Name):raw}] as [Subcategory], 
        p.[{nameof(Product.Name):raw}], p.[ProductNumber]"
    FROM [Product] p
    INNER JOIN [ProductSubcategory] sc ON p.[ProductSubcategoryID]=sc.[ProductSubcategoryID]
    INNER JOIN [ProductCategory] c ON sc.[ProductCategoryID]=c.[ProductCategoryID]");

And in case you can use "find references", "rename" (refactor), etc.

Invoking Stored Procedures

// This is basically Dapper, but with a FluentAPI where you can append parameters dynamically.
var q = cn.CommandBuilder($"[HumanResources].[uspUpdateEmployeePersonalInfo]")
	.AddParameter("ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue)
	.AddParameter("ErrorLogID", dbType: DbType.Int32, direction: ParameterDirection.Output)
	.AddParameter("BusinessEntityID", businessEntityID)
	.AddParameter("NationalIDNumber", nationalIDNumber)
	.AddParameter("BirthDate", birthDate)
	.AddParameter("MaritalStatus", maritalStatus)
	.AddParameter("Gender", gender);
	
int affected = q.Execute(commandType: CommandType.StoredProcedure);
int returnValue = q.Parameters.Get<int>("ReturnValue");

See full documentation here