MyGet Pre-release feed: https://www.myget.org/gallery/dapper
| Package | NuGet Stable | NuGet Pre-release | Downloads | MyGet |
|---|---|---|---|---|
| Dapper.SqlBuilder |
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);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 });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..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