/// <summary> /// Find a list of location ids based on user ratings in database /// </summary> /// <param name="username">string</param> /// <returns>List<int/></returns> public List <int> FindLocationIdsByUserName(string username) { List <int> locationIds = new List <int>(); User user = _dbUser.FindUserByUserName(username, false); using (_connection = new SqlConnection(_connectionString)) { _connection.Open(); using (SqlCommand cmd = _connection.CreateCommand()) { cmd.CommandText = "SELECT LocationId FROM Rating WHERE userId = @userId"; cmd.Parameters.AddWithValue("userId", user.UserId); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int locationId = reader.GetInt32(reader.GetOrdinal("LocationId")); if (!locationIds.Contains(locationId)) { locationIds.Add(locationId); } } } _connection.Close(); } return(locationIds); }
/// <summary> /// /// </summary> /// <param name="userName">string</param> /// <returns>List<Location/></returns> public List <Location> FindLocationsByUserName(string userName) { List <Location> locations = new List <Location>(); User user = _dbUser.FindUserByUserName(userName, false); if (user != null) { int userId = user.UserId; using (_connection = new SqlConnection(_connectionString)) { _connection.Open(); using (SqlCommand cmd = _connection.CreateCommand()) { cmd.CommandText = "SELECT * FROM Locations WHERE UserId = @UserId"; cmd.Parameters.AddWithValue("UserId", userId); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int locationId = reader.GetInt32(reader.GetOrdinal("LocationId")); Location location = new Location { LocationId = locationId, Hits = reader.GetInt32(reader.GetOrdinal("Hits")), IsTopLocation = reader.GetBoolean(reader.GetOrdinal("IsTopLocation")), LocationName = reader.GetString(reader.GetOrdinal("LocationName")), LocationDescription = reader.GetString(reader.GetOrdinal("LocationDescription")), Latitude = reader.GetDouble(reader.GetOrdinal("Latitude")), Longitude = reader.GetDouble(reader.GetOrdinal("Longitude")), Pictures = _dbPicture.FindByLocationId(locationId), Ratings = _dbRating.FindByLocationId(locationId), User = user, RowVersion = (byte[])reader.GetValue(reader.GetOrdinal("rowVersion")) }; locations.Add(location); } } _connection.Close(); } } return(locations); }