static public void updateAll()
{
List <News_Source> insertData = new List <News_Source>();
var connection = SqliteHelper.DBContext("NewsRss_URL.db");
using (connection)
{
connection.Open();
//Read the newly inserted data
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());
insertData.AddRange(tempList);
}
}
}
post(insertData);
}
//
// Check for leveling up
//
private static Character check(Character ch)
{
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
var nextEXP = String.Empty;
using (connection)
{
connection.DefaultTimeout = 60;
connection.Open();
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = String.Format(@"SELECT * FROM Exp_Dtl WHERE Lv = {0}", ch.Lv);
using (var reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
nextEXP = reader["EXP"].ToString();
}
}
connection.Close();
}
if (Convert.ToInt32(nextEXP) <= Convert.ToInt32(ch.EXP))
{
ch.Lv = (Convert.ToInt32(ch.Lv) + 1).ToString();
}
return(ch);
}
static public void post()
{
int[] baseWght = new int[] { 200, 500, 2000, 5000, 30000, 100000, 300000, 1000000, 5000000, 20000000 };
double weight = 2.85, baseLv = 1;
string EXP = String.Empty;
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
using (connection){
connection.DefaultTimeout = 60;
//Insert initial exp data
connection.Open();
var insertCmd = connection.CreateCommand();
for (int lv = 1; lv < 100; lv++)
{
if (lv % 10 == 0)
{
baseLv++;
weight = weight * baseLv * 1.14;
}
EXP = ((long)(lv * weight * baseWght[(int)baseLv - 1])).ToString();
insertCmd.CommandText = String.Format(@"INSERT INTO Exp_Dtl (Lv, EXP) VALUES ('{0}', '{1}')", lv, EXP);
insertCmd.ExecuteNonQuery();
}
}
}
static public void post()
{
var csvFile = SqliteHelper.CsvContext("RoomData.csv");
using (csvFile){
using (var csv = new CsvReader(csvFile, System.Globalization.CultureInfo.CurrentCulture))
{
// Get room data
var roomData = csv.GetRecords <Room>();
// Connect to db
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
using (connection){
connection.DefaultTimeout = 60;
//Insert initial room data
connection.Open();
var insertCmd = connection.CreateCommand();
foreach (var rm in roomData)
{
insertCmd.CommandText = String.Format(@"INSERT INTO Ro_Dtl (
Ro_No, Ro_Lv, Ro_Nm, Ro_Type,
Ro_Bos_No, Mns_No, Pre_Ro_No, Child_Ro_No
) VALUES (
'{0}', '{1}', '{2}', {3},
'{4}', '{5}', '{6}', '{7}')",
rm.Ro_No, rm.Ro_Lv, rm.Ro_Nm, rm.Ro_Type,
rm.Ro_Bos_No, rm.Mns_No, rm.Pre_Ro_No, rm.Child_Ro_No
);
insertCmd.ExecuteNonQuery();
}
}
}
}
}
//
// Create Monster Detail Table
//
static private void Mns_Dtl_Table()
{
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.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 Mns_Dtl(
Mns_No VARCHAR(255) PRIMARY KEY,
Mns_Nm VARCHAR(100),
Mns_Type INTEGER,
Lv VARCHAR(100),
HP VARCHAR(255),
MP VARCHAR(255),
ATK INTEGER,
MATK INTEGER,
Critical INTEGER,
DEF INTEGER,
MDEF INTEGER,
ASPD INTEGER,
re_EXP VARCHAR(255),
re_Mon VARCHAR(255))"
);
createTableCmd.ExecuteNonQuery();
}
}
//
// 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);
}
public static void update(Character ch)
{
ch = check(ch);
//Character ch = new Character();
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
using (connection)
{
connection.DefaultTimeout = 60;
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var updateCmd = connection.CreateCommand();
updateCmd.CommandText = String.Format(
@"UPDATE Ch_Dtl SET Lv = '{0}', EXP = '{1}' WHERE Ch_No = {2}", ch.Lv, ch.EXP, ch.Ch_No);
updateCmd.ExecuteNonQuery();
try{
transaction.Commit();
}
catch (Exception ex) {
//Console.WriteLine("{0}", ex.ToString());
}
}
}
}
//
// Account Validation
//
public static Boolean validate(ref Account act)
{
string userId = String.Empty, userPw = String.Empty, userMon = String.Empty, chNo = String.Empty;
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
using (connection)
{
connection.DefaultTimeout = 60;
connection.Open();
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = String.Format(@"SELECT * FROM Act_Dtl WHERE Act_Nm = '{0}'", act.Act_Nm);
using (var reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
userId = reader["Act_Id"].ToString();
userPw = reader["Act_Pw"].ToString();
userMon = reader["Act_Mon"].ToString();
chNo = reader["Act_Ch_No"].ToString();
}
}
}
if (act.Act_Pw == userPw)
{
act.Act_Id = userId;
act.Act_Mon = userMon;
act.Act_Ch_No = chNo;
return(true);
}
else
{
return(false);
}
}
//
// 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);
}
}
}
}
//
// Get Account detail
//
public static Account get(string userId)
{
Account act = new Account();
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
using (connection)
{
connection.DefaultTimeout = 60;
connection.Open();
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = String.Format(@"SELECT * FROM Act_Dtl WHERE Act_Id = '{0}'", userId);
using (var reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
act.Act_Id = reader["Act_Id"].ToString();
act.Act_Nm = reader["Act_Nm"].ToString();
act.Act_Pw = reader["Act_Pw"].ToString();
act.Act_Mon = reader["Act_Mon"].ToString();
act.Act_Ch_No = reader["Act_Ch_No"].ToString();
}
}
}
return(act);
}
//
// Get All Feeds Table Name
//
static public List <News_Source> get()
{
var connection = SqliteHelper.DBContext("NewsFeeds_Main.db");
using (connection)
{
connection.Open();
//Read the newly inserted data:
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = "SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%'";
List <News_Source> result = new List <News_Source>();
using (var reader = selectCmd.ExecuteReader())
{
var i = 1;
while (reader.Read())
{
var message = String.Format("{0})", i) + reader["name"];
Console.WriteLine(message);
i++;
result.Add(new News_Source()
{
title = reader["name"].ToString()
});
}
}
return(result);
}
}
//
// Create Character
//
public static void create(Character ch, ref Account act)
{
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
string lastNo = String.Empty;
using (connection)
{
connection.DefaultTimeout = 60;
connection.Open();
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = @"SELECT * FROM Ch_Dtl ORDER BY Ch_No DESC LIMIT 1";
using (var reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
lastNo = reader["Ch_No"].ToString();
}
}
lastNo = String.IsNullOrEmpty(lastNo) ? "1" : (Convert.ToInt32(lastNo) + 1).ToString();
using (var transaction = connection.BeginTransaction())
{
var insertCmd = connection.CreateCommand();
insertCmd.CommandText = String.Format(
@"INSERT INTO Ch_Dtl (
Ch_No, Ch_Nm, Ch_Type, Lv, EXP,
HP, MP, ATK, MATK,
Critical, DEF, MDEF,
STR, INT, VIT, AGI,
DEX, LUK, ASPD
)VALUES(
'{0}', '{1}', {2}, '{3}', '{4}',
'{5}', '{6}', {7}, {8},
{9}, {10}, {11},
{12}, {13}, {14}, {15},
{16}, {17}, {18})",
lastNo, ch.Ch_Nm, 0, "1", "0",
"100", "100", 10, 1,
5, 1, 1,
1, 1, 1, 1,
1, 1, 1);
insertCmd.ExecuteNonQuery();
var updateCmd = connection.CreateCommand();
updateCmd.CommandText = String.Format(@"UPDATE Act_Dtl SET Act_Ch_No = {0} WHERE Act_Id = {1}", lastNo, act.Act_Id);
updateCmd.ExecuteNonQuery();
act.Act_Ch_No = lastNo;
try{
transaction.Commit();
}
catch (Exception ex) {
//Console.WriteLine("{0}", ex.ToString());
}
}
}
}
//
// 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();
}
}
//
// Initial route set
//
static public void post(BattleReport btrpt)
{
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
using (connection){
connection.DefaultTimeout = 60;
//Insert initial exp data
connection.Open();
var insertCmd = connection.CreateCommand();
insertCmd.CommandText = String.Format(@"INSERT INTO Ro_Rou( Ro_No, Ch_No) VALUES( '1', '{0}')", btrpt.Ch_Dtl.Ch_No);
insertCmd.ExecuteNonQuery();
}
}
//
// Create Exp Detail Table
//
static private void Exp_Dtl_Table()
{
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.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 Exp_Dtl(
Lv VARCHAR(100) PRIMARY KEY,
EXP VARCHAR(255))"
);
createTableCmd.ExecuteNonQuery();
}
}
//
// Create Account Detail Table
//
static private void Act_Dtl_Table()
{
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.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 Act_Dtl(
Act_Id INTEGER PRIMARY KEY AUTOINCREMENT,
Act_Nm VARCHAR(100),
Act_Pw VARCHAR(100),
Act_Mon INT,
Act_Ch_No VARCHAR(100))"
);
createTableCmd.ExecuteNonQuery();
}
}
//
// 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();
}
//
// Create Account
//
public static Boolean create(ref Account act)
{
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
using (connection)
{
connection.DefaultTimeout = 60;
connection.Open();
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = String.Format(@"SELECT * FROM Act_Dtl WHERE Act_Nm = '{0}'", act.Act_Nm);
using (var reader = selectCmd.ExecuteReader())
{
if (!reader.Read())
{
using (var transaction = connection.BeginTransaction())
{
var insertCmd = connection.CreateCommand();
insertCmd.CommandText = String.Format(
@"INSERT INTO Act_Dtl (
'Act_Nm', 'Act_Pw'
)VALUES(
'{0}', '{1}')"
, act.Act_Nm, act.Act_Pw);
insertCmd.ExecuteNonQuery();
try{
transaction.Commit();
}
catch (Exception ex) {}
}
return(true);
}
else
{
return(false);
}
}
}
}
//
// Get Character detail
//
public static Character get(string chId)
{
Character ch = new Character();
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
using (connection)
{
connection.DefaultTimeout = 60;
connection.Open();
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = String.Format(@"SELECT * FROM Ch_Dtl WHERE Ch_No = {0}", chId);
using (var reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
ch.Ch_No = reader["Ch_No"].ToString();
ch.Ch_Nm = reader["Ch_Nm"].ToString();
ch.Ch_Type = Convert.ToInt32(reader["Ch_Type"]);
ch.Lv = reader["Lv"].ToString();
ch.EXP = reader["EXP"].ToString();
ch.HP = reader["HP"].ToString();
ch.MP = reader["MP"].ToString();
ch.ATK = Convert.ToInt32(reader["ATK"]);
ch.MATK = Convert.ToInt32(reader["MATK"]);
ch.Critical = Convert.ToInt32(reader["Critical"]);
ch.DEF = Convert.ToInt32(reader["DEF"]);
ch.MDEF = Convert.ToInt32(reader["MDEF"]);
ch.STR = Convert.ToInt32(reader["STR"]);
ch.INT = Convert.ToInt32(reader["INT"]);
ch.VIT = Convert.ToInt32(reader["VIT"]);
ch.AGI = Convert.ToInt32(reader["AGI"]);
ch.DEX = Convert.ToInt32(reader["DEX"]);
ch.LUK = Convert.ToInt32(reader["LUK"]);
ch.ASPD = Convert.ToInt32(reader["ASPD"]);
}
}
}
return(ch);
}
//
// Get room Detail
//
static public Room get(BattleReport btrpt)
{
Room room = new Room();
string roNo = String.Empty;
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
using (connection)
{
connection.DefaultTimeout = 60;
connection.Open();
var selectRouteCmd = connection.CreateCommand();
selectRouteCmd.CommandText = String.Format(@"SELECT * FROM Ro_Rou WHERE Ch_No = '{0}'", btrpt.Ch_Dtl.Ch_No);
using (var readerRoute = selectRouteCmd.ExecuteReader())
{
while (readerRoute.Read())
{
roNo = readerRoute["Ro_No"].ToString();
}
}
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = String.Format(@"SELECT * FROM Ro_Dtl WHERE Ro_No = '{0}'", roNo);
using (var reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
room.Ro_No = reader["Ro_No"].ToString();
room.Ro_Nm = reader["Ro_Nm"].ToString();
room.Ro_Type = Convert.ToInt32(reader["Ro_Type"]);
room.Mns_No = reader["Mns_No"].ToString();
room.Ro_Bos_No = reader["Ro_Bos_No"].ToString();
room.Ro_Lv = reader["Ro_Lv"].ToString();
room.Pre_Ro_No = reader["Pre_Ro_No"].ToString();
room.Child_Ro_No = reader["Child_Ro_No"].ToString();
}
}
}
return(room);
}
static private void Ro_Dtl_Table()
{
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.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 Ro_Dtl(
Ro_No VARCHAR(100) PRIMARY KEY,
Ro_Lv VARCHAR(100),
Ro_Nm VARCHAR(100),
Ro_Type INT,
Ro_Bos_No VARCHAR(100),
Mns_No VARCHAR(100),
Pre_Ro_No VARCHAR(100),
Child_Ro_No VARCHAR(100))"
);
createTableCmd.ExecuteNonQuery();
}
}
static public void post()
{
var csvFile = SqliteHelper.CsvContext("MonsterData.csv");
using (csvFile){
using (var csv = new CsvReader(csvFile, System.Globalization.CultureInfo.CurrentCulture))
{
// Get monster data .csv
var monsterData = csv.GetRecords <Monster>();
// Connnect to db
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
using (connection){
connection.DefaultTimeout = 60;
// Insert initial monster data
connection.Open();
var insertCmd = connection.CreateCommand();
foreach (var mns in monsterData)
{
insertCmd.CommandText = String.Format(@"INSERT INTO Mns_Dtl (
Mns_No, Mns_Nm, Mns_Type,
Lv, HP, MP, ATK, MATK,
Critical, DEF, MDEF,
ASPD, re_EXP, re_Mon
) VALUES (
'{0}', '{1}', {2},
'{3}', '{4}', '{5}', {6}, {7},
{8}, {9}, {10},
{11}, '{12}', '{13}')",
mns.Mns_No, mns.Mns_Nm, mns.Mns_Type,
mns.Lv, mns.HP, mns.MP, mns.ATK, mns.MATK,
mns.Critical, mns.DEF, mns.MDEF,
mns.ASPD, mns.re_EXP, mns.re_Mon);
insertCmd.ExecuteNonQuery();
}
}
}
}
}
//
// Get room Detail
//
static public BattleReport get(BattleReport btrpt)
{
Monster mns = new Monster();
// Clear Monster Pre-Data
btrpt.Mns_Dtl.Clear();
var connection = SqliteHelper.DBContext("HsiuHsien_MainDB.db");
using (connection)
{
connection.DefaultTimeout = 60;
connection.Open();
var selectRouteCmd = connection.CreateCommand();
selectRouteCmd.CommandText = String.Format(@"SELECT * FROM Mns_Dtl WHERE Mns_No = '{0}'", btrpt.room.Mns_No);
using (var readerRoute = selectRouteCmd.ExecuteReader())
{
while (readerRoute.Read())
{
mns.Mns_No = readerRoute["Mns_No"].ToString();
mns.Mns_Nm = readerRoute["Mns_Nm"].ToString();
mns.Mns_Type = Convert.ToInt32(readerRoute["Mns_Type"]);
mns.Lv = readerRoute["Lv"].ToString();
mns.HP = readerRoute["HP"].ToString();
mns.MP = readerRoute["MP"].ToString();
mns.ATK = Convert.ToInt32(readerRoute["ATK"]);
mns.MATK = Convert.ToInt32(readerRoute["MATK"]);
mns.Critical = Convert.ToInt32(readerRoute["Critical"]);
mns.DEF = Convert.ToInt32(readerRoute["DEF"]);
mns.MDEF = Convert.ToInt32(readerRoute["MDEF"]);
mns.ASPD = Convert.ToInt32(readerRoute["ASPD"]);
mns.re_EXP = readerRoute["re_EXP"].ToString();
mns.re_Mon = readerRoute["re_Mon"].ToString();
}
}
btrpt.Mns_Dtl.Add(mns);
}
return(btrpt);
}
//
// Get News Feeds List
//
static public void getFeeds()
{
var connection = SqliteHelper.DBContext("NewsRss_URL.db");
using (connection)
{
connection.Open();
//Read the newly inserted data:
var selectCmd = connection.CreateCommand();
selectCmd.CommandText = "SELECT * FROM NewsRssURL";
using (var reader = selectCmd.ExecuteReader())
{
var i = 1;
while (reader.Read())
{
var message = String.Format("{0})", i) + reader["title"] + "\n URL:" + reader["rssURL"];
Console.WriteLine(message);
i++;
}
}
}
}
//
// Create News Feed Table and Insert Data
//
static public void post(List <News_Source> newsdata)
{
var connection = SqliteHelper.DBContext("NewsFeeds_Main.db");
using (connection)
{
updateSts(newsdata);
connection.Open();
//Create a table (drop if already exists first):
newsdata.ForEach(o => {
var titleOs_res = YCLib.stringHandler(o.title);
var createTableCmd = connection.CreateCommand();
createTableCmd.CommandText = String.Format(@"CREATE TABLE IF NOT EXISTS {0}(
news_Id INTEGER PRIMARY KEY,
pubDt VARCHAR(100),
title VARCHAR(255),
summary VARCHAR(255),
url VARCHAR(255))"
, titleOs_res);
try{
createTableCmd.ExecuteNonQuery();
}
catch (Exception ex) {
Console.WriteLine("{0}", ex.ToString());
}
//Get Lst news date
var lstDt = getLstDt(o) == "" ? "1911-01-01 00:00" : getLstDt(o);
//Seed some data:
using (var transaction = connection.BeginTransaction())
{
var titleOs = String.Empty;
var summaryOs = String.Empty;
var urlOs = String.Empty;
var insertCmd = connection.CreateCommand();
var count = 0;
o.newsList.ForEach(item => {
if (DateTime.Compare(Convert.ToDateTime(item.pubDate), Convert.ToDateTime(lstDt)) > 0)
{
titleOs = item.title.Replace("'", "''");
summaryOs = item.summary.Replace("'", "''");
urlOs = item.url.Replace("'", "''");
insertCmd.CommandText = String.Format(@"INSERT INTO {4} (pubDt , title, summary, url) VALUES('{0}', '{1}', '{2}', '{3}')",
item.pubDate, titleOs, summaryOs, urlOs, titleOs_res);
insertCmd.ExecuteNonQuery();
count++;
}
});
try{
transaction.Commit();
if (count == 0)
{
Console.WriteLine("NOTICE: Nothing update - {0}.", titleOs_res);
}
else
{
Console.WriteLine("SUCCESS: Inserted {0} news in {1}.", count, titleOs_res);
}
}
catch (Exception ex) {
Console.WriteLine("{0}", ex.ToString());
}
}
});
}
}
//
// 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());
}
}
;
}
});
}
}
