public PageResult<string> GetResult(int page, FieldCriteria[] criterias)
{
var provider = new DbDataProvider();
var result = new PageResult<string>();
result.TotalCount = Convert.ToInt32(provider.Count(criterias));
result.PageCount = result.TotalCount / pageSize + 1;
result.Page = page;
result.Result = provider.GetEntitiesId(page, pageSize, criterias);
return result;
}
public static string BuildCountQuery(string tableName, FieldCriteria[] criterias)
{
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT Count(Id)");
query.AppendFormat("FROM {0}\n", tableName);
string whereClause = BuildWhereClause(criterias);
if (!string.IsNullOrWhiteSpace(whereClause))
query.AppendFormat("WHERE {0}\n", whereClause);
string orderClause = BuildOrderClause(criterias);
return query.ToString();
}
public long Count(FieldCriteria[] criterias)
{
try {
connection.Open();
SqlCeCommand command = new SqlCeCommand(SqlQueryBuilder.BuildCountQuery("Entity", criterias), connection);
object o = command.ExecuteScalar();
return Convert.ToInt64(o);
}
finally {
connection.Close();
}
}
private static string BuildOrderClause(FieldCriteria[] criterias)
{
StringBuilder clause = new StringBuilder();
for (int i = 0; i < criterias.Length; ++i) {
if (criterias[i].Sort != 0) {
clause.Append(criterias[i].FieldName);
clause.Append(" ");
clause.Append(criterias[i].Sort > 0 ? "ASC" : "DESC");
if (i + 1 != criterias.Length)
clause.Append(", ");
}
}
return clause.ToString();
}
public static string BuildSelectQuery(string tableName, FieldCriteria[] criterias, int pageNumber, int pageSize)
{
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT *");
query.AppendFormat("FROM {0}\n", tableName);
string whereClause = BuildWhereClause(criterias);
if (!string.IsNullOrWhiteSpace(whereClause))
query.AppendFormat("WHERE {0}\n", whereClause);
string orderClause = BuildOrderClause(criterias);
if (!string.IsNullOrWhiteSpace(orderClause))
query.AppendFormat("ORDER BY {0}\n", orderClause);
else
query.AppendLine("ORDER BY Code ASC");
query.Append(BuildPagingClause(pageNumber, pageSize));
return query.ToString();
}
public string[] GetEntitiesId(int page, int pageLength, FieldCriteria[] criterias)
{
List<string> ids = new List<string>();
try {
connection.Open();
SqlCeCommand command = new SqlCeCommand(SqlQueryBuilder.BuildSelectIdsQuery("Entity", criterias, page, pageLength), connection);
var reader = command.ExecuteReader();
while (reader.Read()) {
string id = reader.GetValue(0).ToString();
ids.Add(id);
}
return ids.ToArray();
}
finally {
connection.Close();
}
}
public Entity[] GetEntities(int page, int pageLength, FieldCriteria[] criterias)
{
List<Entity> entities = new List<Entity>();
try {
connection.Open();
SqlCeCommand command = new SqlCeCommand(SqlQueryBuilder.BuildSelectQuery("Entity", criterias, page, pageLength), connection);
var reader = command.ExecuteReader();
while (reader.Read()) {
Entity entity = new Entity();
PopulateEntity(entity, reader);
entities.Add(entity);
}
return entities.ToArray();
}
finally {
connection.Close();
}
}
private static string BuildWhereClause(FieldCriteria[] criterias)
{
StringBuilder clause = new StringBuilder();
for (int i = 0; i < criterias.Length; ++i) {
if (!string.IsNullOrWhiteSpace(criterias[i].Filter)) {
clause.Append(criterias[i].FieldName);
clause.Append(" LIKE ");
clause.AppendFormat("'%{0}%'", criterias[i].Filter);
if (i + 1 != criterias.Length)
clause.Append(" AND ");
}
}
return clause.ToString();
}
