public static List<Feed> LoadAll() { List<Feed> results = new List<Feed>(); String sqlFeeds = @" SELECT * FROM Feeds; "; using (SQLiteConnection m_dbConnection = new SQLiteConnection(Repository.ConnectionString)) { m_dbConnection.Open(); SQLiteCommand command = new SQLiteCommand(sqlFeeds, m_dbConnection); using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Feed feed = new Feed(); feed.Location = new Uri(reader["uri"].ToString()); String lastUpd = reader["last_update"].ToString(); if (String.IsNullOrEmpty(lastUpd)) lastUpd = DateTime.Now.ToString("s"); feed.LastUpdate = DateTime.Parse(lastUpd); feed.Host = feed.Location.Host; feed.Title = reader["title"].ToString(); feed.Id = (long)reader["feed_id"]; results.Add(feed); } } } foreach(Feed f in results) { f.Articles = new List<Model.Article>(Article.LoadByFeed(f)); } return results.ToList(); }
public static List<Article> LoadByFeed(Feed f) { List<Article> results = new List<Article>(); String sqlArticles = @" SELECT * FROM Articles WHERE feed_id = ( SELECT feed_id FROM Feeds WHERE uri = $uri ); "; using (SQLiteConnection m_dbConnection = new SQLiteConnection(Repository.ConnectionString)) { m_dbConnection.Open(); SQLiteCommand command = new SQLiteCommand(sqlArticles, m_dbConnection); command.Parameters.AddWithValue("$uri", f.Location.ToString()); using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Article art = new Article(); art.Location = new Uri(reader["uri"].ToString()); art.PublishDate = DateTime.Parse(reader["published_date"].ToString()); art.Title = reader["title"].ToString(); art.Unread = !(reader["unread"].ToString().Equals("0")); art.ParentFeed = f; results.Add(art); } } } return results.ToList(); }