mirror of
https://github.com/DapperLib/Dapper.git
synced 2026-10-16 21:03:18 +08:00
Marc Gravell
aecffecae1
* attempt NRT makeover - annotate NRTs on GridReader API - add protected access to some of the GridReader internals - switch GridReader callbacks to be more type-independent docs lib updates; deal with yellow warnings and test failures (SQL server certs and cancellation surfacing differently) fix break simplify proposed GridReader changes to protected OnBeforeGrid and OnAfterGrid[Async] Builds: Bump library versions (#1935) Tests: Upgrade dependencies for Dependabot (#1936) I'll do a pass at all of these later, but getting CVE versions out of the pipe. Resolving 4 alerts here: https://github.com/DapperLib/Dapper/security/dependabot merge and lib updates allow Identity to be constructed on-the-fly inside GridReader fix build warnings include readme rev minor - enable [SkipLocalsInit] everywhere - use NET5_0_OR_GREATER for remoting check # Conflicts: # Dapper/CommandDefinition.cs # Dapper/DefaultTypeMap.cs # Dapper/SqlMapper.Async.cs # Dapper/SqlMapper.IDataReader.cs # Dapper/SqlMapper.Link.cs # Dapper/SqlMapper.cs # Directory.Build.props * shipped, not unshipped * fixup SqlBuilder; use is null / is not null * use central package management (#1949) * Nullable test tweaks * Nuget: let's just remove it! * 2 test fixes from review * fix FindExplicitConstructor * add GetPropertySetterOrThrow * fix NRT on FindConstructor * DapperRow: value is object? * use NotNullWhen * make constructor problem more obvious * test fixes --------- Co-authored-by: Nick Craver <nrcraver@gmail.com>
Dapper.SqlBuilder - a simple sql formatter for .Net
Packages
MyGet Pre-release feed: https://www.myget.org/gallery/dapper
| Package | NuGet Stable | NuGet Pre-release | Downloads | MyGet |
|---|---|---|---|---|
| Dapper.SqlBuilder |  |
 |
 |
 |
Features
Dapper.SqlBuilder contains a number of helper methods for generating sql.
The list of extension methods in Dapper.SqlBuilder right now are:
SqlBuilder AddParameters(dynamic parameters);
SqlBuilder Select(string sql, dynamic parameters = null);
SqlBuilder Where(string sql, dynamic parameters = null);
SqlBuilder OrWhere(string sql, dynamic parameters = null);
SqlBuilder OrderBy(string sql, dynamic parameters = null);
SqlBuilder GroupBy(string sql, dynamic parameters = null);
SqlBuilder Having(string sql, dynamic parameters = null);
SqlBuilder Set(string sql, dynamic parameters = null);
SqlBuilder Join(string sql, dynamic parameters = null);
SqlBuilder InnerJoin(string sql, dynamic parameters = null);
SqlBuilder LeftJoin(string sql, dynamic parameters = null);
SqlBuilder RightJoin(string sql, dynamic parameters = null);
SqlBuilder Intersect(string sql, dynamic parameters = null);
Template
SqlBuilder allows you to generate N SQL templates from a composed query, it can easily format sql when you are attaching parameters and how, e.g:
var builder = new SqlBuilder()
.Where("a = @a", new { a = 1 })
.Where("b = @b", new { b = 2 })
.OrderBy("a")
.OrderBy("b");
var counter = builder.AddTemplate("select count(*) from table /**where**/");
var selector = builder.AddTemplate("select * from table /**where**/ /**orderby**/");
var count = cnn.Query(counter.RawSql, counter.Parameters).Single();
var rows = cnn.Query(selector.RawSql, selector.Parameters);
it's same as
var count = cnn.Query("select count(*) from table where a = @a and b = @b", new { a = 1, b = 1 });
var rows = cnn.Query("select * from table where a = @a and b = @b order by a, b", new { a = 1, b = 1 });
Dynamic Filter Paging Example
var builder = new SqlBuilder();
var selectTemplate = builder.AddTemplate(@"select X.* from (
select us.*, ROW_NUMBER() OVER (/**orderby**/) AS RowNumber
from Users us
/**where**/
) as X
where RowNumber between @start and @finish", new { start, finish });
var countTemplate = builder.AddTemplate(@"select count(*) from Users /**where**/");
if (userId.HasValue())
builder.Where($"t.userId = @{nameof(userId)}", new { userId });
if (isCancel)
builder.Where($"t.isCancel = @{nameof(isCancel)}", new { isCancel });
builder.OrderBy(string.Format("t.id {0}", orderDesc ? "desc" : "asc"));
var users = conn.Query<User>(selectTemplate.RawSql, selectTemplate.Parameters);
var count = conn.ExecuteScalar<int>(countTemplate.RawSql, countTemplate.Parameters);
//..etc..
Limitations and caveats
OrWhere use and not or to concat sql problem
sql.Where("a = @a1");
sql.OrWhere("b = @b1");
sql.Where("a = @a2");
sql.OrWhere("b = @b2");
SqlBuilder will generate sql
a = @a1 AND b = @b1 AND a = @a2 AND b = @b2
not
a = @a1 OR b = @b1 AND a = @a2 OR b = @b2