private void SingleFilterTest()
{
DapperSqlGenerator generator = this.MockGenerator();
SingleQueryFilter filter = new SingleQueryFilter();
filter.AddEqual("name", "evalue");
filter.AddGreaterOrEqual("gte", 1);
filter.AddGreater("gt", 0);
filter.AddLess("lt", 10000);
filter.AddLessOrEqual("lte", 999);
filter.AddNotEqual("null", null);
DynamicParameters parameters = new DynamicParameters();
generator.GenerateFilter <DapperEntityWithNoBool>(filter, parameters);
Assert.Equal(5, parameters.ParameterNames.Count()); // null 不作为参数。
}
private void CombinedFilterTest()
{
DapperSqlGenerator generator = this.MockGenerator();
SingleQueryFilter filter = new SingleQueryFilter();
filter.AddEqual("name", "evalue");
filter.AddGreaterOrEqual("gte", 1);
filter.AddGreater("gt", 0);
filter.AddLess("lt", 10000);
filter.AddLessOrEqual("lte", 999);
filter.AddNotEqual("null", null);
SingleQueryFilter filter2 = new SingleQueryFilter();
filter2.AddGreaterOrEqual("gte2", 1);
filter2.AddEqual("name2", "evalue");
filter2.AddGreater("gt2", 0);
filter2.AddLess("lt2", 10000);
filter2.AddLessOrEqual("lte2", 999);
filter2.AddNotEqual("null2", null);
SingleQueryFilter filter3 = new SingleQueryFilter();
filter3.AddEqual("name23", "evalue");
filter3.AddGreaterOrEqual("gte3", 1);
filter3.AddGreater("gt3", 0);
filter3.AddLess("lt3", 10000);
filter3.AddLessOrEqual("lte3", 999);
filter3.AddEqual("null3", null);
CombinedQueryFilter cf1 = new CombinedQueryFilter(filter2, filter3, BooleanClause.And);
CombinedQueryFilter cf2 = new CombinedQueryFilter(filter, cf1, BooleanClause.Or);
DynamicParameters parameters = new DynamicParameters();
generator.GenerateFilter <DapperEntityWithNoBool>(cf2, parameters);
Assert.Equal(15, parameters.ParameterNames.Count()); // null 不作为参数。
}
/// <summary>
/// 生成以 AND 连接的 不带 WHERE 关键字的 WHERE 语句
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="filter">查询条件</param>
/// <param name="fieldsInClause">in条件集合</param>
/// <param name="likeFilds">like条件集合</param>
/// <returns></returns>
public static string GenAndWhereSql <T>(this DapperSqlGenerator sqlgen, QueryFilter filter, Dictionary <string, List <object> > fieldsInClause, Dictionary <string, string> likeFilds, DynamicParameters parameters)
{
List <string> where = new List <string>();
if (filter != null)
{
var whilefilter = sqlgen.GenerateFilter <T>(filter, parameters);
if (!string.IsNullOrWhiteSpace(whilefilter))
{
where.Add(whilefilter);
}
}
if (!fieldsInClause.IsNullOrEmpty())
{
foreach (var item in fieldsInClause)
{
var inclause = sqlgen.GenerateInClause <T>(item.Key, item.Value, parameters);
if (!string.IsNullOrWhiteSpace(inclause))
{
where.Add(inclause);
}
}
}
if (!likeFilds.IsNullOrEmpty())
{
foreach (var item in likeFilds)
{
var likeclause = sqlgen.GenLikeClause(LikeRegion.Both, item.Key, item.Value, parameters);
if (!string.IsNullOrWhiteSpace(likeclause))
{
where.Add(likeclause);
}
}
}
return(where.ToArrayString(" AND "));
}