private IDictionary <string, object>[] GetLinkedBeanRowsEx(string kind, LinkScenario ls)
{
// SELECT * is not an option here, because one Primary Key column is
// not delivered. So the projection has to be put together "manually"
// and checked if all Primary keys included.
var beanProjection = string.Join(", ",
Api.GetKindColumns(kind).Select(c => $"bean.{c} AS bean_{c}"));
if (!beanProjection.Contains($"bean.{ls.LinkedKindPkName}"))
{
beanProjection = $"bean.{ls.LinkedKindPkName} AS bean_{ls.LinkedKindPkName}, " + beanProjection;
}
var linkProjection = string.Join(", ",
Api.GetKindColumns(ls.LinkKind).Select(c => $"link.{c} AS link_{c}"));
if (!linkProjection.Contains($"link.{ls.LinkKindPkName}"))
{
linkProjection = $"link.{ls.LinkKindPkName} AS link_{ls.LinkKindPkName}, " + linkProjection;
}
return(Api.Rows(true,
CreateLinkQuery($"{beanProjection}, {linkProjection}", ls), ls.LinkingKindPkValue));
}
public static IDictionary <string, object>[] Fetch(this SqlBuilder sqlBuilder, BeanApi api,
bool useCache = true, params object[] parameters)
{
var query = sqlBuilder.ToSql();
return(query.StartsWith("SELECT")
? api.Rows(useCache, query, parameters)
: throw NotAnSqlQueryException.Create());
}
void GenericSqlQueries(BeanApi api)
{
/// ## Generic SQL Queries
/// Often it's needed to execute queries which don't map to beans:
/// aggregates, grouping, joins, selecting single column, etc.
///
/// `BeanApi` provides methods for such tasks:
///
{
#if CODE
// Load multiple rows
var rows = api.Rows(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author", 7);
// Load a single row
var row = api.Row(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author
ORDER BY COUNT(*) DESC
LIMIT 1", 7);
// Load a column
var col = api.Col <string>("SELECT DISTINCT author FROM book ORDER BY author");
// Load a single value
var count = api.Cell <int>("SELECT COUNT(*) FROM book");
#endif
}
/// For `Rows` and `Col`, there are unbuffered (memory-optimized) counterparts:
{
#if CODE
foreach (var row in api.RowsIterator("SELECT..."))
{
// do something
}
foreach (var item in api.ColIterator("SELECT..."))
{
// do something
}
#endif
}
/// To execute a non-query SQL command, use the `Exec` method:
#if CODE
api.Exec("SET autocommit = 0");
#endif
/// **NOTE:** all described functions accept parameters in the same form as [finder methods](#finding-beans-with-sql) do.
}
public static IDictionary <string, object>[] FetchPaginated(this SqlBuilder sqlBuilder, BeanApi api,
int pageNo, int perPage = 10, bool useCache = true, params object[] parameters)
{
var query = sqlBuilder.ToSql();
if (!query.StartsWith("SELECT"))
{
throw NotAnSqlQueryException.Create();
}
var pagination = PrepareFetchedPagination(api, query, pageNo, perPage);
var dbDetails = api.CreateDetails();
return(api.Rows(useCache,
$"{query} {dbDetails.Paginate(pagination.CurrentPage, perPage)}", parameters));
}
void GenericSqlQueries(BeanApi api)
{
/// ## Generic SQL Queries
/// Often it's needed to execute queries which don't map to beans:
/// aggregates, grouping, joins, selecting single column, etc.
///
/// `BeanApi` provides methods for such tasks:
///
{
#if CODE
// Load multiple rows
var rows = api.Rows(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author", 7);
// Load a single row
var row = api.Row(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author
ORDER BY COUNT(*) DESC
LIMIT 1", 7);
// Load a column
var col = api.Col<string>("SELECT DISTINCT author FROM book ORDER BY author");
// Load a single value
var count = api.Cell<int>("SELECT COUNT(*) FROM book");
#endif
}
/// For `Rows` and `Col`, there are unbuffered (memory-optimized) counterparts:
{
#if CODE
foreach(var row in api.RowsIterator("...")) {
// do something
}
foreach(var item in api.ColIterator("...")) {
// do something
}
#endif
}
/// To execute a non-query SQL command, use the `Exec` method:
#if CODE
api.Exec("SET autocommit = 0");
#endif
/// **NOTE:** all described functions accept parameters in the same form as [finder methods](#finding-beans-with-sql) do.
}
