private dynamic BuildPagedResult(string primaryKeyField = "", string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args) { dynamic result = new ExpandoObject(); var countSQL = string.Format("SELECT COUNT({0}) FROM {1}", PrimaryKeyField.Split(KeyColSeparator).FirstOrDefault(), TableName); if (String.IsNullOrEmpty(orderBy)) { orderBy = string.IsNullOrEmpty(primaryKeyField) ? PrimaryKeyField : primaryKeyField; } if (!string.IsNullOrEmpty(where)) { if (!where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase)) { where = " WHERE " + where; } } var query = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) AS Row, {0} FROM {2} {3}) AS {2} ", columns, orderBy, TableName, where); var pageStart = (currentPage - 1) * pageSize; query += string.Format(" WHERE Row > {0} AND Row <={1}", pageStart, (pageStart + pageSize)); countSQL += where; result.TotalRecords = Scalar(countSQL, args); result.TotalPages = result.TotalRecords / pageSize; if (result.TotalRecords % pageSize > 0) { result.TotalPages += 1; } result.Items = Query(string.Format(query, columns, TableName), args); return(result); }
/// <summary> /// Returns a single row from the database /// </summary> public virtual dynamic Single(string columns, params object[] key) { var sql = string.Format("SELECT {0} FROM {1}", columns, TableName); var primaryKeyElem = PrimaryKeyField.Split(KeyColSeparator).Select(x => x.Trim()).ToArray(); for (var i = 0; i < primaryKeyElem.Length; i++) { if (i == 0) { sql = sql + " WHERE " + primaryKeyElem[i] + " = @" + i; } else { sql = sql + " AND " + primaryKeyElem[i] + " = @" + i; } } var items = Query(sql, key).ToList(); return(items.FirstOrDefault()); }