public void Find_DiaryEntry_By_TitlePart() { var connectionManager = new ConnectionManager(DbFile.GetConnectionString("testdb.db")); var diaryEntryRepository = new DiaryEntryRepository(connectionManager); diaryEntryRepository.InsertOne(new DiaryEntry { DateCreated = DateTime.Parse("2017/03/12 13:12:21").Ticks, Title = "Diary Entry 1" }); diaryEntryRepository.InsertOne(new DiaryEntry { DateCreated = DateTime.Parse("2017/03/15 08:01:59").Ticks, Title = "Diary Entry 2" }); var findCriteria = new DiaryEntryFindCriteria("Entry 1"); IEnumerable <DiaryEntry> foundEntries = diaryEntryRepository.Find(findCriteria); Assert.That(foundEntries.Count(), Is.EqualTo(1)); }
//public IEnumerable<DiaryEntry> Find(string v) //{ // return this.GetMany<DiaryEntry>(new DiaryEntry { Title = v }, $"select * from DiaryEntry where Title LIKE '%{v}%'"); //} // Important little tutorial on how to use inbuilt sql lite date/time functions for storage. // http://www.sqlitetutorial.net/sqlite-date/ public IEnumerable <DiaryEntry> Find(DiaryEntryFindCriteria findCriteria) { string sql = ""; List <string> filterList = new List <string>(); sql = $"select * from DiaryEntry\n" + "where \n"; if (findCriteria.StartDate != null && findCriteria.EndDate != null) { filterList.Add( "DateCreated BETWEEN \n" + $"{findCriteria.StartDate.Value.Ticks} and {findCriteria.EndDate.Value.Ticks}"); } else if (findCriteria.StartDate != null && findCriteria.EndDate == null) { filterList.Add( "DateCreated >= \n" + $"{findCriteria.StartDate.Value.Ticks}"); } else if (findCriteria.StartDate == null && findCriteria.EndDate != null) { filterList.Add( "DateCreated < \n" + $"{findCriteria.EndDate.Value.Ticks}"); } if (!string.IsNullOrWhiteSpace(findCriteria.Title)) { filterList.Add($"Title LIKE '%{findCriteria.Title}%'"); } sql = sql + string.Join("AND", filterList); return(this.GetMany <DiaryEntry>(null, sql)); }