ContextQMD
Back to Dapper

Readme

Dapper.SqlBuilder/Readme.md

2.1.725.0 KB
Original Source

Dapper.SqlBuilder - a simple sql formatter for .Net

Packages

MyGet Pre-release feed: https://www.myget.org/gallery/dapper

PackageNuGet StableNuGet Pre-releaseDownloadsMyGet
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:

csharp
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:

csharp
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

csharp
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

csharp
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

Combining the Where and OrWhere methods

The OrWhere method currently groups all and and or clauses by type, then join the groups with and or or depending on the first call. This may result in possibly unexpected outcomes. See also issue 647.

Example Where first

When providing the following clauses

csharp
sql.Where("a = @a1");
sql.OrWhere("b = @b1");
sql.Where("a = @a2");
sql.OrWhere("b = @b2");

SqlBuilder will generate sql

sql
a = @a1 AND a = @a2 AND ( b = @b1 OR b = @b2 )

and not say

sql
a = @a1 OR b = @b1 AND a = @a2 OR b = @b2

Example OrWhere first

When providing the following clauses

csharp
sql.OrWhere("b = @b1");
sql.Where("a = @a1");
sql.OrWhere("b = @b2");
sql.Where("a = @a2");

SqlBuilder will generate sql

sql
a = @a1 OR a = @a2 OR ( b = @b1 OR b = @b2 )