public List<Carpark> GetCarparks(string name, int id) { var carparks = new List<Carpark>(); var recent = true; var getThem = false; try { _connection = new MySqlConnection(ConnectionString); _connection.Open(); var sql = string.Format("SELECT * from Roadwatch_Parking where City = '{0}' ", name); using (var cmd = new MySqlCommand(sql, _connection)) { using (var r = cmd.ExecuteReader()) { if (r.HasRows) { while (r.Read()) { //if (DateTime.Now.Subtract(r.GetDateTime("Updated")).TotalDays > 30) //{ // recent = false; // break; //} var cp = new Carpark { Access = r.GetString("Access"), City = r.GetString("City"), CityId = r.GetInt32("City_Id"), District = r.GetString("District"), Hours = r.GetString("Hours"), Id = r.GetInt32("Id"), Location = r.GetString("Location"), Name = r.GetString("Name"), Tarrifs = r.GetString("Tarriffs"), Url = r.GetString("Url") }; carparks.Add(cp); } } else { getThem = true; } } } } catch (Exception ex) { var sf = new StackFrame(); var methodBase = sf.GetMethod(); Database.InsertRoadwatchErrorToDb(methodBase.Name, ex.Message, ex.ToString()); } if (!recent || getThem) { var web = new HtmlWeb(); var doc = web.Load("http://www.theaa.ie/AA/AA-Roadwatch/Car-parking/" + name + "/"); var nodes = doc.DocumentNode.SelectNodes("//div[@class='parkingList']"); foreach (var node in nodes) { var parks = node.ChildNodes[3].ChildNodes[3].ChildNodes; //.SelectNodes("//tr"); foreach (var n in parks.Where(p => p.Name == "tr")) { var cp = new Carpark(); cp.District = CleanString(node.ChildNodes[1].InnerText); cp.Name = CleanString(n.ChildNodes[1].InnerText); cp.Location = CleanString(n.ChildNodes[3].InnerText); cp.Url = "http://www.theaa.ie" + n.ChildNodes[1].ChildNodes[1].Attributes[0].Value; cp.Access = CleanString(GetDetails(cp.Url)); cp.Tarrifs = CleanString(tariffs); cp.Hours = CleanString(openingHours); cp.City = name; cp.CityId = id; carparks.Add(cp); } } if (carparks.Count > 0) { foreach (var cp in carparks) { //InsertCarparkToDb(cp); } } GetCarparks(name, id); } return carparks; }
private void InsertCarparkToDb(Carpark cp) { _connection = new MySqlConnection(ConnectionString); _connection.Open(); try { const string query = "INSERT INTO Roadwatch_Parking (City, District, Name, Location, Url, Access, Hours, Tarriffs, City_Id, Updated)" + " VALUES (@city, @district, @name, @location, @url, @access, @hours, @tarriffs, @cityid, @updated);"; using (var cmd = new MySqlCommand(query, _connection)) { // Start using the passed values in our parameters: cmd.Parameters.AddWithValue("@city", cp.City); cmd.Parameters.AddWithValue("@district", cp.District); cmd.Parameters.AddWithValue("@name", cp.Name); cmd.Parameters.AddWithValue("@location", cp.Location); cmd.Parameters.AddWithValue("@url", cp.Url); cmd.Parameters.AddWithValue("@access", cp.Access); cmd.Parameters.AddWithValue("@hours", cp.Hours); cmd.Parameters.AddWithValue("@tarriffs", cp.Tarrifs); cmd.Parameters.AddWithValue("@cityid", cp.CityId); cmd.Parameters.AddWithValue("@updated", DateTime.Now); // Execute the query cmd.ExecuteNonQuery(); } } catch (Exception ex) { var sf = new StackFrame(); var methodBase = sf.GetMethod(); Database.InsertErrorToDb(methodBase.Name, ex.Message, ex.ToString()); } }