public async Task <List <TableInfo> > GetTableListFromDb() { const string sql = "SELECT * FROM ray_tablelist where prefix=@Table order by version asc"; using (var connection = SqlFactory.CreateConnection(storageConfig.Connection)) { return((await connection.QueryAsync <TableInfo>(sql, new { Table = storageConfig.EventTable })).AsList()); } }
public async Task CreateEventTable(TableInfo table) { const string sql = @" create table {0} ( StateId varchar({1}) not null, UniqueId varchar(250) null, TypeCode varchar(100) not null, Data jsonb not null, Version int8 not null, Timestamp int8 not null, constraint {0}_id_unique unique(StateId,TypeCode,UniqueId) ) WITH (OIDS=FALSE); CREATE UNIQUE INDEX {0}_Version ON {0} USING btree(StateId, Version);"; const string insertSql = "INSERT into ray_tablelist VALUES(@Prefix,@Name,@Version,@CreateTime)"; var key = $"{storageConfig.Connection}-{table.Name}-{storageConfig.StateIdLength}"; if (createEventTableListDict.TryAdd(key, true)) { using (var connection = SqlFactory.CreateConnection(storageConfig.Connection)) { await connection.OpenAsync(); using (var trans = connection.BeginTransaction()) { try { await connection.ExecuteAsync(string.Format(sql, table.Name, storageConfig.StateIdLength), transaction : trans); await connection.ExecuteAsync(insertSql, table, trans); trans.Commit(); createEventTableListDict[key] = true; } catch (Exception e) { trans.Rollback(); if (e is Npgsql.PostgresException ne && ne.ErrorCode == -2147467259) { return; } else { createEventTableListDict.TryRemove(key, out var v); throw; } }
public DbConnection CreateConnection() { return(SqlFactory.CreateConnection(Connection)); }