private void button4_Click(object sender, EventArgs e)
{
StockNo stock = new StockNo();
ArrayList arr = stock.GetAllStock();
ArrayList SqlArr = new ArrayList();
DataTable tb = new DataTable();
SQLiteDataAdapter ad = new SQLiteDataAdapter("select * from Stock",ConfigurationSettings.AppSettings["connectionString"]);
ad.Fill(tb);
foreach (StockNo s in arr)
{
if (tb.Select("StockId='" + s.StockNumber + "'").Length == 0)
{
SqlArr.Add("insert into Stock(StockId,StockName,UpdateDate,StockType) values('" + s.StockNumber + "','" + s.StockName + "','" + DateTime.Now.ToString("yyyyMMdd") + "','" + s.StockType + "')");
}
else
{
SqlArr.Add("update Stock set UpdateDate='" + DateTime.Now.ToString("yyyyMMdd") + "' where StockId='" + s.StockNumber + "'");
}
}
SqlArr.Add("delete from stock where UpdateDate <> '" + DateTime.Now.ToString("yyyyMMdd") + "'");
using (SQLiteConnection conn = new SQLiteConnection(ConfigurationSettings.AppSettings["connectionString"]))
{
conn.Open();
SQLiteTransaction trans = conn.BeginTransaction();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
try
{
foreach (string sql in SqlArr)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
trans.Rollback();
}
}
}
}
public ArrayList GetAllStock()
{
// 下載台灣上市、上櫃公司代號
MemoryStream ms = getMemoryStreamByUrl("http://isin.twse.com.tw/isin/C_public.jsp?strMode=2");
// 使用預設編碼讀入 HTML
HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
doc.Load(ms, Encoding.Default);
// 裝載第一層查詢結果
HtmlAgilityPack.HtmlDocument docStockContext = new HtmlAgilityPack.HtmlDocument();
//HtmlNodeCollection nodes = doc.DocumentNode.SelectNodes("//*[contains(@bgcolor,'#FAFAD2')]"); //上市
HtmlNodeCollection nodes = doc.DocumentNode.SelectNodes("//table")[1].SelectNodes("tr");
int ret = 0;
string str = "";
ArrayList stockNo = new ArrayList();
StockNo s = null;
for (int i = 2; i < nodes.Count; i++)
{
HtmlNodeCollection tds = nodes[i].SelectNodes("td");
if (tds[0].InnerText.Replace(" ", "").Replace(" ", "") == "上市認購(售)權證") break;
foreach (HtmlNode td in tds)
{
str = td.InnerText.Replace(" ", "").Replace(" ", "");
if (str == "") continue;
if(str.IndexOf("/") >=0 ) continue;
if (str == "上市認購(售)權證") break;
if (!int.TryParse(str[0].ToString(), out ret)) continue;
if (stockNo.Contains(str.Substring(0, 4))) continue;
else stockNo.Add(str.Substring(0, 4));
s = new StockNo();
s.StockNumber = str.Substring(0, 4);
s.StockName = str.Substring(4);
s.Status = "A";
s.StockType = "1";
if (!stockArr.Contains(s))
stockArr.Add(s);
}
}
ms.Close();
ms.Dispose();
ms = getMemoryStreamByUrl("http://isin.twse.com.tw/isin/C_public.jsp?strMode=4");
doc = new HtmlAgilityPack.HtmlDocument();
doc.Load(ms, Encoding.Default);
// 裝載第一層查詢結果
docStockContext = new HtmlAgilityPack.HtmlDocument();
//nodes = doc.DocumentNode.SelectNodes("//*[contains(@bgcolor,'#FAFAD2')]"); //上櫃
nodes = doc.DocumentNode.SelectNodes("//table")[1].SelectNodes("tr");
bool isFind = false;
for (int i = 2; i < nodes.Count; i++)
{
HtmlNodeCollection tds = nodes[i].SelectNodes("td");
if (tds.Count == 1 && tds[0].InnerText.Trim() == "股票")
{
isFind = true;
continue;
}
else if(tds.Count == 1 && tds[0].InnerText.Trim() != "股票") isFind = false;
if (!isFind) continue;
foreach (HtmlNode td in tds)
{
str = td.InnerText.Replace(" ", "").Replace(" ", "");
if (str == "") continue;
if (!int.TryParse(str[0].ToString(), out ret)) continue;
if (stockNo.Contains(str.Substring(0, 4))) continue;
else stockNo.Add(str.Substring(0, 4));
s = new StockNo();
s.StockNumber = str.Substring(0, 4);
s.StockName = str.Substring(4);
s.Status = "A";
s.StockType = "2";
if (!stockArr.Contains(s))
stockArr.Add(s);
}
}
ms.Close();
ms.Dispose();
//foreach (HtmlNode n in nodes)
//{
// s = new StockNo();
// str = n.InnerText.Replace(" ", "").Replace(" ", "");
// if (str == "") continue;
// if (str == "臺灣存託憑證") break;
// if (str == "股票")
// isFind = true;
// if (!int.TryParse(str[0].ToString(), out ret)) continue;
// if (!isFind) continue;
// if (stockNo.Contains(str.Substring(0, 4))) continue;
// else stockNo.Add(str.Substring(0, 4));
// s.StockNumber = str.Substring(0, 4);
// s.StockName = str.Substring(4);
// s.Status = "A";
// s.StockType = "2";
// if (!stockArr.Contains(s))
// stockArr.Add(s);
//}
//ms.Close();
//ms.Dispose();
return stockArr;
}
