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