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) }); }