private async Task <DbConnection> GetOpenedConnectionAsync()
{
var connection = _dbConnectionProvider.Get();
await connection.OpenAsync().ConfigureAwait(false);
return(connection);
}
public async Task <IDictionary <string, object> > InsertRow(string table, List <KeyValuePair <string, object> > formValues)
{
CheckColumnNames(table, formValues);
var conn = await _connectionProvider.Get();
var tableInfo = _dbInspector.GetSchema().FindTableByName(table);
var parameters = ToDbParameters(formValues, tableInfo);
var colNames = parameters.Keys.Where(x => Regex.IsMatch(x, "^[\\w]+$")).ToList();
var sql = new[]
{
$"INSERT INTO {table}",
$"({colNames.JoinToString()})",
$"VALUES ({colNames.Select(x => "@" + x).JoinToString()}) " +
$"RETURNING {table}.*"
}.JoinToString(" \r\n");
var ins = await conn.QuerySingleAsync(sql, parameters);
return(ins);
}
protected DataContext(IDbConnectionProvider connectionProvider, string connectionName = "default")
{
_transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted, Timeout = TimeSpan.FromMinutes(5)
});
_connection = connectionProvider.Get(connectionName);
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
}
private async Task <ImmutableSortedDictionary <string, string> > GetSelectorRows(string otherTable)
{
var select = $"select * from {otherTable}";
var conn = await _connectionProvider.Get();
var rows = await conn.QueryAsync(select);
var items = rows.Cast <IDictionary <string, object> >().ToList()
.Select(x => KeyValuePair.Create(_dbInspector.GetId(otherTable, x), _dbInspector.GetTitle(otherTable, x)))
.ToImmutableSortedDictionary();
return(items);
}
private async Task <IHtmlElement[]> ServerInformation()
{
var strings = new[]
{
"SELECT concat( SESSION_USER, ', ', CURRENT_USER)",
"select auth.uid()",
};
var conn = await _connectionProvider.Get();
var elements = strings.Select(s => conn.QuerySingle <object>(s))
.Select(r => new Li(r.ToString()))
.ToArray();
return(elements);
// var x = await conn.QuerySingleAsync("select set_config('request.jwt.claim.sub', 'f65eaad3-2041-4f01-a5db-133c57ebdb05', false)");
// return new Span("Authid=" + x).ToArray();
}
public async Task <object> GetResultForTable(string table)
{
IQueryCollection query = _httpContextAccessor.HttpContext.Request.Query;
var where = "";
if (query.Any())
{
where += " WHERE ";
}
var parameters = new SortedDictionary <string, object>();
foreach (var(key, value) in query)
{
where += key + "=@" + key;
var colSpec = _dbInspector.GetSchema().FindTableByName(table).FindColumn(key);
parameters.Add(key, _dbMutator.Coerce(colSpec, value.SingleOrDefault()));
}
var tabInfo = _dbInspector.GetSchema().FindTableByName(table);
var tab = tabInfo.SchemaOwner.Equals("auth") ? "auth." + table : table;
var sql = $"select * from {tab}{where}";
var conn = await _dbConnectionProvider.Get();
var rs = await conn.QueryAsync(sql, parameters);
var rs2 = rs
.Cast <IDictionary <string, object> >()
.Select(d => AddSelfLink(table, d))
// .Cast<IDictionary<string, object>>()
.ToArray();
foreach (var o in rs2)
{
o["type"] = table;
}
return(new SearchResult {
Items = rs2, Links = LinksForTable(table)
});
}
