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