public List <AttributeSelectValuesResult> GetAttributeSelectValues(NetworkAttributes model, BridgeCareContext db) { var attributeSelectValuesResults = new List <AttributeSelectValuesResult>(); if (!db.NETWORKS.Any(network => network.NETWORKID == model.NetworkId)) { throw new RowNotInTableException($"Could not find network with id {model.NetworkId}"); } var validAttributes = GetAttributes(db).Select(m => m.Name) .Where(attribute => model.Attributes.Contains(attribute)).ToList(); if (!validAttributes.Any()) { throw new RowNotInTableException($"Provided attributes are not valid"); } var attributesWithValues = new List <string>(); var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["BridgeCareContext"].ConnectionString); sqlConnection.Open(); var attributeCountSelects = validAttributes .Select(attribute => $"COUNT(DISTINCT({attribute})) AS {attribute}").ToList(); var countsQuery = $"SELECT {string.Join(", ", attributeCountSelects)} FROM SEGMENT_{model.NetworkId}_NS0;"; var sqlCommand = new SqlCommand(countsQuery, sqlConnection); var reader = sqlCommand.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { for (var i = 0; i < validAttributes.Count; i++) { if (reader.GetInt32(i) > 100) { attributeSelectValuesResults.Add(new AttributeSelectValuesResult { Attribute = validAttributes[i], Values = new List <string>(), ResultMessage = $"Number of values for attribute {validAttributes[i]} exceeds 100; use text input" }); } else if (reader.GetInt32(i) == 0) { attributeSelectValuesResults.Add(new AttributeSelectValuesResult { Attribute = validAttributes[i], Values = new List <string>(), ResultMessage = $"No values found for attribute {validAttributes[i]}; use text input" }); } else { attributesWithValues.Add(validAttributes[i]); } } } } if (attributesWithValues.Any()) { var multipleAttributeValuesSelectQueries = new List <string>(); attributesWithValues.ForEach(attribute => { multipleAttributeValuesSelectQueries.Add($"SELECT DISTINCT(CAST({attribute} AS VARCHAR(255))) AS {attribute} FROM SEGMENT_{model.NetworkId}_NS0;"); }); sqlCommand = new SqlCommand(string.Join("", multipleAttributeValuesSelectQueries), sqlConnection); var index = 0; reader = sqlCommand.ExecuteReader(); while (reader.HasRows) { var values = new List <string>(); while (reader.Read()) { values.Add(reader.GetString(0)); } attributeSelectValuesResults.Add(new AttributeSelectValuesResult { Attribute = attributesWithValues[index], Values = values, ResultMessage = "Success" }); index++; reader.NextResult(); } } sqlCommand.Dispose(); sqlConnection.Close(); return(attributeSelectValuesResults); }
public IHttpActionResult GetAttributeSelectValues([FromBody] NetworkAttributes model) => Ok(repo.GetAttributeSelectValues(model, db));