private void button5_Click(object sender, EventArgs e)
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
db.DBUpdate("create table personal(id integer, name text);", null);
}
}
// 日経平均チャート用データ
public List <DollarYenEntity> GetListDollarYenEntity(DateTime beginDate, DateTime endDate)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" SELECT ");
sb.AppendLine(" d.ExchangeDate ");
sb.AppendLine(" , d.OpeningPrice ");
sb.AppendLine(" , d.LowPrice ");
sb.AppendLine(" , d.HighPrice ");
sb.AppendLine(" , d.ClosingPrice ");
sb.AppendLine(" FROM ");
sb.AppendLine(" dollaryen d ");
sb.AppendLine(" WHERE d.ExchangeDate BETWEEN :BeginDate AND :EndDate ");
sb.AppendLine(" ORDER BY ");
sb.AppendLine(" d.ExchangeDate ");
List <DollarYenEntity> ListDollarYen = new List <DollarYenEntity>();
using (Utility.DbUtil db = new Utility.DbUtil())
{
// データを取得してインスタンス変数に保持
ListDollarYen = db.DBSelect <DollarYenEntity>(sb.ToString(), new { BeginDate = beginDate, EndDate = endDate });
}
return(ListDollarYen);
}
// 日経平均チャート用データ
public List <NikkeiAverageEntity> GetListNikkeiAverage(DateTime beginDate, DateTime endDate)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" SELECT ");
sb.AppendLine(" n.StockDate ");
sb.AppendLine(" , n.OpeningPrice ");
sb.AppendLine(" , n.LowPrice ");
sb.AppendLine(" , n.HighPrice ");
sb.AppendLine(" , n.ClosingPrice ");
sb.AppendLine(" FROM ");
sb.AppendLine(" nikkeiaverage n ");
sb.AppendLine(" WHERE n.StockDate BETWEEN :BeginDate AND :EndDate ");
sb.AppendLine(" ORDER BY ");
sb.AppendLine(" n.StockDate ");
List <NikkeiAverageEntity> ListNikkeiAverage = new List <NikkeiAverageEntity>();
using (Utility.DbUtil db = new Utility.DbUtil())
{
// データを取得してインスタンス変数に保持
ListNikkeiAverage = db.DBSelect <NikkeiAverageEntity>(sb.ToString(), new { BeginDate = beginDate, EndDate = endDate });
}
return(ListNikkeiAverage);
}
private List <Utility.DividendEntity> GetDividendList()
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
return(db.DBSelect <Utility.DividendEntity>("SELECT * FROM dividend "));
}
}
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);
}
// 配当データをSqliteから取得
this.dgvDividend.DataSource = GetDividendList();
}
private void registerProfile(List <Utility.ProfileEntity> listProfile)
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
string deleteSql = @"DELETE FROM profile
WHERE StockCode = :StockCode
";
db.DBInsert(deleteSql, new { StockCode = listProfile.Min(stock => stock.StockCode) });
string insertSql = @"INSERT INTO profile
(
StockCode
,CompanyName
,Feature
,ConcatenationBusiness
,HeadquartersLocation
,IndustriesCategory
,FoundationDate
,MarketName
,ListedDate
,ClosingMonth
,UnitShares
,EmployeeNumberSingle
,EmployeeNumberConcatenation
,AvarageAnnualIncome
) VALUES (
:StockCode
,:CompanyName
,:Feature
,:ConcatenationBusiness
,:HeadquartersLocation
,:IndustriesCategory
,:FoundationDate
,:MarketName
,:ListedDate
,:ClosingMonth
,:UnitShares
,:EmployeeNumberSingle
,:EmployeeNumberConcatenation
,:AvarageAnnualIncome
)";
db.DBInsert(insertSql, listProfile);
}
}
private void registerStockPrice(List <Utility.StockPriceEntity> listStockPrice)
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
string deleteSql = @"DELETE FROM stockprice
WHERE StockCode = :StockCode
AND StockDate BETWEEN :StockDateFrom AND :StockDateTo
";
DateTime StockDateFrom = listStockPrice.Min(stock => stock.StockDate);
DateTime StockDateTo = listStockPrice.Max(stock => stock.StockDate);
db.DBInsert(deleteSql, new { StockCode = listStockPrice.Min(stock => stock.StockCode), StockDateFrom = StockDateFrom, StockDateTo = StockDateTo });
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, listStockPrice);
}
}
// 株価チャート用のデータ
public List <StockPriceProfile> GetListStockPriceProfileFilterTempTable(DateTime beginDate, DateTime endDate)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" SELECT ");
sb.AppendLine(" p.StockCode ");
sb.AppendLine(" , p.CompanyName ");
sb.AppendLine(" , p.IndustriesCategory ");
sb.AppendLine(" , p.MarketName ");
sb.AppendLine(" , p.ClosingMonth ");
sb.AppendLine(" , s.StockDate ");
sb.AppendLine(" , s.OpeningPrice ");
sb.AppendLine(" , s.LowPrice ");
sb.AppendLine(" , s.HighPrice ");
sb.AppendLine(" , s.ClosingPrice ");
sb.AppendLine(" , s.TradeVolume ");
sb.AppendLine(" , s.AdjustmentClosingPrice ");
sb.AppendLine(" FROM ");
sb.AppendLine(" stockprice s ");
sb.AppendLine(" INNER JOIN profile p ");
sb.AppendLine(" ON s.StockCode = p.StockCode ");
sb.AppendLine(" INNER JOIN temptable t ");
sb.AppendLine(" ON s.StockCode = t.StockCode ");
sb.AppendLine(" WHERE s.StockDate BETWEEN :BeginDate AND :EndDate ");
sb.AppendLine(" ORDER BY ");
sb.AppendLine(" s.StockCode ");
sb.AppendLine(" , s.StockDate ");
List <StockPriceProfile> ListStockPriceProfile = new List <StockPriceProfile>();
using (Utility.DbUtil db = new Utility.DbUtil())
{
// データを取得してインスタンス変数に保持
ListStockPriceProfile = db.DBSelect <StockPriceProfile>(sb.ToString(), new { BeginDate = beginDate, EndDate = endDate });
}
return(ListStockPriceProfile);
}
// テーブルを作成する
private void CreateTables()
{
// dividend 配当
// dollaryen ドル／円
// nikkeiaverage 日経平均
// profile プロフィール
// stockprice 株価
string sql = "";
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)
{
// なければ作成
sql = @"CREATE TABLE stockprice
(
StockCode NUMERIC
,CompanyName TEXT
,StockDate TEXT
,OpeningPrice NUMERIC
,HighPrice NUMERIC
,LowPrice NUMERIC
,ClosingPrice NUMERIC
,TradeVolume NUMERIC
,AdjustmentClosingPrice NUMERIC
,primary key(StockCode,StockDate)
) ";
db.DBExecuteSQL(sql);
}
// テーブルを検索
tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'dividend'");
if (tblLst[0] == 0)
{
sql = @"CREATE TABLE dividend
(
OrderNo NUMERIC
,StockCode NUMERIC
,Market TEXT
,CompanyName TEXT
,Dividend NUMERIC
,DividendYield NUMERIC
,DetailUrl TEXT
,primary key(StockCode)
) ";
db.DBExecuteSQL(sql);
}
// テーブルを検索
tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'profile'");
if (tblLst[0] == 0)
{
sql = @"CREATE TABLE profile
(
StockCode NUMERIC
,CompanyName TEXT
,Feature TEXT
,ConcatenationBusiness TEXT
,HeadquartersLocation TEXT
,IndustriesCategory TEXT
,FoundationDate TEXT
,MarketName TEXT
,ListedDate TEXT
,ClosingMonth NUMERIC
,UnitShares NUMERIC
,EmployeeNumberSingle NUMERIC
,EmployeeNumberConcatenation NUMERIC
,AvarageAnnualIncome NUMERIC
,primary key(StockCode)
) ";
db.DBExecuteSQL(sql);
}
// テーブルを検索
tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'dollaryen'");
if (tblLst[0] == 0)
{
sql = @"CREATE TABLE dollaryen
(
ExchangeDate TEXT
,OpeningPrice NUMERIC
,HighPrice NUMERIC
,LowPrice NUMERIC
,ClosingPrice NUMERIC
,primary key(ExchangeDate)
) ";
db.DBExecuteSQL(sql);
}
// テーブルを検索
tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'nikkeiaverage'");
if (tblLst[0] == 0)
{
sql = @"CREATE TABLE nikkeiaverage
(
StockDate TEXT
,OpeningPrice NUMERIC
,HighPrice NUMERIC
,LowPrice NUMERIC
,ClosingPrice NUMERIC
,primary key(StockDate)
) ";
db.DBExecuteSQL(sql);
}
// テーブルを検索
tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'temptable'");
if (tblLst[0] == 0)
{
sql = @"CREATE TABLE temptable
(
StockCode NUMERIC
,CompanyName TEXT
,primary key(StockCode)
) ";
db.DBExecuteSQL(sql);
}
// テーブルを検索
tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'stockcode'");
if (tblLst[0] == 0)
{
sql = @"CREATE TABLE stockcode
(
StockCode NUMERIC
,primary key(StockCode)
) ";
db.DBExecuteSQL(sql);
}
// テーブルを検索
tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'pricenumbering'");
if (tblLst[0] == 0)
{
// なければ作成
sql = @"CREATE TABLE pricenumbering
(
StockCode NUMERIC
,CompanyName TEXT
,StockDate TEXT
,RowNum NUMERIC
,OpeningPrice NUMERIC
,HighPrice NUMERIC
,LowPrice NUMERIC
,ClosingPrice NUMERIC
,TradeVolume NUMERIC
,AdjustmentClosingPrice NUMERIC
,primary key(StockCode,StockDate)
) ";
db.DBExecuteSQL(sql);
}
}
}
private void btnCreateTable_Click(object sender, EventArgs e)
{
if (MessageBox.Show("テーブルを作成しますか？", "更新確認", MessageBoxButtons.YesNo) == DialogResult.No)
{
return;
}
using (Utility.DbUtil db = new Utility.DbUtil())
{
#region stockpriceテーブル
// テーブルを作成
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
,primary key(StockCode,StockDate)
) ";
db.DBExecuteSQL(sql);
#endregion
#region dividendテーブル
// テーブルを作成
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 ");
}
sql = @"CREATE TABLE dividend
(
OrderNo NUMERIC
,StockCode NUMERIC
,Market TEXT
,CompanyName TEXT
,Dividend NUMERIC
,DividendYield NUMERIC
,DetailUrl TEXT
,primary key(StockCode)
) ";
db.DBExecuteSQL(sql);
#endregion
#region profileテーブル
// テーブルを作成
tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'profile'");
if (tblLst[0] > 0)
{
db.DBExecuteSQL("DROP TABLE profile ");
}
sql = @"CREATE TABLE profile
(
StockCode NUMERIC
,CompanyName TEXT
,Feature TEXT
,ConcatenationBusiness TEXT
,HeadquartersLocation TEXT
,IndustriesCategory TEXT
,FoundationDate TEXT
,MarketName TEXT
,ListedDate TEXT
,ClosingMonth NUMERIC
,UnitShares NUMERIC
,EmployeeNumberSingle NUMERIC
,EmployeeNumberConcatenation NUMERIC
,AvarageAnnualIncome NUMERIC
,primary key(StockCode)
) ";
db.DBExecuteSQL(sql);
#endregion
#region dollaryenテーブル
// テーブルを作成
tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'dollaryen'");
if (tblLst[0] > 0)
{
db.DBExecuteSQL("DROP TABLE dollaryen ");
}
sql = @"CREATE TABLE dollaryen
(
ExchangeDate TEXT
,OpeningPrice NUMERIC
,HighPrice NUMERIC
,LowPrice NUMERIC
,ClosingPrice NUMERIC
,primary key(ExchangeDate)
) ";
db.DBExecuteSQL(sql);
#endregion
#region nikkeiaverageテーブル
// テーブルを作成
tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'nikkeiaverage'");
if (tblLst[0] > 0)
{
db.DBExecuteSQL("DROP TABLE nikkeiaverage ");
}
sql = @"CREATE TABLE nikkeiaverage
(
StockDate TEXT
,OpeningPrice NUMERIC
,HighPrice NUMERIC
,LowPrice NUMERIC
,ClosingPrice NUMERIC
,primary key(StockDate)
) ";
db.DBExecuteSQL(sql);
#endregion
}
MessageBox.Show("テーブル作成完了");
}
