public async Task <IEnumerable <FuncionarioResponse> > GetFuncionario(string startname, List <string> gender, List <string> level, decimal?minsalary, decimal?maxsalary)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from funcionarios ");
if (!string.IsNullOrEmpty(startname))
{
sql.AppendLine(string.Format(" {0} name like @startname", DapperFuncitions.ExistsWhere(sql.ToString())));
}
if (gender.Count > 0)
{
sql.AppendLine(string.Format(" {0} gender in @gender", DapperFuncitions.ExistsWhere(sql.ToString())));
}
if (level.Count > 0)
{
sql.AppendLine(string.Format(" {0} level in @level", DapperFuncitions.ExistsWhere(sql.ToString())));
}
if ((minsalary ?? 0m) > 0)
{
sql.AppendLine(string.Format(" {0} salary < @minsalary", DapperFuncitions.ExistsWhere(sql.ToString())));
}
if ((maxsalary ?? 0m) > 0)
{
sql.AppendLine(string.Format(" {0} salary > @maxsalary", DapperFuncitions.ExistsWhere(sql.ToString())));
}
using (var connection = new SqlConnection(_DBConfig.GetConnectionString()))
{
return(await connection.QueryAsync <FuncionarioResponse>(sql.ToString(), new { startname = startname.FillLikeOperator(), gender, level, minsalary, maxsalary }));
}
}
public void ExistsWhereWithOutValidInputSql()
{
string sql = "select any text no sql";
var ret = DapperFuncitions.ExistsWhere(sql);
Assert.Equal("", ret);
}
public void FillLikeOperatorWithNullValue()
{
var result = DapperFuncitions.FillLikeOperator(null);
result = result.Substring(1, result.Length - 1);
result = result.Substring(0, result.Length - 1);
Assert.Equal("", result);
}
public void FillLikeOperatorWithInvalidSymbols(string operators)
{
var result = DapperFuncitions.FillLikeOperator(operators);
result = result.Substring(1, result.Length - 1);
result = result.Substring(0, result.Length - 1);
var field = Regex.IsMatch(result, @"^[a-zA-Z0-9 ]+$") || result.Trim() == "";
Assert.True(field);
}
public void ExistsWhereWithNullorEmptyValue(string sql)
{
var ret = DapperFuncitions.ExistsWhere(sql);
Assert.NotNull(ret);
}
