public IList<Site> ListSites() { if (SiteCache.Current.IsCached()) return SiteCache.Current.Items; IList<Site> list = new List<Site>(); try { using (SqliteConnection connection = new SqliteConnection(FeedConnectionString)) { connection.Open(); using (SqliteCommand command = new SqliteCommand(connection)) { command.CommandText = "SELECT id,title,url FROM sites"; using (SqliteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Site site = new Site(); site.Id = reader.GetGuid(0); site.Title = reader.GetString(1); site.Url = reader.GetString(2); list.Add(site); } } } } } catch (SqliteException e) { Logger.Warn("SqliteException occured while listing sites: \n{0}", e); } SiteCache.Current.Update(list); return list; }
public IList<Feed> ListFeeds() { if (FeedCache.Current.IsCached()) return FeedCache.Current.Items; IList<Feed> list = new List<Feed>(); try { using (SqliteConnection connection = new SqliteConnection(FeedConnectionString)) { connection.Open(); using (SqliteCommand command = new SqliteCommand(connection)) { command.CommandText = @"SELECT f.*,c.title as categoryTitle,s.title as siteTitle, s.url as siteUrl " + "FROM feeds f INNER JOIN categories c ON UPPER(c.id) = UPPER(f.categoryid) " + "INNER JOIN sites s on UPPER(s.id) = UPPER(f.siteid)"; using (SqliteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Category category = new Category(); category.Id = (Guid)reader["categoryid"]; category.Title = (string)reader["categoryTitle"]; Site site = new Site(); site.Id = (Guid)reader["siteid"]; site.Title = (string)reader["siteTitle"]; site.Url = (string)reader["siteUrl"]; Feed feed = new Feed(); feed.Id = (Guid)reader["id"]; feed.Category = category; feed.Site = site; feed.Cleaner = (string)reader["cleaner"]; feed.Url = (string)reader["url"]; long type = (long)reader["type"]; feed.FeedType = (FeedType)type; list.Add(feed); } } } } } catch (SqliteException e) { Logger.Warn("SqliteException occured while listing feeds: \n{0}", e); } FeedCache.Current.Update(list); return list; }