예제 #1
0
        public List <ClubGround> GetClubGroundByCounty(string county)
        {
            var             clubGrounds = new List <ClubGround>();
            MySqlConnection _connection;

            _connection = new MySqlConnection(ConnectionString);

            try
            {
                _connection.Open();
                var sql = "Select * from Club_Grounds where County = '" + county + "' order by Club_Ground";

                using (var cmd = new MySqlCommand(sql, _connection))
                {
                    using (var r = cmd.ExecuteReader())
                    {
                        while (r.Read())
                        {
                            var v = new ClubGround
                            {
                                Id            = Convert.ToInt32(r.GetString("Id")),
                                Club_Ground   = r.GetString("Club_Ground"),
                                County        = county,
                                Latitude      = r.GetString("Latitude"),
                                Longitude     = r.GetString("Longitude"),
                                Club_Ground_2 = r.GetString("Club_Ground_2"),
                                Colours       = r.GetString("Colours"),
                                Email         = r.GetString("Email"),
                                Facebook      = r.GetString("Facebook"),
                                Phone         = r.GetString("Phone"),
                                Website       = r.GetString("Website"),
                                Twitter       = r.GetString("Twitter")
                            };
                            clubGrounds.Add(v);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var stackFrame = new StackFrame();
                var methodBase = stackFrame.GetMethod();
                Database.InsertErrorToDb(methodBase.Name, ex.Message, ex.ToString());
            }
            finally
            {
                _connection.Close();
            }

            return(clubGrounds);
        }
예제 #2
0
        public String ScrapeClubGrounds(string county, string id)
        {
            var             grounds = new List <ClubGround>();
            MySqlConnection _connection;

            _connection = new MySqlConnection(ConnectionString);
            var          web = new HtmlWeb();
            HtmlDocument doc;

            try
            {
                var link = "http://clubfinder.co/includes/clubs.php?sport=1&region=" + id;
                doc = web.Load(link);
                var nodes = doc.DocumentNode.SelectNodes("//button");
                _connection.Open();
                foreach (var node in nodes)
                {
                    var attr = node.Attributes;
                    foreach (var at in attr.AttributesWithName("onclick"))
                    {
                        var s = at.Value;
                        s = Regex.Match(s, @"\(([^;]*)\)").Groups[1].Value;
                        s = s.Replace("'", "");
                        var values = s.Split(',');
                        var cg     = new ClubGround
                        {
                            Club_Ground   = values[0],
                            Club_Ground_2 = values[1],
                            Latitude      = values[2],
                            Longitude     = values[3],
                            Colours       = values[4],
                            Website       = values[5],
                            Facebook      = values[6],
                            Twitter       = values[7],
                            Email         = values[8],
                            Phone         = values[9],
                            County        = county
                        };
                        grounds.Add(cg);

                        const string query =
                            "INSERT INTO Club_Grounds (County, Club_Ground, Club_Ground_2, Latitude, Longitude, Colours, Website, " +
                            "Facebook, Email, Phone, Twitter) VALUES (@county, @clubground, @clubground2, @latitude, @longitude, @colours, @website, " +
                            "@facebook, @email, @phone, @twitter);";

                        using (var cmd = new MySqlCommand(query, _connection))
                        {
                            // Start using the passed values in our parameters:
                            cmd.Parameters.AddWithValue("@county", cg.County);
                            cmd.Parameters.AddWithValue("@clubground", cg.Club_Ground);
                            cmd.Parameters.AddWithValue("@clubground2", cg.Club_Ground_2);
                            cmd.Parameters.AddWithValue("@latitude", cg.Latitude);
                            cmd.Parameters.AddWithValue("@longitude", cg.Longitude);
                            cmd.Parameters.AddWithValue("@colours", cg.Colours);
                            cmd.Parameters.AddWithValue("@website", cg.Website);
                            cmd.Parameters.AddWithValue("@facebook", cg.Facebook);
                            cmd.Parameters.AddWithValue("@email", cg.Email);
                            cmd.Parameters.AddWithValue("@phone", cg.Phone);
                            cmd.Parameters.AddWithValue("@twitter", cg.Twitter);

                            // Execute the query
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var stackFrame = new StackFrame();
                var methodBase = stackFrame.GetMethod();
                Database.InsertErrorToDb(methodBase.Name, ex.Message, ex.ToString());
                return("error");
            }
            finally
            {
                _connection.Close();
            }
            return("ok");
        }