public async Task <IDictionary <string, PostcodeInfo> > Execute(SqlTransaction transaction, GetPostcodeInfos query) { var sql = @" SELECT pc.Postcode, pc.Position.Lat Latitude, pc.Position.Long Longitude, pc.InEngland FROM Pttcd.Postcodes pc JOIN @Postcodes x ON pc.Postcode = x.Value"; var paramz = new { Postcodes = TvpHelper.CreateStringTable(query.Postcodes) }; return((await transaction.Connection.QueryAsync <PostcodeInfo>(sql, paramz, transaction: transaction)) .ToDictionary(p => p.Postcode, p => p)); }
public async Task <OneOf <NotFound, Success> > Execute(SqlTransaction transaction, CreateCourseRun query) { var sql = $@" IF NOT EXISTS (SELECT 1 FROM Pttcd.Courses WHERE CourseId = @CourseId) BEGIN SELECT 1 AS Result RETURN END INSERT INTO Pttcd.CourseRuns ( CourseRunId, CourseId, CourseRunStatus, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, CourseName, VenueId, ProviderCourseId, DeliveryMode, FlexibleStartDate, StartDate, CourseWebsite, Cost, CostDescription, DurationUnit, DurationValue, StudyMode, AttendancePattern, [National] ) VALUES ( @CourseRunId, @CourseId, {(int)CourseStatus.Live}, @CreatedOn, @CreatedByUserId, @CreatedOn, @CreatedByUserId, @CourseName, @VenueId, @ProviderCourseId, @DeliveryMode, @FlexibleStartDate, @StartDate, @CourseWebsite, @Cost, @CostDescription, @DurationUnit, @DurationValue, @StudyMode, @AttendancePattern, @National ) INSERT INTO Pttcd.CourseRunSubRegions (CourseRunId, RegionId) SELECT @CourseRunId, Value FROM @SubRegionIds SELECT 0 AS Result DECLARE @CourseRunIds Pttcd.GuidIdTable INSERT INTO @CourseRunIds VALUES (@CourseRunId) EXEC Pttcd.RefreshFindACourseIndex @CourseRunIds = @CourseRunIds, @Now = @CreatedOn"; var paramz = new { query.CourseId, query.CourseRunId, query.CourseName, query.DeliveryMode, query.FlexibleStartDate, query.StartDate, CourseWebsite = query.CourseUrl, query.Cost, query.CostDescription, query.DurationUnit, query.DurationValue, query.ProviderCourseId, query.National, SubRegionIds = TvpHelper.CreateStringTable(query.SubRegionIds ?? Enumerable.Empty <string>()), query.VenueId, query.AttendancePattern, query.StudyMode, CreatedByUserId = query.CreatedBy.UserId, query.CreatedOn, }; var result = await transaction.Connection.QuerySingleAsync <Result>(sql, paramz, transaction); if (result == Result.CourseNotFound) { return(new NotFound()); } else { return(new Success()); } }
public async Task <OneOf <NotFound, Success> > Execute(SqlTransaction transaction, UpdateCourseRun query) { var sql = $@" UPDATE Pttcd.CourseRuns SET CourseName = @CourseName, DeliveryMode = @DeliveryMode, FlexibleStartDate = @FlexibleStartDate, StartDate = @StartDate, CourseWebsite = @CourseUrl, Cost = @Cost, CostDescription = @CostDescription, DurationUnit = @DurationUnit, DurationValue = @DurationValue, ProviderCourseId = @ProviderCourseId, [National] = @National, VenueId = @VenueId, AttendancePattern = @AttendancePattern, StudyMode = @StudyMode, UpdatedBy = @UpdatedByUserId, UpdatedOn = @UpdatedOn, DataIsHtmlEncoded = 0 WHERE CourseRunId = @CourseRunId AND CourseRunStatus = {(int)CourseStatus.Live} IF @@ROWCOUNT = 0 BEGIN SELECT 1 AS Result RETURN END MERGE Pttcd.CourseRunSubRegions AS target USING (SELECT Value FROM @SubRegionIds) AS source ON target.CourseRunId = @CourseRunId AND target.RegionId = source.Value WHEN NOT MATCHED THEN INSERT (CourseRunId, RegionId) VALUES (@CourseRunId, source.Value) WHEN NOT MATCHED BY SOURCE AND target.CourseRunId = @CourseRunId THEN DELETE; DECLARE @CourseRunIds Pttcd.GuidIdTable INSERT INTO @CourseRunIds VALUES (@CourseRunId) EXEC Pttcd.RefreshFindACourseIndex @CourseRunIds = @CourseRunIds, @Now = @UpdatedOn SELECT 0 AS Result"; var paramz = new { query.CourseRunId, query.CourseName, query.DeliveryMode, query.FlexibleStartDate, query.StartDate, query.CourseUrl, query.Cost, query.CostDescription, query.DurationUnit, query.DurationValue, query.ProviderCourseId, query.National, SubRegionIds = TvpHelper.CreateStringTable(query.SubRegionIds ?? Array.Empty <string>()), query.VenueId, query.AttendancePattern, query.StudyMode, UpdatedByUserId = query.UpdatedBy.UserId, query.UpdatedOn }; var result = await transaction.Connection.QuerySingleAsync <Result>(sql, paramz, transaction); if (result == Result.Success) { return(new Success()); } else { return(new NotFound()); } }