private async Task <IReadOnlyCollection <FeedPackageDetails> > GetPackageDetailsAsync(
SqlConnection sqlConnection,
Db2CatalogCursor cursor)
{
var packages = new List <FeedPackageDetails>();
var packageQuery = BuildDb2CatalogSqlQuery(cursor);
using (var packagesCommand = new SqlCommand(packageQuery, sqlConnection)
{
CommandTimeout = _commandTimeout
})
{
packagesCommand.Parameters.AddWithValue(CursorParameterName, cursor.CursorValue);
using (_telemetryService.TrackGetPackageDetailsQueryDuration(cursor))
{
using (var packagesReader = await packagesCommand.ExecuteReaderAsync())
{
while (await packagesReader.ReadAsync())
{
packages.Add(_db2catalogProjection.ReadFeedPackageDetailsFromDataReader(packagesReader));
}
}
}
}
return(packages);
}
/// <summary>
/// Asynchronously gets a <see cref="IReadOnlyCollection{FeedPackageDetails}"/> from the gallery database.
/// </summary>
/// <param name="cursor">Defines the cursor to be used.</param>
/// <returns>A task that represents the asynchronous operation.
/// The task result (<see cref="Task{TResult}.Result" />) returns an
/// <see cref="IReadOnlyCollection{FeedPackageDetails}" />.</returns>
private async Task <IReadOnlyCollection <FeedPackageDetails> > GetPackages(Db2CatalogCursor cursor)
{
using (var sqlConnection = await _connectionFactory.OpenAsync())
{
return(await GetPackageDetailsAsync(sqlConnection, cursor));
}
}
/// <summary>
/// Builds the SQL query string for db2catalog.
/// </summary>
/// <param name="cursor">The <see cref="Db2CatalogCursor"/> to be used.</param>
/// <returns>The SQL query string for the db2catalog job, build from the the provided <see cref="Db2CatalogCursor"/>.</returns>
internal static string BuildDb2CatalogSqlQuery(Db2CatalogCursor cursor)
{
return($@"SELECT TOP {cursor.Top} WITH TIES
{Db2CatalogSqlSubQuery}
AND P.[{cursor.ColumnName}] > @{CursorParameterName}
ORDER BY P.[{cursor.ColumnName}]");
}
/// <summary>
/// Returns a <see cref="SortedList{DateTime, IList{FeedPackageDetails}}"/> from the gallery database.
/// </summary>
/// <param name="keyDateFunc">The <see cref="DateTime"/> field to sort the <see cref="FeedPackageDetails"/> on.</param>
private async Task <SortedList <DateTime, IList <FeedPackageDetails> > > GetPackagesInOrder(
Func <FeedPackageDetails, DateTime> keyDateFunc,
Db2CatalogCursor cursor)
{
var allPackages = await GetPackages(cursor);
return(OrderPackagesByKeyDate(allPackages, keyDateFunc));
}
/// <summary>
/// Builds the SQL query string for db2catalog.
/// </summary>
/// <param name="cursor">The <see cref="Db2CatalogCursor"/> to be used.</param>
/// <returns>The SQL query string for the db2catalog job, build from the the provided <see cref="Db2CatalogCursor"/>.</returns>
internal static string BuildDb2CatalogSqlQuery(Db2CatalogCursor cursor)
{
// We need to provide an inner ORDER BY to support the TOP clause
return(string.Format(Db2CatalogSqlSubQuery,
$@"SELECT TOP {cursor.Top} WITH TIES ",
$@"AND P.[{cursor.ColumnName}] > @{CursorParameterName}
ORDER BY P.[{cursor.ColumnName}]",
$@"ORDER BY P_EXT.[{cursor.ColumnName}], P_EXT.[{Db2CatalogProjectionColumnNames.Key}]"));
}
private async Task <IReadOnlyCollection <FeedPackageDetails> > GetPackageDetailsAsync(
SqlConnection sqlConnection,
Db2CatalogCursor cursor)
{
var packageQuery = BuildDb2CatalogSqlQuery(cursor);
#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
using (var packagesCommand = new SqlCommand(packageQuery, sqlConnection)
{
CommandTimeout = _commandTimeout
})
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities
{
packagesCommand.Parameters.AddWithValue(CursorParameterName, cursor.CursorValue);
using (_telemetryService.TrackGetPackageDetailsQueryDuration(cursor))
{
return(await ReadPackagesAsync(packagesCommand));
}
}
}
public Task <SortedList <DateTime, IList <FeedPackageDetails> > > GetPackagesEditedSince(DateTime since, int top)
{
return(GetPackagesInOrder(
package => package.LastEditedDate,
Db2CatalogCursor.ByLastEdited(since, top)));
}
