//
// Get NewsFeeds Lastest Update Date
//
static public string getLstDt(News_Source newsdata)
{
var connection = SqliteHelper.DBContext("NewsFeeds_Main.db");
var result = string.Empty;
var titleOs = YCLib.stringHandler(newsdata.title);
using (connection)
{
connection.Open();
//Read the newly inserted data:
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = String.Format("SELECT * FROM {0} ORDER BY pubDt Desc LIMIT 1", titleOs);
using (var reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
result = reader["pubDt"].ToString();
}
}
}
return(result);
}
//
// Search news detail
//
static public void get(News_Source newsdata)
{
var connection = SqliteHelper.DBContext("NewsFeeds_Main.db");
var titleOs = YCLib.stringHandler(newsdata.title);
using (connection)
{
connection.Open();
//Read the newly inserted data:
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = String.Format("SELECT * FROM {0} ORDER BY pubDt DESC LIMIT 20", titleOs);
using (var reader = selectCmd.ExecuteReader())
{
var count = 1;
Console.Clear();
while (reader.Read())
{
var message = string.Format("{0})", count) + reader["pubDt"] + " " + reader["title"] + "\n";// + "summary:" + reader["summary"];
count++;
Console.WriteLine(message);
}
}
}
}
//
// Drop Target News table
//
static public void delete(News_Source newsdata)
{
var connection = SqliteHelper.DBContext("NewsFeeds_Main.db");
using (connection)
{
connection.Open();
var titleOs_res = newsdata.title.Replace(" ", "_").Replace("-", "_").Replace("|", "").Replace("__", "_");
var delTableCmd = connection.CreateCommand();
delTableCmd.CommandText = String.Format(@"DROP TABLE IF EXISTS {0}", titleOs_res);
delTableCmd.ExecuteNonQuery();
}
}
//
// 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());
}
}
;
}
});
}
}
