/// <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 number of rows found.</returns>
protected internal virtual long SqlCount(SqlSearch search, ResultOption option)
{
if (search is null)
{
throw new ArgumentNullException(nameof(search));
}
if (option is null)
{
throw new ArgumentNullException(nameof(option));
}
if (option.Contains(ResultOptionMode.Group))
{
return(SqlCountGroupBy(search, option));
}
var command = new StringBuilder();
command.Append("SELECT COUNT(*) FROM ");
command.Append(FQTN);
command.Append(" WHERE ");
command.Append(search);
foreach (var o in option.ToArray())
{
switch (o.Mode)
{
case ResultOptionMode.SortAsc:
case ResultOptionMode.SortDesc:
case ResultOptionMode.None:
break;
default:
throw new InvalidOperationException($"ResultOptionMode {o.Mode} not supported!");
}
}
var value = Storage.QueryValue(new SqlCmd(command.ToString(), search.Parameters.ToArray()));
if (value == null)
{
throw new InvalidDataException($"Could not read value from {FQTN}!");
}
return(Convert.ToInt64(value));
}
/// <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));
}
