public Result TableRows(string table, RowsOptions opts)
{
var query = new StringBuilder();
query.Append($"SELECT * FROM {table}");
if (!string.IsNullOrWhiteSpace(opts.Where))
{
query.Append($" WHERE {opts.Where}");
}
if (!string.IsNullOrWhiteSpace(opts.SortColumn))
{
query.Append($" ORDER BY {opts.SortColumn} {opts.SortOrder}");
}
else
{
query.Append(" ORDER BY 1 ASC"); // hopefully will pick the primary key and hopefully the primary key direction is ASC
}
query.Append($" OFFSET {opts.Offset} ROWS");
query.Append($" FETCH NEXT {opts.Limit} ROWS ONLY");
return(session.Query(query.ToString()));
}
public IActionResult GetTableRows([FromRoute] string table, [FromQuery] RowsOptions opts)
{
var session = GetSession();
if (session == null)
{
return(NotConnected());
}
var database = databaseFactory.Database(session);
var tableRows = database.TableRows(table, opts);
var numRows = database.TableRowsCount(table, opts);
var numPages = numRows / opts.Limit;
if (numPages * opts.Limit < numRows)
{
numPages++;
}
tableRows.Pagination = new Pagination
{
Rows = numRows,
Page = (opts.Offset / opts.Limit) + 1,
Pages = numPages,
PerPage = opts.Limit,
};
return(Ok(tableRows));
}
public long TableRowsCount(string table, RowsOptions opts)
{
// Can return the estimated rows from the catalog if we're not filtering
if (string.IsNullOrWhiteSpace(opts.Where))
{
return(TableInfo(table).Rows);
}
var query = $"SELECT COUNT(1) FROM {table} WHERE {opts.Where}";
return((long)session.ExecuteScalar(query));
}
public long TableRowsCount(string table, RowsOptions opts)
{
return(databaseImplementation.TableRowsCount(table, opts));
}
public Result TableRows(string table, RowsOptions opts)
{
return(databaseImplementation.TableRows(table, opts));
}
