public void Display() { string xmlObject = ""; Nsye lastSearch = new Nsye(); //Get current user Id int Id = Global.thisUser.Id; string q = "select lastSearchAll from Users where Id = " + Id; //get user data DataTable dt1 = service.FetchProducts(q); foreach (DataRow row in dt1.Rows) { xmlObject = row["lastSearchAll"].ToString(); } //Check if user is new user! if (xmlObject == "none") { MessageBox.Show("Welcome ! Make your first history search in order to see last search on homepage"); } else { //Deserialize lastSearchAll (from current user) to Nyse object (lastSearch) XmlSerializer serializer = new XmlSerializer(typeof(Nsye)); using (TextReader reader = new StringReader(xmlObject)) { lastSearch = (Nsye)serializer.Deserialize(reader); } //CALL STORED PROCEDURE WHICH RETURN LAST SEARCH FROM CURRENT USER //using (SqlCommand cmd = new SqlCommand("SearchData", con)) //{ // cmd.CommandType = CommandType.StoredProcedure; // cmd.Parameters.Add("@exchange", SqlDbType.VarChar).Value = lastSearch.exchange; // cmd.Parameters.Add("@symbol", SqlDbType.VarChar).Value = lastSearch.stock_symbol; // cmd.Parameters.Add("@dateFrom", SqlDbType.VarChar).Value = lastSearch.date_from; // cmd.Parameters.Add("@dateTo", SqlDbType.VarChar).Value = lastSearch.date_to; // cmd.Parameters.Add("@priceOpenFrom", SqlDbType.VarChar).Value = lastSearch.stock_price_open_from; // cmd.Parameters.Add("@priceOpenTo", SqlDbType.VarChar).Value = lastSearch.stock_price_open_to; // cmd.Parameters.Add("@priceHighFrom", SqlDbType.VarChar).Value = lastSearch.stock_price_high_from; // cmd.Parameters.Add("@priceHighTo", SqlDbType.VarChar).Value = lastSearch.stock_price_high_to; // cmd.Parameters.Add("@priceCloseFrom", SqlDbType.VarChar).Value = lastSearch.stock_price_close_from; // cmd.Parameters.Add("@priceCloseTo", SqlDbType.VarChar).Value = lastSearch.stock_price_close_to; // cmd.Parameters.Add("@priceLowFrom", SqlDbType.VarChar).Value = lastSearch.stock_price_low_from; // cmd.Parameters.Add("@priceLowTo", SqlDbType.VarChar).Value = lastSearch.stock_price_low_to; // cmd.Parameters.Add("@adjFrom", SqlDbType.VarChar).Value = lastSearch.stock_price_adj_close_from; // cmd.Parameters.Add("@adjTo", SqlDbType.VarChar).Value = lastSearch.stock_price_adj_close_to; // cmd.Parameters.Add("@volumeFrom", SqlDbType.VarChar).Value = lastSearch.stock_volume_from; // cmd.Parameters.Add("@volumeTo", SqlDbType.VarChar).Value = lastSearch.stock_volume_to; // cmd.ExecuteNonQuery(); //Get last search data //DataTable dt2 = FetchProducts1(cmd); //Call WCF -> to db stored procedure for search history data DataTable dt2 = service.SearchData(lastSearch); //Insert data into datagrid_products dataTable.ItemsSource = dt2.DefaultView; dataTable.CanUserAddRows = false; } }
//FETCH All FROM DB / PUT IT IN DataTable x //used only for testing, before stored procedures were implemented //public DataTable FetchProducts(string query) //{ // SqlCommand cmd2 = con.CreateCommand(); // cmd2.CommandType = CommandType.Text; // cmd2.CommandText = query; // cmd2.ExecuteNonQuery(); // DataTable dt1 = new DataTable(); // SqlDataAdapter da1 = new SqlDataAdapter(cmd2); // da1.Fill(dt1); // return dt1; //} //Stored procedure fetch method //public DataTable FetchProducts1(SqlCommand cmd5) //{ // DataTable dt2 = new DataTable(); // SqlDataAdapter da1 = new SqlDataAdapter(cmd5); // da1.Fill(dt2); // return dt2; //} //SEARCH HANDLER private void SearchBtn_Click(object sender, RoutedEventArgs e) { try { //Store search inputs into Search (Nsye model) Search = new ServiceReference1.Nsye(); //GET ALL INPUTS, AND SET DEFAULT VALUES IF THERE IS NO INPUT //Exchange Search.exchange = exchangeInput.SelectedValue.ToString(); //Symbol Search.stock_symbol = symbolInput.SelectedValue.ToString(); //Open if (openFromInput.Text != "") { Search.stock_price_open_from = float.Parse(openFromInput.Text); } else { Search.stock_price_open_from = 1; } if (openToInput.Text != "") { Search.stock_price_open_to = float.Parse(openToInput.Text); } else { Search.stock_price_open_to = 20; } //Close if (closeInputFrom.Text != "") { Search.stock_price_close_from = float.Parse(closeInputFrom.Text); } else { Search.stock_price_close_from = 1; } if (closeInputTo.Text != "") { Search.stock_price_close_to = float.Parse(closeInputTo.Text); } else { Search.stock_price_close_to = 20; } //High if (highInputFrom.Text != "") { Search.stock_price_high_from = float.Parse(highInputFrom.Text); } else { Search.stock_price_high_from = 1; } if (highInputTo.Text != "") { Search.stock_price_high_to = float.Parse(highInputTo.Text); } else { Search.stock_price_high_to = 20; } //Low if (lowInputFrom.Text != "") { Search.stock_price_low_from = float.Parse(lowInputFrom.Text); } else { Search.stock_price_low_from = 1; } if (lowInputTo.Text != "") { Search.stock_price_low_to = float.Parse(lowInputTo.Text); } else { Search.stock_price_low_to = 20; } //Volume if (volumeInputFrom.Text != "") { Search.stock_volume_from = int.Parse(volumeInputFrom.Text); } else { Search.stock_volume_from = 1; } if (volumeInputTo.Text != "") { Search.stock_volume_to = int.Parse(volumeInputTo.Text); } else { Search.stock_volume_to = 9999999; } //Adj if (adjInputFrom.Text != "") { Search.stock_price_adj_close_from = float.Parse(adjInputFrom.Text); } else { Search.stock_price_adj_close_from = 1; } if (adjInputTo.Text != "") { Search.stock_price_adj_close_to = float.Parse(adjInputTo.Text); } else { Search.stock_price_adj_close_to = 20; } //Date if (dateFrom.SelectedDate != null) { Search.date_from = dateFrom.SelectedDate.Value.ToString("d/MM/yyyy"); } else { Search.date_from = "10/10/1999"; } if (dateTo.SelectedDate != null) { Search.date_to = dateTo.SelectedDate.Value.ToString("d/MM/yyyy"); } else { Search.date_to = "10/10/2020"; } //Return Search //string date_query = "SELECT * from nyse_history where convert(datetime, date, 103) between CONVERT(datetime, '"+Search.date_from+"', 103) and CONVERT(datetime,'"+Search.date_to+"')"; //string open_query = "SELECT * FROM nyse_history WHERE CONVERT(float, stock_price_open)" +" BETWEEN "+Search.stock_price_open_from+ " AND " +Search.stock_price_open_to; //string mix = "SELECT * from nyse_history where (convert(datetime, date, 103) between CONVERT(datetime, '" + Search.date_from + "', 103) and CONVERT(datetime,'" + Search.date_to + "')) " + // "AND (CONVERT(float, stock_price_open) BETWEEN " + Search.stock_price_open_from + " AND " + Search.stock_price_open_to+ // ") AND (CONVERT(float, stock_price_high) BETWEEN " + Search.stock_price_high_from + " AND " + Search.stock_price_high_to +")"; //CALL STORED PROCEDURE WHICH RETURN SEARCH FROM DB //using (SqlCommand cmd = new SqlCommand("SearchData", con)) //{ // cmd.CommandType = CommandType.StoredProcedure; // cmd.Parameters.Add("@exchange", SqlDbType.VarChar).Value = Search.exchange; // cmd.Parameters.Add("@symbol", SqlDbType.VarChar).Value = Search.stock_symbol; // cmd.Parameters.Add("@dateFrom", SqlDbType.VarChar).Value = Search.date_from; // cmd.Parameters.Add("@dateTo", SqlDbType.VarChar).Value = Search.date_to; // cmd.Parameters.Add("@priceOpenFrom", SqlDbType.VarChar).Value = Search.stock_price_open_from; // cmd.Parameters.Add("@priceOpenTo", SqlDbType.VarChar).Value = Search.stock_price_open_to; // cmd.Parameters.Add("@priceHighFrom", SqlDbType.VarChar).Value = Search.stock_price_high_from; // cmd.Parameters.Add("@priceHighTo", SqlDbType.VarChar).Value = Search.stock_price_high_to; // cmd.Parameters.Add("@priceCloseFrom", SqlDbType.VarChar).Value = Search.stock_price_close_from; // cmd.Parameters.Add("@priceCloseTo", SqlDbType.VarChar).Value = Search.stock_price_close_to; // cmd.Parameters.Add("@priceLowFrom", SqlDbType.VarChar).Value = Search.stock_price_low_from; // cmd.Parameters.Add("@priceLowTo", SqlDbType.VarChar).Value = Search.stock_price_low_to; // cmd.Parameters.Add("@adjFrom", SqlDbType.VarChar).Value = Search.stock_price_adj_close_from; // cmd.Parameters.Add("@adjTo", SqlDbType.VarChar).Value = Search.stock_price_adj_close_to; // cmd.Parameters.Add("@volumeFrom", SqlDbType.VarChar).Value = Search.stock_volume_from; // cmd.Parameters.Add("@volumeTo", SqlDbType.VarChar).Value = Search.stock_volume_to; // cmd.ExecuteNonQuery(); //Call WCF -> to db stored procedure for search history data dt2 = service.SearchData(Search); //Call Verification method (OVERLOAD for assignment requirement) to check if dt2 have more than 1 row... bool moreThanOneRow = Global.Verification(dt2, 1); if (!moreThanOneRow) { MessageBox.Show("There are no results, Please change your filters and try again."); } ; //Insert data into datagrid_products dataGrid_nyse.ItemsSource = dt2.DefaultView; dataGrid_nyse.CanUserAddRows = false; //Serialise Search and store in database lastSearchHistory = Global.SerializeToXml(Search); //SqlCommand cmd3 = con.CreateCommand(); //cmd3.CommandType = CommandType.Text; //cmd3.CommandText = "UPDATE Users SET lastSearchAll = '" +lastSearchHistory+ "' , lastSearchAll= '"+ lastSearchHistory + "' WHERE Id = " + Global.thisUser.Id; //cmd3.ExecuteNonQuery(); int SearchToDb = service.lastSearch(lastSearchHistory, Global.thisUser.Id); if (SearchToDb != 1) { MessageBox.Show("Last Search is not saved to history search, something went wrong!"); } //update number of results noResults.Text = "Number of results: " + dt2.Rows.Count.ToString(); //Fill Visualise by: combo box: foreach (DataColumn column in dt2.Columns) { colNames.Add(column.ColumnName.ToString()); } //add items from colList colNames.Clear(); visualiseInputBy.Items.Clear(); foreach (DataColumn column in dt2.Columns) { colNames.Add(column.ColumnName.ToString()); } foreach (string item in colNames) { visualiseInputBy.Items.Add(item); } visualiseInputBy.Items.Remove("date"); visualiseInputBy.Items.Remove("stock_symbol"); visualiseInputBy.Items.Remove("exchange"); //} } catch (System.Exception ex) { Global.CustomLog(ex.Message.ToString()); Global.CustomLog(ex.StackTrace.ToString()); Global.CustomLog("-------------------------------------------------------------------------------------------------------"); MessageBox.Show("Something went wrong! \n Please use right search format. \n For more informations check help on homepage."); } }