public IHttpActionResult Get(string country = null, int?m = null, int?y = null, string lang = null, string order = null, bool?desc = null, int?limit = -1) { DataAccess data = new DataAccess(); Finalscore fsc = new Finalscore() { id = -1, namecountry = (country != null?country:"Nan"), namelanguage = (lang != null?lang:"Nan"), month = (m != null && y != null?new DateTime((int)y, (int)m + 1, 1):new DateTime(1800, 1, 1)), score = -1, order = (order != null?order:"Nan"), desc = (desc != null?(bool)desc : false) }; var products = data.GetFinalscore(fsc, limit); List <FinalscoreModel> result = new List <FinalscoreModel>(); foreach (var item in products) { FinalscoreModel finalscore = new FinalscoreModel(); finalscore.id = item.id; finalscore.namecountry = item.namecountry; finalscore.namelanguage = item.namelanguage; finalscore.score = item.score; finalscore.month = item.month; result.Add(finalscore); } return(Ok(result)); }
public IEnumerable <Finalscore> GetFinalscore(Finalscore sc, int?limit) { using (NpgsqlConnection connection = new NpgsqlConnection(connectionString)) { connection.Open(); string query = @" SELECT scores.id ,countries.name as cname ,languages.name as lname ,score ,month FROM scores LEFT JOIN countries ON countries.id = scores.id_country LEFT JOIN languages ON languages.id = scores.id_language"; using (NpgsqlCommand command = connection.CreateCommand()) { command.CommandText = query; command.CommandType = CommandType.Text; bool where = false; if (sc.namecountry != "Nan") { command.CommandText += " WHERE countries.name = @namecountry "; command.Parameters.Add(new NpgsqlParameter("@namecountry", sc.namecountry)); where = true; } if (sc.namelanguage != "Nan") { if (where) { command.CommandText += "AND"; } else { command.CommandText += " WHERE"; } command.CommandText += " languages.name = @namelanguage "; command.Parameters.Add(new NpgsqlParameter("@namelanguage", sc.namelanguage)); } if (sc.month != new DateTime(1800, 1, 1)) { if (where) { command.CommandText += "AND"; } else { command.CommandText += " WHERE"; } command.CommandText += " scores.month = @scoremonth "; command.Parameters.Add(new NpgsqlParameter("@scoremonth", sc.month)); } if (sc.order != "Nan") { command.CommandText += " ORDER BY " + sc.order; if (sc.desc) { command.CommandText += " DESC"; } else { command.CommandText += " ASC"; } } if (limit != null && limit > 0) { command.CommandText += " LIMIT @limit"; command.Parameters.Add(new NpgsqlParameter("@limit", limit)); } NpgsqlDataReader reader = command.ExecuteReader(); List <Finalscore> Scores = new List <Finalscore>(); while (reader.Read()) { Finalscore finalscore = new Finalscore(); finalscore.id = (int)reader["id"]; finalscore.namecountry = reader["cname"] as string; finalscore.namelanguage = reader["lname"] as string; finalscore.score = (int)reader["score"]; finalscore.month = DateTime.ParseExact(reader["month"].ToString(), "dd/MM/yyyy hh:mm:ss", CultureInfo.InvariantCulture); Scores.Add(finalscore); } return(Scores); } } }