/// <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]); }