private void DLHistoricalDataB_Click(object sender, EventArgs e)
{
bool validname = false;
bool ready = false;
if (MyUtility.isValidName(StockTableName.Text))
{
validname = true;
}
else
{
MessageBox.Show("Stock Table name can not nither start with number nor include special charachter");
StockNameTxtBox.Text = "Stock Name";
StockTableName.Text = "Stock Table";
}
if (!ss.TableIsExist(StockTableName.Text) && validname)
{
DialogResult dialogResult = MessageBox.Show("Table \"" + StockTableName.Text + "\" does not exist would you like to create it ?", "Table does not found", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
if (ss.CreateHistoricalTable(StockTableName.Text))
{
HistLabel.Text = "Table " + StockTableName.Text + " created successfully";
ListViewItem lvl = new ListViewItem(StockNameTxtBox.Text);
lvl.SubItems.Add(StockTableName.Text);
lvl.SubItems.Add(DateTime.Now.ToShortDateString());
listView2.Items.Add(lvl);
ss.insert("insert into Histoorical_Stock_Table values ('" + StockNameTxtBox.Text + "', '" + StockTableName.Text + "' ,'" + DateTime.Now.ToShortDateString() + "');");
ready = true;
}
else
{
HistLabel.Text = "Creating table terminated due, \n(Invalid Stock Table Name)";
}
}
else if (dialogResult == DialogResult.No)
{
HistLabel.Text = "Action Terminated";
}
}
else if (ss.TableIsExist(StockTableName.Text))
{
ready = true;
}
if (ready)
{
///////////// sql initial command's
SqlConnection conn;
string connection_string = "Data Source=HOSEIN-PC;Initial Catalog=FYP1;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
conn = new SqlConnection(connection_string);
////////////////////////// end of sql commands, starting of webclient commands
try
{
string downloadedStrings;
System.Net.WebClient client;
client = new System.Net.WebClient();
downloadedStrings = client.DownloadString("http://ichart.finance.yahoo.com/table.csv?s=" + StockNameTxtBox.Text);//hgvbap2b4c8d2e7f6jg3g6j1");
string[] temp = downloadedStrings.Split('\n');
///////////////////////// end of webclient commands
/*the temp.Length -1 should be put instead of temp.Length because we did
* not count the first element .
*/
HistLabel.Text = "Inserting";
HistLabel.Refresh();
int counters = 0;
bool Succed = true;
for (int i = 1; i < temp.Length - 1; i++)
{
try
{
string[] innerTemp = temp[i].Split(',');
DateTime mydate = Convert.ToDateTime(innerTemp[0]);
decimal? Open = MyUtility.getDecimal(innerTemp[1]);
decimal? High = MyUtility.getDecimal(innerTemp[2]);
decimal? Low = MyUtility.getDecimal(innerTemp[3]);
decimal? Close = MyUtility.getDecimal(innerTemp[4]);
decimal? Volume = MyUtility.getDecimal(innerTemp[5]);
decimal? Adj_Close = MyUtility.getDecimal(innerTemp[6]);
SqlCommand cmd = new SqlCommand(
"INSERT INTO " + StockTableName.Text + " VALUES(@Date_,@Open_,@High,@Low,@Close_,@Volume,@Adj_Close);"
, conn);
cmd.Parameters.Add("@Date_", SqlDbType.Date);
cmd.Parameters["@Date_"].Value = mydate.ToShortDateString();
cmd.Parameters.Add("@Open_", SqlDbType.Decimal);
cmd.Parameters["@Open_"].Value = Open;
cmd.Parameters.Add("@High", SqlDbType.Decimal);
cmd.Parameters["@High"].Value = High;
cmd.Parameters.Add("@Low", SqlDbType.Decimal);
cmd.Parameters["@Low"].Value = Low;
cmd.Parameters.Add("@Close_", SqlDbType.Decimal);
cmd.Parameters["@Close_"].Value = Close;
cmd.Parameters.Add("@Volume", SqlDbType.Decimal);
cmd.Parameters["@Volume"].Value = Volume;
cmd.Parameters.Add("@Adj_Close", SqlDbType.Decimal);
cmd.Parameters["@Adj_Close"].Value = Adj_Close;
try
{
conn.Open();
Int32 rowsAffected = cmd.ExecuteNonQuery();
counters++;
// Console.WriteLine("RowsAffected: {0}", rowsAffected);
conn.Close();
}
catch (Exception ex)
{
// Console.WriteLine(ex.Message);
//MessageBox.Show(ex.Data.ToString());
HistLabel.Text = counters + " Rows has been added";
HistLabel.Refresh();
// MessageBox.Show(ex.HResult.ToString());
MessageBox.Show(ex.ToString());
Succed = false;
conn.Close();
break;
//DisplaySqlErrors(ex);
}
//Int32 rowsAffected = cmd.ExecuteNonQuery();
//MessageBox.Show("done!");
}
catch (IndexOutOfRangeException) // Error 001
{
MessageBox.Show("Contact administrator Technical Error 001 / Form1 ");
HistLabel.Text = "Contact Admin /Error 001";
HistLabel.Refresh();
}
}
if (Succed)
{
HistLabel.Text = "Inserting done successfully,\n" + counters + " rows are added";
}
}
catch (System.Net.WebException)
{
MessageBox.Show("Check Stock name symbol and try again (Stock Name)");
StockNameTxtBox.Text = "Stock Name";
StockTableName.Text = "Stock Table";
}
finally
{
// update time !
}
}
}
private void Createbutton_Click(object sender, EventArgs e)
{
try {
if (!MyUtility.isValidName(DataSetName.Text))
{
throw new Exception();
}
if (this.listView1.SelectedItems.Count == 0)
{
MessageBox.Show("Please select a stock from Historical data table", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string insetrquery = "insert into analyse_lists values('" + this.listView1.SelectedItems[0].SubItems[0].Text.ToString() + "','" + DataSetName.Text.ToString() + "','" + dateStart.Value.ToShortDateString() + "','" + dateEnd.Value.ToShortDateString() + "')";
//ss.insert(insetrquery);
if (ss.insert(insetrquery))
{
ListViewItem lvl = new ListViewItem(DataSetName.Text.ToString());
lvl.SubItems.Add(this.listView1.SelectedItems[0].SubItems[0].Text.ToString());
lvl.SubItems.Add(dateStart.Value.ToShortDateString());
lvl.SubItems.Add(dateEnd.Value.ToShortDateString());
listView2.Items.Add(lvl);
}
//Loading Load = new Loading();
//Load.label1.Text = "Calculating MACD ... ";
//Load.Show();
TimeSeries myview = new CreateView(this.dateStart.Value.Date, this.dateEnd.Value.Date, HistoTextBox.Text, DataSetName.Text);
TimeSeries MAC = new MACDTEMP(this.dateStart.Value.Date, this.dateEnd.Value.Date, HistoTextBox.Text, DataSetName.Text);
//Load.label1.Text = "Calculating Stocastic Occilator ... ";
TimeSeries STO = new STOCTEMP(this.dateStart.Value.Date, this.dateEnd.Value.Date, HistoTextBox.Text, DataSetName.Text);
// Load.label1.Text = "Calculating RSI ... ";
TimeSeries RSI = new RSITEMP(this.dateStart.Value.Date, this.dateEnd.Value.Date, HistoTextBox.Text, DataSetName.Text);
//Load.label1.Text = "Calculating GMMA ... ";
TimeSeries GMMA = new GMMATEMP(this.dateStart.Value.Date, this.dateEnd.Value.Date, HistoTextBox.Text, DataSetName.Text);
string query =
//view = "+DataSetName+"_View
//MACDTEMP = "+DataSetName+"_MACD
//Sto = "+DataSetName+"_STO
//RSI = "+DataSetName+"_RSI
//GMMA = "+DataSetName+"_GMMA
// YLTView = "+DataSetName+"_AllView
"create view " + DataSetName.Text + "_AllView as " +
"select " + DataSetName.Text + "_View.Date_, " + DataSetName.Text + "_View.Open_, " + DataSetName.Text + "_View.High, " + DataSetName.Text + "_View.Low, " + DataSetName.Text + "_View.Close_, " + DataSetName.Text + "_View.Volume, " + DataSetName.Text + "_View.Adj_Close," +
"" + DataSetName.Text + "_MACD._12_Days_Ema ," + DataSetName.Text + "_MACD._26_Days_Ema , " + DataSetName.Text + "_MACD.MACD_12Minus26_days, " + DataSetName.Text + "_MACD._Signal, " + DataSetName.Text + "_MACD._histogram, " +
"" + DataSetName.Text + "_RSI.Change," + DataSetName.Text + "_RSI.gain," + DataSetName.Text + "_RSI.Loss, " + DataSetName.Text + "_RSI.Avg_Gain, " + DataSetName.Text + "_RSI.Avg_Loss," + DataSetName.Text + "_RSI.RS," + DataSetName.Text + "_RSI._14_days_RSI, " +
"" + DataSetName.Text + "_STO.highest_high_14, " + DataSetName.Text + "_STO.Lowest_low_14, " + DataSetName.Text + "_STO._14_day_StochasticOscillator, " +
"" + DataSetName.Text + "_GMMA._3_days_Ema," + DataSetName.Text + "_GMMA._5_days_Ema," + DataSetName.Text + "_GMMA._8_days_Ema," + DataSetName.Text + "_GMMA._10_days_Ema," + DataSetName.Text + "_GMMA._15_days_Ema," + DataSetName.Text + "_GMMA._30_days_Ema," + DataSetName.Text + "_GMMA._35_days_Ema, " +
"" + DataSetName.Text + "_GMMA._40_days_Ema," + DataSetName.Text + "_GMMA._45_days_Ema," + DataSetName.Text + "_GMMA._50_days_Ema," + DataSetName.Text + "_GMMA._60_days_Ema " +
" from " + DataSetName.Text + "_View, " + DataSetName.Text + "_MACD , " + DataSetName.Text + "_RSI , " + DataSetName.Text + "_GMMA, " + DataSetName.Text + "_STO where " + DataSetName.Text + "_View.ID= " + DataSetName.Text + "_MACD.ID and " + DataSetName.Text + "_View.ID = " + DataSetName.Text + "_RSI.ID and " + DataSetName.Text + "_View.ID =" + DataSetName.Text + "_GMMA.ID and " + DataSetName.Text + "_View.ID = " + DataSetName.Text + "_STO.ID";
ss.createView(query);
//MAC.createTable();
//MAC.dropMACD();
//Load.Close();
}
catch (Exception)
{
MessageBox.Show("Invalid Table name");
}
}
