/// <inheritdoc />
public Task <PagedList <T> > ReadList <T>(object whereConditions, object sortOrders, int pageSize, int pageNumber)
{
TypeMap type = TypeMap.GetTypeMap <T>();
// create the paging variables
int firstRow = ((pageNumber - 1) * pageSize) + 1;
int lastRow = firstRow + (pageSize - 1);
// get the candidate objects
IEnumerable <T> filteredT;
IDictionary <string, object> whereDict = type.CoalesceToDictionary(whereConditions);
if (whereDict.Count == 0)
{
filteredT = this.ReadAll <T>().GetAwaiter().GetResult();
}
else
{
filteredT = this.ReadList <T>(whereConditions).GetAwaiter().GetResult();
}
// get the total number of candidate objects
int total = filteredT.Count();
// validate / build the ordering string
string ordering = string.Empty;
IDictionary <string, SortOrder> sortOrderDict = type.CoalesceSortOrderDictionary(sortOrders);
for (int i = 0; i < sortOrderDict.Count; i++)
{
// check whether this property exists for the type
string propertyName = sortOrderDict.Keys.ElementAt(i);
if (!type.AllProperties.ContainsKey(propertyName))
{
throw new ArgumentException($"Failed to find property {propertyName} on {type.Type.Name}");
}
ordering += string.Format(
"{0}{1}{2}",
propertyName,
sortOrderDict[propertyName] == SortOrder.Descending ? " desc" : string.Empty,
i != sortOrderDict.Count - 1 ? "," : string.Empty);
}
// order the rows and take the results for this page
filteredT = filteredT.AsQueryable <T>().OrderBy(ordering).Skip(firstRow - 1).Take(pageSize);
return(Task.FromResult(new PagedList <T>()
{
Rows = filteredT,
HasNext = lastRow < total,
HasPrevious = firstRow > 1,
TotalPages = (total / pageSize) + ((total % pageSize) > 0 ? 1 : 0),
TotalRows = total
}));
}
/// <inheritdoc />
public Task <IEnumerable <T> > ReadList <T>(object whereConditions)
{
// get the type map
TypeMap type = TypeMap.GetTypeMap <T>();
// validate the where conditions
whereConditions = type.CoalesceToDictionary(whereConditions);
type.ValidateWhereProperties(whereConditions);
return(Task.FromResult(this.ReadWhere <T>((IDictionary <string, object>)whereConditions)));
}
/// <inheritdoc />
public Task <T> Update <T>(object properties)
{
// get the type map
TypeMap type = TypeMap.GetTypeMap <T>();
// validate the key
IDictionary <string, object> id = type.ValidateKeyProperties(properties);
// get the existing object
T obj = this.ReadWhere <T>(id).SingleOrDefault();
if (obj != null)
{
// find the properties to update
IDictionary <string, object> allProps = type.CoalesceToDictionary(properties);
IDictionary <string, object> updateProps = allProps.Where(kvp => !id.ContainsKey(kvp.Key))
.ToDictionary(kvp => kvp.Key, kvp => kvp.Value);
// check whether there are any properties to update
if (!type.UpdateableProperties.Any(x => updateProps.Keys.Contains(x.Property)))
{
throw new ArgumentException("Please provide one or more updateable properties.");
}
// update the properties that are not date stamps
foreach (string propertyName in updateProps.Keys)
{
PropertyMap propertyMap = type.UpdateableProperties.Where(x => x.Property == propertyName).SingleOrDefault();
if (propertyMap != null)
{
propertyMap.PropertyInfo.SetValue(obj, updateProps[propertyName]);
}
}
// update the properties that are date stamps (and updateable)
if (type.DateStampProperties.Any(x => !x.IsReadOnly))
{
DateTime dateStamp = DateTime.Now;
foreach (PropertyMap propertyMap in type.DateStampProperties.Where(x => !x.IsReadOnly))
{
propertyMap.PropertyInfo.SetValue(obj, dateStamp);
}
}
}
return(Task.FromResult(obj));
}
/// <inheritdoc />
public string BuildSelectWhere(TypeMap type, object whereConditions, object sortOrders, int firstRow, int lastRow)
{
if (type == null)
{
throw new ArgumentException("Please provide a non-null TypeMap.");
}
// build the WHERE clause (if any specified)
IDictionary <string, object> whereConditionsDict = type.CoalesceToDictionary(whereConditions);
string where = whereConditionsDict.Any() ? BuildWhere(type, whereConditions) : string.Empty;
// build the ORDER BY clause (always required and will default to primary key columns ascending, if none specified)
string orderBy = this.BuildOrderBy(type, sortOrders);
// build paging sql
return($@"SELECT {string.Join(", ", type.SelectProperties.Select(x => x.ColumnSelect))}
FROM (SELECT ROW_NUMBER() OVER ({orderBy}) AS RowNo, *
FROM {type.TableIdentifier}
{where}) tmp
WHERE tmp.RowNo BETWEEN {firstRow} AND {lastRow};");
}
/// <inheritdoc />
public string BuildSelectCount(TypeMap type, object whereConditions)
{
if (type == null)
{
throw new ArgumentException("Please provide a non-null TypeMap.");
}
string cacheKey = $"{type.Type.FullName}_Select_Count";
if (!this.staticSqlStatementCache.ContainsKey(cacheKey))
{
this.staticSqlStatementCache[cacheKey] = $"SELECT COUNT(*) FROM {type.TableIdentifier}";
}
IDictionary <string, object> whereConditionsDict = type.CoalesceToDictionary(whereConditions);
if (whereConditionsDict.Any())
{
return($"{this.staticSqlStatementCache[cacheKey]} {BuildWhere(type, whereConditions)}");
}
return(this.staticSqlStatementCache[cacheKey]);
}
