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();
}