// FilterButtonOnClick handler private void FilterButtonOnClick(object sender, RoutedEventArgs e) { ClearResultColumn(); Type inventoryType = inventoryTypes[InventoryFilterComboBox.SelectedIndex]; SqlParameter[] producers = ProducersFilterControl.GetSelectedProducers(); IEnumerable <IFilter> characteristics = CharacteristicPanel.Children.OfType <IFilter>(); int ratingsLimit = CharacteristicPanel.Children.OfType <RatingsFilter>().First().RatingCount; IRubberType rubberType = CharacteristicPanel.Children.OfType <IRubberType>().FirstOrDefault(); List <dynamic> items = DbQuering.GetInventoryByFiltery(inventoryType, producers, characteristics, ratingsLimit, rubberType); if (items.Count == 0) { ShowNotFoundMessage(); return; } // add datagrid to panel DataGrid table = resultTables[InventoryFilterComboBox.SelectedIndex]; table.ItemsSource = null; table.ItemsSource = items; Grid.SetRow(table, 2); Grid.SetColumn(table, 1); ContentGrid.Children.Add(table); }
public static List <dynamic> GetInventoryByFiltery(Type inventoryType, SqlParameter[] producers, IEnumerable <IFilter> characteristics, int ratingsLimit, IRubberType specialTypes) { List <dynamic> items = new List <dynamic>(); using (var connection = new SqlConnection(DbConnect.DbConnectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandType = CommandType.Text; // get name of table to search string inventoryTable = inventoryType.Name; StringBuilder queryStringBuilder = new StringBuilder( $"SELECT * FROM Item inner JOIN {inventoryTable} AS inventory ON Item.ID = inventory.Item_ID WHERE "); // process producers list if (producers != null) { string producersParamenters = String.Join(",", producers.Select(p => p.ParameterName)); string producersQuery = $"Item.Producer_ID IN ({producersParamenters}) AND "; queryStringBuilder.Append(producersQuery); cmd.Parameters.AddRange(producers); } // process list of charchteristics if (characteristics != null) { IEnumerable <string> characteristicQueries = characteristics.Select(c => GetCharacteristicQuery(c, cmd.Parameters)); string query = String.Join(" AND ", characteristicQueries); queryStringBuilder.Append($"{query} AND "); } // proces ratings limit queryStringBuilder.AppendFormat("Ratings >= {0}", ratingsLimit); try { switch (inventoryTable) { case "Rubber": queryStringBuilder.Append($" AND {specialTypes.GetRubberType()[0]}"); break; case "Pips": string[] types = specialTypes.GetRubberType(); SqlParameter[] typesParameters = new SqlParameter[types.Length]; for (int i = 0; i < types.Length; i++) { typesParameters[i] = new SqlParameter($"@type{i}", SqlDbType.VarChar); typesParameters[i].Value = types[i]; } cmd.Parameters.AddRange(typesParameters); string parameters = String.Join(",", typesParameters.Select(p => p.ParameterName)); queryStringBuilder.Append($" AND PipsType IN ({parameters})"); break; } } catch (NullReferenceException) { } cmd.CommandText = queryStringBuilder.ToString(); // execute query and process response SqlDataReader sqlDataReader = cmd.ExecuteReader(); ConstructorInfo constructorInfo = inventoryType.GetConstructor(new[] { typeof(SqlDataReader) }); while (sqlDataReader.Read()) { object tempItem = constructorInfo.Invoke(new[] { sqlDataReader }); dynamic item = Convert.ChangeType(tempItem, inventoryType); items.Add(item); } } } return(items); }