private static string BuildWhereClause(BlockFilterCriteria filter, out DynamicParameters param)
{
param = new DynamicParameters();
var whereClause = new StringBuilder();
if (filter.HeightFrom.HasValue)
{
var fromHeight = filter.HeightFrom.Value;
param.Add(nameof(fromHeight), fromHeight);
whereClause.Append($"AND b.[Height] >= @fromHeight ");
}
if (filter.HeightTo.HasValue)
{
var toHeight = filter.HeightTo.Value;
param.Add(nameof(toHeight), toHeight);
whereClause.Append($"AND b.[Height] <= @toHeight ");
}
if (filter.MinSize.HasValue)
{
var min = filter.MinSize.Value;
param.Add(nameof(min), min);
whereClause.Append($"AND b.[Size] >= @min ");
}
if (filter.MaxSize.HasValue)
{
var max = filter.MaxSize.Value;
param.Add(nameof(max), max);
whereClause.Append($"AND b.[Size] <= @max ");
}
if (filter.UtcFrom.HasValue)
{
var fromDate = filter.UtcFrom.Value;
param.Add(nameof(fromDate), fromDate);
whereClause.Append($"AND b.[Timestamp] >= @fromDate ");
}
if (filter.UtcTo.HasValue)
{
var toDate = filter.UtcTo.Value;
param.Add(nameof(toDate), toDate);
whereClause.Append($"AND b.[Timestamp] <= @toDate ");
}
if (filter.Channel.HasValue)
{
var channel = filter.Channel.Value;
param.Add(nameof(channel), channel);
whereClause.Append($"AND b.[Channel] = @channel ");
}
return(whereClause.ToString());
}
public async Task <FilterResult <BlockLiteDto> > GetBlocksFilteredAsync(BlockFilterCriteria filter, int start, int count, bool countResults, int?maxResults = null)
{
const string from = @"
FROM [dbo].[Block] b
INNER JOIN [dbo].[Transaction] t ON t.[BlockHeight] = b.[Height]
WHERE 1 = 1 ";
const string groupBy = @"
GROUP BY b.[Height], b.[Hash], b.[Size], b.[Channel], b.[Version], b.[MerkleRoot],
b.[Timestamp], b.[Nonce], b.[Bits], b.[Difficulty], b.[Mint] " ;
var where = BuildWhereClause(filter, out var param);
var sqlOrderBy = "ORDER BY ";
switch (filter.OrderBy)
{
case OrderBlocksBy.Highest:
sqlOrderBy += "b.[Height] DESC ";
break;
case OrderBlocksBy.Lowest:
sqlOrderBy += "b.[Height] ";
break;
case OrderBlocksBy.Largest:
sqlOrderBy += "b.[Size] DESC ";
break;
case OrderBlocksBy.Smallest:
sqlOrderBy += "b.[Size] ";
break;
default:
sqlOrderBy += "b.[Height] DESC ";
throw new ArgumentOutOfRangeException();
}
var sqlQ = $@"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
b.[Height],
b.[Hash],
b.[Size],
b.[Channel],
b.[Timestamp],
b.[Difficulty],
b.[Mint],
COUNT(t.[TransactionId]) AS TransactionCount
{from}
{where}
{groupBy}
{sqlOrderBy}
OFFSET @start ROWS FETCH NEXT @count ROWS ONLY;";
var sqlC = $@"SELECT
COUNT(*)
FROM (SELECT TOP (@maxResults)
1 AS Cnt
{from}
{where}
{groupBy}) AS resultCount;";
using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync())
{
var results = new FilterResult <BlockLiteDto>();
param.Add(nameof(count), count);
param.Add(nameof(start), start);
param.Add(nameof(maxResults), maxResults ?? int.MaxValue);
using (var multi = await sqlCon.QueryMultipleAsync(string.Concat(sqlQ, sqlC), param))
{
var dbBlocks = (await multi.ReadAsync()).Select(x => new BlockLiteDto
{
Height = x.Height,
Hash = x.Hash,
Size = x.Size,
Channel = x.Channel,
Timestamp = x.Timestamp,
Difficulty = x.Difficulty,
Mint = x.Mint,
TransactionCount = x.TransactionCount
});
results.Results = dbBlocks.ToList();
results.ResultCount = countResults
? (int)(await multi.ReadAsync <int>()).FirstOrDefault()
: -1;
}
return(results);
}
}
