public List<Drink> GetToplist(string name, string name2, string whiskeySort, string country, string region, double maxPrice, double minPrice, string userId) { SqlDataReader myReader = null; List<Drink> toplist = new List<Drink>(); try { command.Connection = myConnection; myConnection.ConnectionString = connectionString; myConnection.Open(); command.CommandText = "sp_Toplist"; command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Clear(); command.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 255); command.Parameters.Add("@Name2", System.Data.SqlDbType.NVarChar, 255); command.Parameters.Add("@WhiskeySort", System.Data.SqlDbType.NVarChar, 255); command.Parameters.Add("@Country", System.Data.SqlDbType.NVarChar, 255); command.Parameters.Add("@Region", System.Data.SqlDbType.NVarChar, 255); command.Parameters.Add("@MaxPrice", System.Data.SqlDbType.Float); command.Parameters.Add("@MinPrice", System.Data.SqlDbType.Float); command.Parameters.Add("@UserID", System.Data.SqlDbType.NVarChar, 128); command.Parameters["@Name"].Value = name; command.Parameters["@Name2"].Value = name2; command.Parameters["@WhiskeySort"].Value = whiskeySort; command.Parameters["@Country"].Value = country; command.Parameters["@Region"].Value = region; command.Parameters["@MaxPrice"].Value = maxPrice; command.Parameters["@MinPrice"].Value = minPrice; command.Parameters["@UserID"].Value = userId; myReader = command.ExecuteReader(); if (myReader != null) { Drink tmpDrink = new Drink(); List<string> tmptoplist = new List<string>(); while (myReader.Read()) { tmpDrink = new Drink(); tmpDrink.Nr=(myReader[0] is double ? (double)myReader[0]:0); tmpDrink.ProductId= (myReader[1] is double ? (double)myReader[1] : 0); tmpDrink.Varnummer = (myReader[2] is double ? (double)myReader[2] : 0); tmpDrink.Name= myReader[3].ToString(); tmpDrink.Name2 = myReader[4].ToString(); tmpDrink.Price = Convert.ToDouble(myReader[5].ToString(), System.Globalization.CultureInfo.InvariantCulture); tmpDrink.Volume = Convert.ToDouble(myReader[6].ToString(), System.Globalization.CultureInfo.InvariantCulture); tmpDrink.PricePerLiter = Convert.ToDouble(myReader[7].ToString(), System.Globalization.CultureInfo.InvariantCulture); tmpDrink.ProductGroup = myReader[8].ToString(); tmpDrink.Origin = myReader[9].ToString(); tmpDrink.Country = myReader[10].ToString(); tmpDrink.Producer = myReader[11].ToString(); tmpDrink.AlcoLevel= myReader[12].ToString(); tmpDrink.Grade=(myReader[13] is double ? (double)myReader[13]:0); toplist.Add(tmpDrink); } } } catch (Exception ex) { } finally { if (myReader != null) { myReader.Close(); } if (myConnection!=null) { myConnection.Close(); } } return toplist; }
private List<Drink> SortAndDistinct(List<Drink> listToSort) { Drink tmpDrink = new Drink(); var List = listToSort.GroupBy(x => new { Varnummer = x.Varnummer }). Select(y => new { Count = y.Count(), Average = y.Average(z => z.Grade), Varnummer = y.Key.Varnummer }).ToList(); var returnList = listToSort.DistinctBy(p => p.Varnummer).ToList(); foreach (var Drink in returnList) { foreach (var pretendedDrink in List) { if (Drink.Varnummer == pretendedDrink.Varnummer) { Drink.AverageGrade = pretendedDrink.Average; Drink.Votes = pretendedDrink.Count; } } } return returnList.OrderByDescending(d => d.AverageGrade).ToList(); }