private void btnDividend_Click(object sender, EventArgs e) { using (Utility.DbUtil db = new Utility.DbUtil()) { // テーブルを作成 List <decimal> tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'dividend'"); if (tblLst[0] > 0) { db.DBExecuteSQL("DROP TABLE dividend "); } string sql = @"CREATE TABLE dividend ( OrderNo NUMERIC ,StockCode NUMERIC ,Market TEXT ,CompanyName TEXT ,Dividend NUMERIC ,DividendYield NUMERIC ,DetailUrl TEXT ) "; db.DBExecuteSQL(sql); List <Utility.DividendEntity> list = new List <Utility.DividendEntity>(); Utility.FinanceUtil finance = new Utility.FinanceUtil(); list = finance.GetDividendEntityList(); string insertSql = @"INSERT INTO dividend ( OrderNo ,StockCode ,Market ,CompanyName ,Dividend ,DividendYield ,DetailUrl ) VALUES ( :OrderNo ,:StockCode ,:Market ,:CompanyName ,:Dividend ,:DividendYield ,:DetailUrl )"; db.DBInsert(insertSql, list); List <Utility.DividendEntity> dividend = db.DBSelect <Utility.DividendEntity>("SELECT * FROM dividend "); this.dgvStockPrice.DataSource = dividend; } }
private async Task UpdateDividend() { Stopwatch sw = new System.Diagnostics.Stopwatch(); sw.Start(); this.txtUpdateStatus.Text = ""; this.txtUpdateStatus.Text += "配当利回り取得開始" + Environment.NewLine; // 情報の取得 List <Utility.DividendEntity> list = new List <Utility.DividendEntity>(); await Task.Run(() => { Utility.FinanceUtil finance = new Utility.FinanceUtil(); list = finance.GetDividendEntityList(); }); sw.Stop(); this.txtUpdateStatus.Text += "配当利回り取得終了 " + sw.Elapsed.ToString() + Environment.NewLine; sw.Restart(); this.txtUpdateStatus.Text += "配当利回り更新開始" + Environment.NewLine; await Task.Run(() => { using (Utility.DbUtil db = new Utility.DbUtil()) { // 削除 db.DBExecuteSQL("DELETE FROM dividend"); // 登録 string insertSql = @"INSERT INTO dividend ( OrderNo ,StockCode ,Market ,CompanyName ,Dividend ,DividendYield ,DetailUrl ) VALUES ( :OrderNo ,:StockCode ,:Market ,:CompanyName ,:Dividend ,:DividendYield ,:DetailUrl )"; db.DBInsert(insertSql, list); db.DBExecuteSQL("DELETE FROM dividend WHERE StockCode NOT IN (SELECT TargetStockCode From targetcode) "); } }); sw.Stop(); this.txtUpdateStatus.Text += "配当利回り更新終了 " + sw.Elapsed.ToString() + Environment.NewLine; }
private async Task UpdateStockCode() { Stopwatch sw = new System.Diagnostics.Stopwatch(); sw.Start(); this.txtUpdateStatus.Text = ""; this.txtUpdateStatus.Text += "証券コード取得開始" + Environment.NewLine; // 情報の取得 List <StockPriceEntity> list = new List <StockPriceEntity>(); await Task.Run(() => { Utility.FinanceUtil finance = new Utility.FinanceUtil(); list = finance.GetStockCodeList(); }); sw.Stop(); this.txtUpdateStatus.Text += "証券コード取得終了 " + sw.Elapsed.ToString() + Environment.NewLine; sw.Restart(); this.txtUpdateStatus.Text += "証券コード更新開始" + Environment.NewLine; await Task.Run(() => { using (Utility.DbUtil db = new Utility.DbUtil()) { // 削除 db.DBExecuteSQL("DELETE FROM stockcode"); // 登録 string insertSql = @"INSERT INTO stockcode ( StockCode ) VALUES ( :StockCode )"; db.DBInsert(insertSql, list); } }); sw.Stop(); this.txtUpdateStatus.Text += "証券コード更新終了 " + sw.Elapsed.ToString() + Environment.NewLine; }
private void btnStockPrice_Click(object sender, EventArgs e) { using (Utility.DbUtil db = new Utility.DbUtil()) { // テーブルを作成 List <decimal> tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'stockprice'"); if (tblLst[0] >= 0) { db.DBExecuteSQL("DROP TABLE stockprice "); } string sql = @"create table stockprice ( StockCode NUMERIC ,CompanyName TEXT ,StockDate TEXT ,OpeningPrice NUMERIC ,HighPrice NUMERIC ,LowPrice NUMERIC ,ClosingPrice NUMERIC ,TradeVolume NUMERIC ,AdjustmentClosingPrice NUMERIC ) "; db.DBExecuteSQL(sql); List <Utility.StockPriceEntity> list = new List <Utility.StockPriceEntity>(); Utility.FinanceUtil finance = new Utility.FinanceUtil(); list = finance.GetStockPriceEntityList(Convert.ToInt32(txtStockCode.Text), DateTime.Now.AddMonths(-3), DateTime.Now); string insertSql = @"INSERT INTO stockprice ( StockCode ,CompanyName ,StockDate ,OpeningPrice ,HighPrice ,LowPrice ,ClosingPrice ,TradeVolume ,AdjustmentClosingPrice ) VALUES ( :StockCode ,:CompanyName ,:StockDate ,:OpeningPrice ,:HighPrice ,:LowPrice ,:ClosingPrice ,:TradeVolume ,:AdjustmentClosingPrice )"; db.DBInsert(insertSql, list); List <Utility.StockPriceEntity> stockprice = db.DBSelect <Utility.StockPriceEntity>("SELECT * FROM stockprice "); this.dgvStockPrice.DataSource = stockprice; // チャート生成 CreateStockChart(stockprice); } }
private void btnNumbering_Click(object sender, EventArgs e) { // 情報の取得 List <Utility.StockPriceEntity> listStock = new List <Utility.StockPriceEntity>(); List <Utility.NumberingPriceEntity> listNum = new List <Utility.NumberingPriceEntity>(); using (Utility.DbUtil db = new Utility.DbUtil()) { listStock = db.DBSelect <StockPriceEntity>(@"SELECT stockcode.StockCode ,ifnull(profile.CompanyName,'') CompanyName FROM stockcode LEFT JOIN profile ON stockcode.StockCode = profile.StockCode ORDER BY stockcode.StockCode "); db.DBExecuteSQL("DELETE FROM pricenumbering"); } StringBuilder sb = new StringBuilder(); sb.Length = 0; sb.AppendLine(" SELECT "); sb.AppendLine(" StockCode "); sb.AppendLine(" ,CompanyName "); sb.AppendLine(" ,StockDate "); sb.AppendLine(" ,OpeningPrice "); sb.AppendLine(" ,HighPrice "); sb.AppendLine(" ,LowPrice "); sb.AppendLine(" ,ClosingPrice "); sb.AppendLine(" ,TradeVolume "); sb.AppendLine(" ,AdjustmentClosingPrice "); sb.AppendLine(" ,(SELECT COUNT(*) + 1 "); sb.AppendLine(" FROM stockprice "); sb.AppendLine(" WHERE StockCode = :StockCode "); sb.AppendLine(" AND StockDate >= :StockDate "); sb.AppendLine(" AND (StockDate < t.StockDate OR (StockDate = t.StockDate AND StockCode < t.StockCode )) "); sb.AppendLine(" ) AS RowNum "); sb.AppendLine(" FROM stockprice t "); sb.AppendLine("WHERE t.StockCode = :StockCode "); sb.AppendLine(" AND t.StockDate >= :StockDate "); foreach (StockPriceEntity r in listStock) { using (Utility.DbUtil db = new Utility.DbUtil()) { listNum = db.DBSelect <NumberingPriceEntity>(sb.ToString(), new { StockCode = r.StockCode, StockDate = DateTime.Now.AddMonths(-3).Date }); string insertSql = @"INSERT INTO pricenumbering ( StockCode ,CompanyName ,StockDate ,RowNum ,OpeningPrice ,HighPrice ,LowPrice ,ClosingPrice ,TradeVolume ,AdjustmentClosingPrice ) VALUES ( :StockCode ,:CompanyName ,:StockDate ,:RowNum ,:OpeningPrice ,:HighPrice ,:LowPrice ,:ClosingPrice ,:TradeVolume ,:AdjustmentClosingPrice )"; if (listNum.Count() > 0) { db.DBInsert(insertSql, listNum); } } } }