public IEnumerable<AppImage> GetImagesFromDb(string title, string link) { var images = new List<AppImage>(); var imagesNotInDb = false; try { _connection.Open(); var sql = string.Format("SELECT Location, Abstract from Image " + "where Title = '{0}' order by ID", title); using (var cmd = new MySqlCommand(sql, _connection)) { using (var r = cmd.ExecuteReader()) { if (r.HasRows) { while (r.Read()) { var img = new AppImage { URL = r.GetString("Location"), Abstract = r.GetString("Abstract") }; images.Add(img); } } else { imagesNotInDb = true; } } } if (imagesNotInDb) { //no images in db so insert them GetImages(title, link); //now get them and send back to phone images = GetNewImages(title); } } catch (Exception ex) { var stackFrame = new StackFrame(); var methodBase = stackFrame.GetMethod(); Database.InsertErrorToDb(methodBase.Name, ex.Message, ex.ToString()); } finally { _connection.Close(); } return images; }
private void GetImages(string title, string link) { var images = new List<AppImage>(); var web = new HtmlWeb(); try { var doc = web.Load(link); var nodes = doc.DocumentNode.SelectNodes("//div[@id='gallery-slider']").Descendants(); foreach (var node in nodes) { if (node.Name != "a") continue; var image = new AppImage {Title = title}; var decodedString = WebUtility.HtmlDecode(node.InnerHtml); image.Abstract = Regex.Replace(decodedString, @"\t|\n|\r", ""); image.Abstract = Regex.Replace(image.Abstract, @"<span>|</span>", "\t\n"); if (image.Abstract != "\t\n\t\n") { if (image.Abstract.Contains(";")) { image.Abstract = image.Abstract.Substring(image.Abstract.IndexOf(';') + 1); } else if (image.Abstract.Contains(":")) { image.Abstract = image.Abstract.Substring(image.Abstract.IndexOf(':') + 1); } var attr = node.Attributes; foreach (var attribute in attr.Where(attribute => attribute.Name == "href")) { image.URL = "http://gaa.ie" + attribute.Value; } images.Add(image); } } } catch (Exception ex) { var stackFrame = new StackFrame(); var methodBase = stackFrame.GetMethod(); Database.InsertErrorToDb(methodBase.Name, ex.Message, ex.ToString()); } try { foreach (var img in images) { const string query = "INSERT INTO Image (Title, Location, Abstract) VALUES (@title, @location, @abstract);"; using (var cmd = new MySqlCommand(query, _connection)) { // Start using the passed values in our parameters: cmd.Parameters.AddWithValue("@title", img.Title); cmd.Parameters.AddWithValue("@location", img.URL); cmd.Parameters.AddWithValue("@abstract", img.Abstract); // Execute the query cmd.ExecuteNonQuery(); } } } catch (Exception ex) { var stackFrame = new StackFrame(); var methodBase = stackFrame.GetMethod(); Database.InsertErrorToDb(methodBase.Name, ex.Message, ex.ToString()); } }
private List<AppImage> GetNewImages(string title) { var images = new List<AppImage>(); var sql = string.Format("SELECT * from Image " + "where Title = '{0}' order by ID", title); try { using (var cmd = new MySqlCommand(sql, _connection)) { using (var r = cmd.ExecuteReader()) { while (r.Read()) { var img = new AppImage { Abstract = r.GetString("Abstract"), Title = r.GetString("Title"), URL = r.GetString("Location") }; images.Add(img); } } } } catch (Exception ex) { var stackFrame = new StackFrame(); var methodBase = stackFrame.GetMethod(); Database.InsertErrorToDb(methodBase.Name, ex.Message, ex.ToString()); } return images; }