protected virtual async Task <QueryResult <TEntity> > QueryAsync <TResult>(string filter, string[] selectColumns, int skip, int take,
CancellationToken cancellationToken, string[] orderByColumns, bool orderByAscending = true, IDictionary <string, object> filterValues = null, bool distinct = false)
{
using (var connection = await GetConnectionAsync(cancellationToken))
{
if (filterValues != null)
{
var filterValuesSql = SqlHelper.GetAndEqualsStatement(DatabaseDriver, filterValues);
filter = $"{filter} {DatabaseDriver.GetSqlStatementTemplate(SqlStatement.And)} ({filterValuesSql})";
}
int totalCount;
using (var countCommand = connection.CreateSelectCountCommand(DatabaseDriver, Table.Schema, Table.Name, filter, filterValues, distinct: distinct))
{
totalCount = Convert.ToInt32(
await countCommand.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false));
}
return(new QueryResult <TEntity>(
new DbDataReaderAsyncEnumerable <TEntity>(
GetConnectionAsync,
c =>
c.CreateSelectSkipTakeCommand(DatabaseDriver, Table.Schema, Table.Name, selectColumns, filter, skip, take,
orderByColumns, orderByAscending, filterValues, distinct),
Mapper,
selectColumns),
totalCount));
}
}
public virtual async Task SetAbsoluteKeyExpirationAsync(TKey key, DateTimeOffset expiration)
{
using (var cancellationTokenSource = CreateCancellationTokenSource())
{
using (var connection = await GetConnectionAsync(cancellationTokenSource.Token).ConfigureAwait(false))
{
var keyColumnValues = KeyMapper.GetColumnValues(key);
var columnValues = new Dictionary <string, object>
{
{ _expirationColumnName, expiration }
};
using (var command = connection.CreateTextCommand(
DatabaseDriver.GetSqlStatementTemplate(SqlStatement.Update),
new
{
schemaName = DatabaseDriver.ParseIdentifier(Table.Schema ?? DatabaseDriver.DefaultSchema),
tableName = DatabaseDriver.ParseIdentifier(Table.Name),
columnValues = SqlHelper.GetCommaEqualsStatement(DatabaseDriver, columnValues.Keys.ToArray()),
filter = SqlHelper.GetAndEqualsStatement(DatabaseDriver, keyColumnValues.Keys.ToArray())
},
keyColumnValues.Concat(columnValues).Select(c => c.ToDbParameter(DatabaseDriver))))
{
if (await command.ExecuteNonQueryAsync(cancellationTokenSource.Token).ConfigureAwait(false) ==
0)
{
throw new ArgumentException("Invalid key", nameof(key));
}
}
}
}
}
public virtual async Task <bool> RemoveExpirationAsync(TKey key)
{
using var cancellationTokenSource = CreateCancellationTokenSource();
await using var connection = await GetConnectionAsync(cancellationTokenSource.Token).ConfigureAwait(false);
var keyColumnValues = KeyMapper.GetColumnValues(key);
var columnValues = new Dictionary <string, object>
{
{ _expirationColumnName, DBNull.Value }
};
await using var command = connection.CreateTextCommand(
DatabaseDriver.GetSqlStatementTemplate(SqlStatement.Update),
new
{
schemaName = DatabaseDriver.ParseIdentifier(Table.Schema ?? DatabaseDriver.DefaultSchema),
tableName = DatabaseDriver.ParseIdentifier(Table.Name),
columnValues = SqlHelper.GetCommaEqualsStatement(DatabaseDriver, columnValues.Keys.ToArray()),
filter = SqlHelper.GetCombinedAndStatement(DatabaseDriver,
SqlHelper.GetAndEqualsStatement(DatabaseDriver, keyColumnValues.Keys.ToArray()),
SqlHelper.GetIsNotNullStatement(DatabaseDriver, columnValues.Keys.ToArray()))
},
keyColumnValues.Concat(columnValues).Select(c => c.ToDbParameter(DatabaseDriver)));
var teste = SqlHelper.GetCombinedAndStatement(DatabaseDriver,
SqlHelper.GetAndEqualsStatement(DatabaseDriver, keyColumnValues.Keys.ToArray()),
SqlHelper.GetIsNotNullStatement(DatabaseDriver, columnValues.Keys.ToArray()));
if (await command.ExecuteNonQueryAsync(cancellationTokenSource.Token).ConfigureAwait(false) == 0)
{
return(false);
}
return(true);
}
public virtual async Task <bool> TryUpdateAsync(TKey key, TValue newValue, TValue oldValue)
{
using (var cancellationTokenSource = CreateCancellationTokenSource())
{
using (var connection = await GetConnectionAsync(cancellationTokenSource.Token).ConfigureAwait(false))
{
var oldColumnValues = GetColumnValues(key, oldValue);
var filterOldColumnValues = oldColumnValues
.Select(kv => new KeyValuePair <string, object>($"Old{kv.Key}", kv.Value))
.ToDictionary(t => t.Key, t => t.Value);
var newColumnValues = GetColumnValues(key, newValue, true);
using (var command = connection.CreateTextCommand(
DatabaseDriver.GetSqlStatementTemplate(SqlStatement.Update),
new
{
schemaName = DatabaseDriver.ParseIdentifier(Table.Schema ?? DatabaseDriver.DefaultSchema),
tableName = DatabaseDriver.ParseIdentifier(Table.Name),
columnValues = SqlHelper.GetCommaEqualsStatement(DatabaseDriver, newColumnValues.Keys.ToArray()),
filter = SqlHelper.GetAndEqualsStatement(DatabaseDriver, oldColumnValues.Keys.ToArray(), filterOldColumnValues.Keys.ToArray())
},
newColumnValues.Concat(filterOldColumnValues).Select(c => c.ToDbParameter(DatabaseDriver))))
{
return(await command.ExecuteNonQueryAsync(cancellationTokenSource.Token).ConfigureAwait(false) == 1);
}
}
}
}
public TableFacts()
{
Schema = "my";
Name = "Items";
KeyColumnsNames = new[] { "Id" };
Columns = new Dictionary <string, SqlType>()
{
{ "Id", new SqlType(DbType.Guid) },
{ "Name", new SqlType(DbType.String, 250) },
};
SynchronizationStrategy = SchemaSynchronizationStrategy.UntilSuccess;
ConnectionString = "db=fake;user=a;pass=b";
DatabaseDriver = Substitute.For <IDatabaseDriver>();
DbConnection = Substitute.For <DbConnection>();
DbCommand = Substitute.For <DbCommand>();
DbDataReader = Substitute.For <DbDataReader>();
DatabaseDriver.CreateConnection(ConnectionString).Returns(DbConnection);
DatabaseDriver.GetSqlStatementTemplate(Arg.Any <SqlStatement>()).Returns("");
DbConnection.CreateCommand().Returns(DbCommand);
DbCommand.ExecuteScalarAsync(Arg.Any <CancellationToken>()).Returns(true);
DbCommand.ExecuteReaderAsync(Arg.Any <CancellationToken>()).Returns(DbDataReader);
}
