public ResultDto PostScholasticData(ScholasticDto scholasticDto) { scholasticDto.GuidedReading = scholasticDto.GuidedReading.Trim(); scholasticDto.Dra = scholasticDto.Dra.Trim(); var verificationResult = VerifyScholasticDto(scholasticDto); //if (verificationResult.ResultCode < 0) // return verificationResult; var result = new ResultDto { ResultDescription = "" }; using (var context = new BookcaveEntities()) { var bookRecords = context.Database.SqlQuery<BookRecord>("Select * from BookRecords where Isbn13 = {0}", scholasticDto.Isbn13); if (bookRecords.FirstOrDefault() == null) return new ResultDto { ResultDescription = "no general info exists yet for " + scholasticDto.Isbn13 }; var skillRecords = context.Database.SqlQuery<SkillRecord>("Select * from SkillRecords where Isbn13 = {0}", scholasticDto.Isbn13); var currentSkillRecord = skillRecords.FirstOrDefault(); Mapper.CreateMap<ScholasticDto, SkillRecord>(); if (currentSkillRecord != null) { var modifiedSkillRecord = Mapper.Map<ScholasticDto, SkillRecord>(scholasticDto, currentSkillRecord); var averageSkillAge = DataFunctions.GetAverageSkillAge(modifiedSkillRecord); context.Database.ExecuteSqlCommand("update SkillRecords set ScholasticGrade={0},Dra={1},GuidedReading={2},AverageSkillAge={3} where Isbn13={4}", scholasticDto.ScholasticGrade, scholasticDto.Dra, scholasticDto.GuidedReading, averageSkillAge, scholasticDto.Isbn13); result.ResultDescription += scholasticDto.Isbn13 + " skills updated "; } else { var newSkillRecord = Mapper.Map<ScholasticDto, SkillRecord>(scholasticDto); newSkillRecord.AverageSkillAge = DataFunctions.GetAverageSkillAge(newSkillRecord); context.SkillRecords.Add(newSkillRecord); result.ResultDescription += scholasticDto.Isbn13 + " skills added "; } var contentRecords = context.Database.SqlQuery<ContentRecord>("Select * from ContentRecords where Isbn13 = {0}", scholasticDto.Isbn13); var currentContentRecord = contentRecords.FirstOrDefault(); Mapper.CreateMap<ScholasticDto, ContentRecord>(); if (currentContentRecord != null) { var modifiedContentRecord = Mapper.Map<ScholasticDto, ContentRecord>(scholasticDto, currentContentRecord); var averageContentAge = DataFunctions.GetAverageContentAge(modifiedContentRecord); context.Database.ExecuteSqlCommand("update ContentRecords set ScholasticGradeLower={0}, ScholasticGradeHigher={1},AverageContentAge={2} where Isbn13={3}", scholasticDto.ScholasticGradeLower, scholasticDto.ScholasticGradeHigher, averageContentAge, scholasticDto.Isbn13); context.SaveChanges(); result.ResultDescription += scholasticDto.Isbn13 + " contents updated"; } else { var newContentRecord = Mapper.Map<ScholasticDto, ContentRecord>(scholasticDto); context.ContentRecords.Add(newContentRecord); context.SaveChanges(); result.ResultDescription += scholasticDto.Isbn13 + " contents added"; } //try //{ //} //catch (DbEntityValidationException e) { Console.WriteLine(e.Message); } //catch (DbUpdateException e) { Console.WriteLine(e.Message); } } return result; }
public ResultDto PostCommonSenseData(CommonSenseDto commonSenseDto) { var result = new ResultDto { ResultDescription = "" }; using (var context = new BookcaveEntities()) { var bookRecords = context.Database.SqlQuery<BookRecord>("Select * from BookRecords where Isbn13 = {0}", commonSenseDto.Isbn13); if (bookRecords.FirstOrDefault() == null) return new ResultDto { ResultDescription = "no general info exists yet for " + commonSenseDto.Isbn13 }; var contentRecords = context.Database.SqlQuery<ContentRecord>("Select * from ContentRecords where Isbn13 = {0}", commonSenseDto.Isbn13); var currentContentRecord = contentRecords.FirstOrDefault(); Mapper.CreateMap<CommonSenseDto, ContentRecord>(); if (currentContentRecord != null) { var modifiedContentRecord = Mapper.Map<CommonSenseDto, ContentRecord>(commonSenseDto, currentContentRecord); var aggregateContent = DataFunctions.GetAverageContentAge(modifiedContentRecord); context.Database.ExecuteSqlCommand ( "update ContentRecords set CommonSensePause={0},CommonSenseOn={1},CommonSenseNoKids={2},AverageContentAge={3} where Isbn13={4}", commonSenseDto.CommonSensePause, commonSenseDto.CommonSenseOn, commonSenseDto.CommonSenseNoKids, aggregateContent, commonSenseDto.Isbn13 ); context.SaveChanges(); result.ResultDescription += commonSenseDto.Isbn13 + " contents updated"; } else { var newContentRecord = Mapper.Map<CommonSenseDto, ContentRecord>(commonSenseDto); newContentRecord.AverageContentAge = DataFunctions.GetAverageContentAge(newContentRecord); context.ContentRecords.Add(newContentRecord); context.SaveChanges(); result.ResultDescription += commonSenseDto.Isbn13 + " contents added"; } } return result; }
/// <summary> /// Adds or updates a single book in the books database /// </summary> /// <param name="lexileDto">book to add</param> /// <returns></returns> public ResultDto PostLexileData(LexileDto lexileDto) { if (!LexileRecordVerified(lexileDto)) return new ResultDto { ResultDescription = "bad object" }; var result = new ResultDto { ResultDescription = "" }; Mapper.CreateMap<LexileDto, SkillRecord>(); using (var context = new BookcaveEntities()) { //var recordQuery = context.SkillRecords.Where(book => book.Isbn13.Equals(lexileDto.Isbn13)); var recordQuery = context.Database.SqlQuery<SkillRecord>("Select * from SkillRecords where Isbn13 = {0}", lexileDto.Isbn13); //var currentSkillRecord = skillRecords.FirstOrDefault(); var skillRecords = new List<SkillRecord>(recordQuery); SkillRecord currentSkillRecord = null; if (skillRecords.Count > 0) currentSkillRecord = skillRecords.First(); if (currentSkillRecord != null) { var modifiedSkillRecord = Mapper.Map<LexileDto, SkillRecord>(lexileDto, currentSkillRecord); var aggregateSkill = DataFunctions.GetAverageSkillAge(modifiedSkillRecord); context.Database.ExecuteSqlCommand ( "update SkillRecords set LexScore={0},LexCode={1},LexUpdate={2},AverageSkillAge={3} where Isbn13={4}", lexileDto.LexScore, lexileDto.LexCode, lexileDto.LexUpdate, aggregateSkill, lexileDto.Isbn13 ); result.ResultDescription += lexileDto.Isbn13 + " skills updated "; } else { var newSkillRecord = Mapper.Map<LexileDto, SkillRecord>(lexileDto); newSkillRecord.AverageSkillAge = DataFunctions.GetAverageSkillAge(newSkillRecord); context.SkillRecords.Add(newSkillRecord); result.ResultDescription += lexileDto.Isbn13 + " skills added "; } Mapper.CreateMap<LexileDto, BookRecord>(); var bookQuery = context.Database.SqlQuery<BookRecord>("Select * from BookRecords where Isbn13 = {0}", lexileDto.Isbn13); //var bookQuery = context.BookRecords.Where(book => book.Isbn13.Equals(lexileDto.Isbn13)); var bookRecords = new List<BookRecord>(bookQuery); BookRecord currentBookRecord = null; if (bookRecords.Count > 0) currentBookRecord = bookRecords[0]; //var currentBookRecord = bookRecords.FirstOrDefault(); if (currentBookRecord != null) { //currentBookRecord = Mapper.Map<LexileDto, BookRecord>(lexileDto, currentBookRecord); context.Database.ExecuteSqlCommand ( "update BookRecords set Title={0},Author={1},Publisher={2},PageCount={3},DocType={4},Series={5},Awards={6},Summary={7} where Isbn13={8}", lexileDto.Title, lexileDto.Author, lexileDto.Publisher, lexileDto.PageCount, lexileDto.DocType, lexileDto.Series, lexileDto.Awards, lexileDto.Summary, lexileDto.Isbn13 ); context.SaveChanges(); result.ResultDescription += lexileDto.Isbn13 + " books added "; } else { var newBookRecord = Mapper.Map<LexileDto, BookRecord>(lexileDto); context.BookRecords.Add(newBookRecord); context.SaveChanges(); result.ResultDescription += lexileDto.Isbn13 + " books added "; } //try //{ //} //catch (DbEntityValidationException e) { Console.WriteLine(e.Message); } //catch (DbUpdateException e) { Console.WriteLine(e.Message); } } return result; }
public ResultDto PostBarnesData(BarnesDto barnesDto) { ResultDto result = new ResultDto { ResultDescription = "" }; using (var context = new BookcaveEntities()) { var bookRecords = context.Database.SqlQuery<BookRecord>("Select * from BookRecords where Isbn13 = {0}", barnesDto.Isbn13); if (bookRecords.FirstOrDefault() == null) return new ResultDto { ResultDescription = "no general info exists yet for " + barnesDto.Isbn13 }; var ratingRecords = context.Database.SqlQuery<RatingRecord>("Select * from RatingRecords where Isbn13 = {0}", barnesDto.Isbn13); RatingRecord currentRatingRecord = null; if (ratingRecords.Count() > 0) currentRatingRecord = ratingRecords.FirstOrDefault(); Mapper.CreateMap<BarnesDto, RatingRecord>(); if (currentRatingRecord != null) { var modifiedRatingRecord = Mapper.Map<BarnesDto, RatingRecord>(barnesDto, currentRatingRecord); context.Database.ExecuteSqlCommand("update RatingRecords set BarnesAvg={0} where Isbn13={1}", barnesDto.BarnesAvg, barnesDto.Isbn13); result.ResultDescription += barnesDto.Isbn13 + " ratings updated "; } else { var newRatingRecord = Mapper.Map<BarnesDto, RatingRecord>(barnesDto); context.RatingRecords.Add(newRatingRecord); result.ResultDescription += barnesDto.Isbn13 + " ratings added "; } var contentRecords = context.Database.SqlQuery<ContentRecord>("Select * from ContentRecords where Isbn13 = {0}", barnesDto.Isbn13); ContentRecord currentContentRecord = null; if (contentRecords.Count() > 0) currentContentRecord = contentRecords.FirstOrDefault(); Mapper.CreateMap<BarnesDto, ContentRecord>(); if (currentContentRecord != null) { var modifiedContentRecord = Mapper.Map(barnesDto, currentContentRecord); var averageContentAge = DataFunctions.GetAverageContentAge(modifiedContentRecord); context.Database.ExecuteSqlCommand ( "update ContentRecords set BarnesAgeYoung={0},BarnesAgeOld={1},AverageContentAge={2} where Isbn13={3}", barnesDto.BarnesAgeYoung, barnesDto.BarnesAgeOld, averageContentAge, barnesDto.Isbn13 ); context.SaveChanges(); result.ResultDescription += barnesDto.Isbn13 + " contents updated"; } else { var newContentRecord = Mapper.Map<BarnesDto, ContentRecord>(barnesDto); newContentRecord.AverageContentAge = DataFunctions.GetAverageContentAge(newContentRecord); context.ContentRecords.Add(newContentRecord); context.SaveChanges(); result.ResultDescription += barnesDto.Isbn13 + " contents added"; } //try //{ //} //catch (DbUpdateException e) //{ // Console.WriteLine("book metadata wasn't found in primary table."); // return new ResultDto { ResultDescription = e.Message }; //} } return result; }