public async Task <KnownUserRecord> GetKnownUserById(Guid uniqueId) { return((await _queryExecutor .NewQuery("SELECT * FROM [security].[User] WHERE [UniqueId]=@UniqueId;") .WithParameters(new { UniqueId = uniqueId }) .ExecuteAsync <KnownUserRecord>()) .Single()); }
public async Task <UserForAuthentication> GetUserForAuthentication(string username) { var query = @" SELECT [Id], [Username], [PasswordHash], [Salt], [Iterations], [Prf], [ForceChangePassword], [Title], [GivenName], [FamilyName], [EmailAddress] FROM [dbo].[User] WHERE [Username] = @Username AND [IsDeleted] = 0"; var results = await _queryExecutor.NewQuery(query) .WithParameters(new { Username = username }) .ExecuteDynamicAsync(); return(results .Select( _ => new UserForAuthentication { Id = _.Id, Username = _.Username, Password = new HashedPassword( _.PasswordHash, _.Salt, _.Iterations, (KeyDerivationPrf)Enum.Parse(typeof(KeyDerivationPrf), _.Prf)), ForceChangePassword = _.ForceChangePassword, Title = _.Title, FirstName = _.GivenName, LastName = _.FamilyName, EmailAddress = _.EmailAddress }) .SingleOrDefault()); }
public async Task UpdateSport(string sportName, Sport newState) { if (sportName == null) { throw new ArgumentNullException(nameof(sportName)); } if (newState == null) { throw new ArgumentNullException(nameof(newState)); } var numChanged = await _queryExecutor .NewQuery("UPDATE [dbo].[Sport] SET [Name]=@Name, [FullName]=@FullName WHERE [Name]=@id") .WithCommandType(CommandType.Text) .WithParameters( new { Id = sportName, Name = newState.Name, FullName = newState.FullName }) .ExecuteAsync(); if (numChanged < 0) { throw new InvalidIdentifierException($"No Sport with id '{sportName}' was found."); } }
public async Task UpdateVenue(string venueName, Venue newState) { if (venueName == null) { throw new ArgumentNullException(nameof(venueName)); } if (newState == null) { throw new ArgumentNullException(nameof(newState)); } var numChanged = await _queryExecutor .NewQuery("UPDATE [dbo].[Venue] SET [Name]=@Name, [Country]=@Country WHERE [Name]=@id") .WithCommandType(CommandType.Text) .WithParameters( new { Id = venueName, Name = newState.Name, Country = newState.Country }) .ExecuteAsync(); if (numChanged < 0) { throw new InvalidIdentifierException($"No Venue with id '{venueName}' was found."); } }
public async Task <IEnumerable <EventHistoryItem> > GetEventHistory(int roundId) { var round = await _context.Round .AsNoTracking() .Include(r => r.RelatedSeason) .AsNoTracking() .Where(r => r.Id == roundId) .FirstOrDefaultAsync(); if (round == null) { throw new ArgumentException("The specified round was not found.", nameof(roundId)); } return(await _queryExecutor.NewQuery(@" SELECT TOP 5 R.[Id], R.[Date], R.[Rating], R.[Rain], SE.[Name] AS WinningTeam, STRING_AGG(P.[FirstName] + ' ' + P.[LastName], ', ') WITHIN GROUP (ORDER BY P.[LastName] ASC, P.[FirstName] ASC) AS WinningParticipants FROM [dbo].[Round] R INNER JOIN [dbo].[Season] S ON R.[Season] = S.[Id] INNER JOIN [dbo].[Status] ST ON ST.[Name] = R.[Status] LEFT JOIN [dbo].[SeasonEntry] SE ON SE.[Season] = S.[Id] AND SE.[Team] = R.[WinningTeam] LEFT JOIN [dbo].[RoundWinner] RW ON R.[Id] = RW.[Round] LEFT JOIN [dbo].[Participant] P ON P.[Id] = RW.[Participant] WHERE R.[Venue] = @Venue -- If the requested round is non-championship, only get history of non-championship events -- If the requested round is part of the championship, exclude non-championship events AND ((@IsNonChampionship = 1 AND R.[Number] = 0) OR (@IsNonChampionship = 0 AND R.[Number] > 0)) AND ST.[Step] > 1 AND S.[Sport] = @Sport AND R.[Date] <= @Date GROUP BY R.[Id], R.[Date], R.[Rating], R.[Rain], SE.[Name] ORDER BY R.[Date] DESC") .WithCommandType(CommandType.Text) .WithParameters(new { Venue = round.Venue, Sport = round.RelatedSeason.Sport, Date = round.Date.Date, IsNonChampionship = round.Number < 1 }) .ExecuteAsync <EventHistoryItem>()); }
public Task <IEnumerable <RoundToAcquire> > GetRoundsToAcquire() { return(_queryExecutor.NewQuery(@" SELECT S.[Sport], R.[Id], R.[Number], R.[Date], R.[Name], R.[Venue] FROM [dbo].[Round] R INNER JOIN [dbo].[Season] S ON S.[Id] = R.[Season] INNER JOIN [dbo].[Status] ST ON ST.[Name] = R.[Status] WHERE ST.[Step] = 0 -- Scheduled but not ReadyToWatch AND R.[Date] <= @Today") .WithCommandType(CommandType.Text) .WithParameters(new { Today = DateTime.Now.Date }) .ExecuteAsync <RoundToAcquire>()); }
public async Task UpdateSeasonEntry(SeasonEntryEditModel seasonEntry) { if (seasonEntry == null) { throw new ArgumentNullException(nameof(seasonEntry)); } await _queryExecutor .NewQuery("UPDATE [dbo].[SeasonEntry] SET Name=@Name WHERE [Season]=@Season AND [Team]=@Team") .WithCommandType(CommandType.Text) .WithParameters(new { Season = seasonEntry.Season, Team = seasonEntry.Team, Name = seasonEntry.Name }) .ExecuteAsync(); }