//This method is used to work with all filters //Filter options are stored in the generic filters list //SqlCommand starts with standard beginning of SQL query //In switch we append to it necessary values, //value by default is "Any' public DataTable Filter(List<string> filterList) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * FROM [Screener].[dbo].[ScreenerSmall] WHERE "); for (int i = 0; i < filterList.Count; i++) { if (filterList[i] != ANY) { switch (i) { case (int)FiltersNames.Country: string insert = "[Country] Like ('%" + filterList[i] + "%') And "; sqlCommand.Append(insert); break; case (int)FiltersNames.Industry: insert = "[Industry] Like ('%" + filterList[i] + "%') And "; sqlCommand.Append(insert); break; case (int)FiltersNames.Sector: insert = "[Sector] Like ('%" + filterList[i] + "%') And "; sqlCommand.Append(insert); break; case (int)FiltersNames.Price: if (filterList[i][0] == UNDER) { insert = "[Price] < " + float.Parse(filterList[i].Remove(REMOVE_ZERO_INDEX, PRICE_UNDER_AMOUNT_OF_SYMBOLS)) + " And "; } else if (filterList[i][0] == OVER) { insert = "[Price] > " + float.Parse(filterList[i].Remove(REMOVE_ZERO_INDEX, PRICE_OVER_AMOUNT_OF_SYMBOLS)) + " And "; } else { insert = sqlCommand.ToString(); } sqlCommand.Append(insert); break; case (int)FiltersNames.Date: insert = "[Date] = '" + filterList[i] + "'"; sqlCommand.Append(insert); break; } } } if (sqlCommand[sqlCommand.Length - INDEX] == WHITE_SPACE) { sqlCommand.Remove(sqlCommand.Length - REMOVE_START_INDEX, REMOVE_LENGTH); } DataAccess data = new DataAccess(); DataSet dataSet = data.SortByFilters(sqlCommand.ToString()); DataTable tables = dataSet.Tables[0]; return tables; }
public DataTable FindByTicker(string ticker) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * FROM [Screener].[dbo].[ScreenerSmall] WHERE "); string insert = "[Ticker] = '" + ticker + "'"; sqlCommand.Append(insert); DataAccess data = new DataAccess(); DataSet dataSet = data.SortByFilters(sqlCommand.ToString()); DataTable tables = dataSet.Tables[0]; return tables; }