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