//
// Send Lastest News URL to telegram
//
static public void send()
{
List <News_Source> newsdata = new List <News_Source>();
var connection = SqliteHelper.DBContext("NewsRss_URL.db");
using (connection)
{
connection.Open();
//Get News Rss URL List
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = "SELECT * FROM NewsRssURL";
List <News_Source> tempList = new List <News_Source>();
RssReader rss = new RssReader();
using (var reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
tempList = rss.getNews(reader["rssURL"].ToString());
newsdata.AddRange(tempList);
}
}
}
newsdata.ForEach(o => {
var count = 0;
//Get lastest Date of News in Database
var lstDt = NewsFeeds.getLstDt(o) == "" ? "1911-01-01 00:00" : NewsFeeds.getLstDt(o);
//send data
o.newsList.ForEach(item => {
//only get newest news
if (DateTime.Compare(Convert.ToDateTime(item.pubDate), Convert.ToDateTime(lstDt)) > 0)
{
TelegramBot.send(item);
count++;
}
});
Console.WriteLine("{0} has {1} news", o.title, count);
});
NewsFeeds.updateAll();
}
//
// Add new news feeds in db
//
static public void updateSts(List <News_Source> newsdata)
{
var connection = SqliteHelper.DBContext("NewsRss_URL.db");
using (connection)
{
connection.DefaultTimeout = 60;
//If table not exist than create one
connection.Open();
var createTableCmd = connection.CreateCommand();
createTableCmd.CommandText = String.Format(@"CREATE TABLE IF NOT EXISTS NewsRssURL(
news_Id INTEGER PRIMARY KEY,
LstMntDt NUMERIC,
title VARCHAR(255),
rssURL VARCHAR(100))"
);
createTableCmd.ExecuteNonQuery();
// Get Last update datetime from NewsRssRUL
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = "SELECT * FROM NewsRssURL";
List <News_Source> tempList = new List <News_Source>();
RssReader rss = new RssReader();
using (var reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
News_Source temp = new News_Source {
LstMntDt = reader["LstMntDt"].ToString(),
title = reader["title"].ToString()
};
tempList.Add(temp);
}
}
newsdata.ForEach(o => {
using (var transaction = connection.BeginTransaction())
{
var LstMntDt_his = "1911-01-01 00:00";
var insertCmd = connection.CreateCommand();
var deleteCmd = connection.CreateCommand();
var titleOs_res = YCLib.stringHandler(o.title);
tempList.ForEach(x => {
if (x.title == titleOs_res)
{
LstMntDt_his = x.LstMntDt;
}
});
// Require to update NewsRssRUL or not
if (DateTime.Compare(Convert.ToDateTime(o.LstMntDt), Convert.ToDateTime(LstMntDt_his)) > 0)
{
deleteCmd.CommandText = String.Format(@"DELETE FROM NewsRssURL WHERE title = '{0}'", titleOs_res);
deleteCmd.ExecuteNonQuery();
insertCmd.CommandText = String.Format(@"INSERT INTO NewsRssURL (LstMntDt , title, rssURL) VALUES('{0}', '{1}', '{2}')",
o.LstMntDt, titleOs_res, o.rssURL);
insertCmd.ExecuteNonQuery();
try{
transaction.Commit();
}
catch (Exception ex) {
Console.WriteLine("{0}", ex.ToString());
}
}
;
}
});
}
}