Dapper-QueryBuilder
2.0.0
dotnet add package Dapper-QueryBuilder --version 2.0.0
NuGet\Install-Package Dapper-QueryBuilder -Version 2.0.0
<PackageReference Include="Dapper-QueryBuilder" Version="2.0.0" />
paket add Dapper-QueryBuilder --version 2.0.0
#r "nuget: Dapper-QueryBuilder, 2.0.0"
// Install Dapper-QueryBuilder as a Cake Addin #addin nuget:?package=Dapper-QueryBuilder&version=2.0.0 // Install Dapper-QueryBuilder as a Cake Tool #tool nuget:?package=Dapper-QueryBuilder&version=2.0.0
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.SqlBuilder($"DELETE FROM Orders WHERE OrderId = {orderId};");
int deletedRows = cmd.Execute();
cn.SqlBuilder($@"
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.SqlBuilder();
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 | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 is compatible. net5.0-windows was computed. net6.0 is compatible. 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 is compatible. 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. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.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 was computed. net462 is compatible. net463 was computed. net47 was computed. net471 was computed. net472 is compatible. 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. |
-
.NETFramework 4.6.2
- Dapper (>= 2.0.123)
- InterpolatedSql (>= 2.3.0)
- Microsoft.CSharp (>= 4.7.0)
- System.ValueTuple (>= 4.5.0)
-
.NETFramework 4.7.2
- Dapper (>= 2.0.123)
- InterpolatedSql (>= 2.3.0)
- Microsoft.CSharp (>= 4.7.0)
- System.ValueTuple (>= 4.5.0)
-
.NETStandard 2.0
- Dapper (>= 2.0.123)
- InterpolatedSql (>= 2.3.0)
- Microsoft.CSharp (>= 4.7.0)
- System.ValueTuple (>= 4.5.0)
-
net5.0
- Dapper (>= 2.0.123)
- InterpolatedSql (>= 2.3.0)
- Microsoft.CSharp (>= 4.7.0)
- System.ValueTuple (>= 4.5.0)
-
net6.0
- Dapper (>= 2.0.123)
- InterpolatedSql (>= 2.3.0)
- Microsoft.CSharp (>= 4.7.0)
- System.ValueTuple (>= 4.5.0)
-
net7.0
- Dapper (>= 2.0.123)
- InterpolatedSql (>= 2.3.0)
- Microsoft.CSharp (>= 4.7.0)
- System.ValueTuple (>= 4.5.0)
NuGet packages (7)
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 |
|
Codibre.GrpcSqlProxy.Client
Package Description |
|
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. |
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated | |
---|---|---|---|
2.0.0 | 39,504 | 3/17/2024 | |
2.0.0-beta2 | 112 | 3/14/2024 | |
2.0.0-beta1 | 1,502 | 7/10/2023 | |
1.2.9 | 290,561 | 1/7/2023 | |
1.2.8 | 416 | 1/7/2023 | |
1.2.7 | 117,855 | 7/31/2022 | |
1.2.6 | 1,060 | 7/28/2022 | |
1.2.5 | 74,875 | 5/9/2022 | |
1.2.4 | 16,188 | 3/6/2022 | |
1.2.3 | 4,057 | 2/22/2022 | |
1.2.2 | 4,290 | 2/13/2022 | |
1.2.1 | 545 | 2/13/2022 | |
1.2.0 | 93,532 | 11/4/2021 | |
1.1.1 | 6,602 | 10/24/2021 | |
1.1.0 | 161,208 | 5/1/2021 | |
1.0.9 | 4,042 | 4/20/2021 | |
1.0.8 | 7,930 | 3/30/2021 | |
1.0.7 | 52,946 | 3/6/2021 | |
1.0.6 | 4,549 | 2/9/2021 | |
1.0.5 | 14,953 | 12/25/2020 | |
1.0.4 | 39,787 | 8/18/2020 | |
1.0.3 | 964 | 8/6/2020 | |
1.0.2 | 628 | 8/5/2020 | |
1.0.1 | 587 | 8/3/2020 | |
1.0.0 | 2,011 | 8/2/2020 |