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"); } }