protected override SearchTeacherResponse ProcessOperation(SearchTeacherRequest request)
{
SearchTeacherResponse response = new SearchTeacherResponse();
TeacherFilter teacherFilter = this._mappingResolver.BuildFrom(request.Data);
PagedResult <Teacher> teachers = this._teacherRepository.ListPagedByFilter(teacherFilter, request.PageNumber, request.PageSize);
response.Data = this._mappingResolver.BuildFrom(teachers.Results);
return(response);
}
public PagedResult <Teacher> ListPagedByFilter(TeacherFilter filter, int pageNumber, int pageSize)
{
//Usar o var para passar o parâmetro implicitamente
//var parameters = new DynamicParameters();
DynamicParameters parameters = new DynamicParameters();
parameters.AddDynamicParams(new { Filter = filter, PageNumber = pageNumber, PageSize = pageSize });
const string queryCount = @"Select
COUNT(TeacherId)
FROM
dbo.Teacher
@DynamicFilter;
";
const string queryFilter = @"
SELECT
TeacherId,
Name,
Gender,
LevelId,
Salary,
AdmitionDate
FROM
dbo.Teacher
@DynamicFilter
";
StringBuilder sqlString = new StringBuilder(queryCount);
sqlString.Append(queryFilter);
ApplyFilter(sqlString, filter, parameters);
sqlString.Append(@"ORDER BY TeacherId
OFFSET (@PageNumber - 1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;");
using (SqlConnection sqlConnection = GetSqlConnection())
{
using (var multi = sqlConnection.QueryMultiple(sqlString.ToString(), parameters))
{
var queryMultipleCount = multi.Read <long>().First();
var queryMultipleFilter = multi.Read <Teacher>();
PagedResult <Teacher> pagedTeachers = PagedResult <Teacher> .Create(queryMultipleFilter, queryMultipleCount);
return(pagedTeachers);
}
}
}
public IEnumerable <Teacher> Select(TeacherFilter filter)
{
string procedureName = "Teacher_Select";
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(procedureName, connection)
{
CommandType = System.Data.CommandType.StoredProcedure
};
var paramList = GetFilterParam(filter);
foreach (var param in paramList)
{
SqlParameter Param = new SqlParameter
{
ParameterName = param.Key,
Value = param.Value
};
command.Parameters.Add(Param);
}
var result = command.ExecuteReader();
List <Teacher> resultList = new List <Teacher>();
if (result.HasRows)
{
while (result.Read())
{
Teacher entity = new Teacher
{
Id = result.GetInt32(0),
Name = result.GetString(1),
Email = result.IsDBNull(2)? null : result.GetString(2)
};
resultList.Add(entity);
}
}
result.Close();
return(resultList);
}
}
private List <KeyValuePair <string, object> > GetFilterParam(TeacherFilter filter)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >();
if (filter == null)
{
return(parameters);
}
if (filter.Id.HasValue)
{
parameters.Add(new KeyValuePair <string, object>("@Id", filter.Id));
}
if (filter.Name != null && filter.Name != "")
{
parameters.Add(new KeyValuePair <string, object>("@Name", filter.Name));
}
if (filter.Email != null && filter.Email != "")
{
parameters.Add(new KeyValuePair <string, object>("@OrganizationId", filter.Email));
}
return(parameters);
}
private void ApplyFilter(StringBuilder sql, TeacherFilter filter, DynamicParameters parameters)
{
var conditions = new List <string>();
if (filter == null)
{
sql.Replace("@DynamicFilter", "");
}
else
{
if (!string.IsNullOrWhiteSpace(filter.Name))
{
//Vai dar ruim, só pesquisar
conditions.Add("Name LIKE @Name");
parameters.Add("Name", filter.Name + "%", DbType.String);
}
if (filter.Genders?.Any() == true)
{
//Pode passar direto o parâmetro, dapper já ajusta dentro do parênteses
conditions.Add("Gender IN @Genders");
parameters.Add("Genders", filter.Genders);
}
if (filter.LevelIds?.Any() == true)
{
conditions.Add("LevelId IN @LevelIds");
parameters.Add("LevelIds", filter.LevelIds);
}
if (filter.MinSalary.HasValue && filter.MaxSalary.HasValue)
{
conditions.Add("Salary BETWEEN @MinSalary AND @MaxSalary");
parameters.Add("MinSalary", filter.MinSalary, DbType.Decimal);
parameters.Add("MaxSalary", filter.MaxSalary, DbType.Decimal);
}
//XOR --> apenas um é verdadeiro (^)
else if (filter.MinSalary.HasValue ^ filter.MaxSalary.HasValue)
{
if (filter.MinSalary.HasValue)
{
conditions.Add("Salary >= @MinSalary");
parameters.Add("MinSalary", filter.MinSalary, DbType.Decimal);
}
else
{
conditions.Add("Salary <= @MaxSalary");
parameters.Add("MaxSalary", filter.MaxSalary, DbType.Decimal);
}
}
if (filter.MinAdmitionDate.HasValue && filter.MaxAdmitionDate.HasValue)
{
conditions.Add("AdmitionDate BETWEEN @MinAdmitionDate AND @MaxAdmitionDate");
parameters.Add("MinAdmitionDate", filter.MinAdmitionDate, DbType.DateTime2);
parameters.Add("MaxAdmitionDate", filter.MaxAdmitionDate, DbType.DateTime2);
}
else if (filter.MinAdmitionDate.HasValue ^ filter.MaxAdmitionDate.HasValue)
{
if (filter.MinAdmitionDate.HasValue)
{
conditions.Add("AdmitionDate >= @MinAdmitionDate");
parameters.Add("MinAdmitionDate", filter.MinAdmitionDate, DbType.DateTime2);
}
else
{
conditions.Add("AdmitionDate <= @MaxAdmitionDate");
parameters.Add("MaxAdmitionDate", filter.MaxAdmitionDate, DbType.DateTime2);
}
}
string dynamicFilter = conditions.Any() ? $"WHERE {string.Join(" AND ", conditions)}" : "";
sql.Replace("@DynamicFilter", dynamicFilter);
}
}