/// <summary>Searches for grouped datasets and returns the id of the first occurence (sql handles this differently).</summary> /// <param name="search">Search definition.</param> /// <param name="option">Options for the search.</param> /// <returns>Returns a list of rows matching the specified criteria.</returns> protected internal virtual IList <Row> SqlGetGroupRows(SqlSearch search, ResultOption option) { RowLayout layout; var command = new StringBuilder(); command.Append("SELECT "); if (Storage.SupportsAllFieldsGroupBy) { layout = Layout; command.Append("*"); } else { layout = null; var fieldNumber = 0; foreach (var fieldName in search.FieldNames) { if (fieldNumber++ > 0) { command.Append(", "); } command.Append(Storage.EscapeFieldName(Layout[fieldName])); } } command.Append(" FROM "); command.Append(FQTN); command.Append(" WHERE "); command.Append(search); var groupCount = 0; foreach (var o in option.Filter(ResultOptionMode.Group)) { if (groupCount++ == 0) { command.Append(" GROUP BY "); } else { command.Append(","); } command.Append(Storage.EscapeFieldName(Layout[o.Parameter])); } return(Query(new SqlCmd(command.ToString(), search.Parameters.ToArray()), ref layout)); }
/// <summary>Searches the table for rows with given field value combinations.</summary> /// <param name="search">The search to run.</param> /// <param name="option">Options for the search and the result set.</param> /// <returns>Returns the ID of the row found or -1.</returns> protected internal virtual IList <Row> SqlGetRows(SqlSearch search, ResultOption option) { if (option.Contains(ResultOptionMode.Group)) { return(SqlGetGroupRows(search, option)); } var command = new StringBuilder(); command.Append("SELECT * FROM "); command.Append(FQTN); command.Append(" WHERE "); command.Append(search); var orderCount = 0; foreach (var o in option.Filter(ResultOptionMode.SortAsc, ResultOptionMode.SortDesc)) { if (orderCount++ == 0) { command.Append(" ORDER BY "); } else { command.Append(","); } command.Append(Storage.EscapeFieldName(Layout[o.Parameter])); if (o.Mode == ResultOptionMode.SortAsc) { command.Append(" ASC"); } else { command.Append(" DESC"); } } var limit = 0; foreach (var o in option.Filter(ResultOptionMode.Limit)) { if (limit++ > 0) { throw new InvalidOperationException("Cannot set two different limits!"); } command.Append(" LIMIT " + o.Parameter); } var offset = 0; foreach (var o in option.Filter(ResultOptionMode.Offset)) { if (offset++ > 0) { throw new InvalidOperationException("Cannot set two different offsets!"); } command.Append(" OFFSET " + o.Parameter); } var layout = Layout; return(Query(new SqlCmd(command.ToString(), search.Parameters.ToArray()), ref layout)); }
/// <summary>Searches for grouped datasets and returns the number of items found.</summary> /// <param name="search">Search definition.</param> /// <param name="option">Options for the search.</param> /// <returns>Numer of items found.</returns> protected internal virtual long SqlCountGroupBy(SqlSearch search, ResultOption option) { if (search is null) { throw new ArgumentNullException(nameof(search)); } if (option is null) { throw new ArgumentNullException(nameof(option)); } var command = new StringBuilder(); command.Append("SELECT COUNT("); if (Storage.SupportsAllFieldsGroupBy) { command.Append("*"); } else { var fieldNumber = 0; foreach (var fieldName in search.FieldNames) { if (fieldNumber++ > 0) { command.Append(", "); } command.Append(Storage.EscapeFieldName(Layout[fieldName])); } } command.Append(") FROM "); command.Append(FQTN); command.Append(" WHERE "); command.Append(search); if (option.Contains(ResultOptionMode.Limit) | option.Contains(ResultOptionMode.Offset)) { throw new InvalidOperationException("Cannot use Option.Group and Option.Limit/Offset at once!"); } var groupCount = 0; foreach (var o in option.Filter(ResultOptionMode.Group)) { if (groupCount++ == 0) { command.Append(" GROUP BY "); } else { command.Append(","); } command.Append(Storage.EscapeFieldName(Layout[o.Parameter])); } var value = QueryValue(new SqlCmd(command.ToString(), search.Parameters.ToArray())); if (value == null) { throw new InvalidDataException($"Could not read value from {FQTN}!"); } return(Convert.ToInt64(value)); }