Dapper-QueryBuilder 1.2.9

.NET 5.0 .NET Standard 2.0 .NET Framework 4.6.1
There is a newer prerelease version of this package available.
See the version list below for details.
dotnet add package Dapper-QueryBuilder --version 1.2.9
NuGet\Install-Package Dapper-QueryBuilder -Version 1.2.9
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Dapper-QueryBuilder" Version="1.2.9" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Dapper-QueryBuilder --version 1.2.9
#r "nuget: Dapper-QueryBuilder, 1.2.9"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Dapper-QueryBuilder as a Cake Addin
#addin nuget:?package=Dapper-QueryBuilder&version=1.2.9

// Install Dapper-QueryBuilder as a Cake Tool
#tool nuget:?package=Dapper-QueryBuilder&version=1.2.9

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.

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

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 our query. This is a simple concept but it allows us to dynamically add new parameterized SQL clauses/conditions 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 += $"AND Name LIKE {productName}"; 
query += $"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.

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 invoked we replace the /**where**/ by WHERE <filter1> AND <filter2> AND <filter3...> (if any filter was added).

Dynamic Query with **filters** keyword

**filters** is exactly like **where**, but it's used if we already have other fixed conditions before:

var q = cn.QueryBuilder(@"SELECT ProductId, Name, ListPrice, Weight
    FROM Product
    WHERE Price>{minPrice} /**filters**/
    ORDER BY ProductId
    ");

When Dapper is invoked we replace the /**filters**/ by AND <filter1> AND <filter2...> (if any filter was added).

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 += $"WHERE c.[Name] IN {categories}";

Fluent API (Chained-methods)

var q = cn.FluentQueryBuilder()
	.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).

See full documentation here

Product Compatible and additional computed target framework versions.
.NET net5.0 is compatible.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed. 
.NET Core netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net461 is compatible.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (5)

Showing the top 5 NuGet packages that depend on Dapper-QueryBuilder:

Package Downloads
DapperChunk

感谢开源库 Dapper DapperQueryBuilder 1.Dapper Chunks 用于大数据处理 2.Dapper Sqlbuilder sql 构建器,仿照 开源库 FluentQueryBuilder 构建.(目前实现 mysql,mssql,sqlite), FluentQueryExtensions.Register注册 数据库类型与构建器的映射关系

ViraVenda.Core

Biblioteca core do aplicativo Vira Venda

Harbin.DataAccess

Data Access library based on Dapper, Dapper.FastCRUD, and DapperQueryBuilder. Implement Repositories (Generic Repository Pattern) and helps to manage connections to distributed databases and/or read-replicas.

Harbin.DataAccess.DapperFastCRUD

Data Access library based on Dapper, Dapper.FastCRUD, and DapperQueryBuilder. Implement Repositories (Generic Repository Pattern) and helps to manage connections to distributed databases and/or read-replicas.

Harbin.DataAccess.DapperSimpleCRUD

Data Access library based on Dapper, Dapper.SimpleCRUD, and DapperQueryBuilder. Implement Repositories (Generic Repository Pattern) and helps to manage connections to distributed databases and/or read-replicas.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
2.0.0-beta1 154 7/10/2023
1.2.9 74,352 1/7/2023
1.2.8 284 1/7/2023
1.2.7 75,451 7/31/2022
1.2.6 833 7/28/2022
1.2.5 41,356 5/9/2022
1.2.4 12,478 3/6/2022
1.2.3 3,171 2/22/2022
1.2.2 3,861 2/13/2022
1.2.1 453 2/13/2022
1.2.0 57,470 11/4/2021
1.1.1 5,275 10/24/2021
1.1.0 139,290 5/1/2021
1.0.9 3,658 4/20/2021
1.0.8 6,809 3/30/2021
1.0.7 46,375 3/6/2021
1.0.6 4,232 2/9/2021
1.0.5 13,548 12/25/2020
1.0.4 34,270 8/18/2020
1.0.3 579 8/6/2020
1.0.2 538 8/5/2020
1.0.1 496 8/3/2020
1.0.0 676 8/2/2020