Cities.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    public class CityResult
    {
        public string City { get; set; }
    }

    public class Meta
    {
        public string Next { get; set; }
    }

    public class RestCity
    {
        public List<CityResult> Results { get; set; }
        public Meta Meta { get; set; }
    }

    public class RestCountry
    {
        public string Name { get; set; }
        public List<string> Languages { get; set; }
        public string Alpha2Code { get; set; }
        public string Code
        {
            get
            {
                return Languages.Any() ? string.Format("{0}-{1}", Languages[0], Alpha2Code) : Alpha2Code;
            }
        }
    }

    public class Program
    {
        public static List<CityResult> GetCities(string code)
        {
            var result = new List<CityResult>();
            var client = new HttpClient();
            var uri = string.Format("http://api.meetup.com/2/cities?country={0}", code);

            var response = client.GetAsync(uri).Result;
            if (response.IsSuccessStatusCode)
            {
                var apiResult = response.Content.ReadAsAsync<RestCity>().Result;
                result.AddRange(apiResult.Results);

                while (!string.IsNullOrEmpty(apiResult.Meta.Next))
                {
                    response = client.GetAsync(apiResult.Meta.Next).Result;
                    if (response.IsSuccessStatusCode)
                    {
                        apiResult = response.Content.ReadAsAsync<RestCity>().Result;
                        result.AddRange(apiResult.Results);
                    }
                }
            }

            return result;
        }

        static void Main(string[] args)
        {
            using (var connection = new SqlConnection("Data Source=(localdb)\\v11.0;Integrated Security=true;AttachDbFileName=|DataDirectory|\\Locations.mdf"))
            {
                var query = @"INSERT INTO Cities (CountryID, Name, BusinessActive) VALUES ";
                var readQuery = @"SELECT * FROM Countries";

                SqlDataAdapter adapter = new SqlDataAdapter(readQuery, connection);
                DataSet DS = new DataSet();
                adapter.Fill(DS);

                var table = DS.Tables[0];
                foreach (DataRow row in table.Rows)
                {
                    var countryId = int.Parse(row["ID"].ToString());
                    var code = row["Code"] as string;
                    var countryCode = code.Substring(code.IndexOf("-") + 1);
                    var cities = GetCities(countryCode);

                    for (var i = 0; i < cities.Count; i++)
                    {
                        var item = cities[i];
                        if (item == null || string.IsNullOrEmpty(item.City)) continue;

                        item.City = item.City.Replace("'", "''");
                        query = string.Format("INSERT INTO Cities (CountryID, Name, BusinessActive) VALUES  ({0}, N'{1}', 1)", countryId, item.City);
                        //if (i < cities.Count - 1)
                        //{
                        //    query += ",";
                        //}
                        adapter = new SqlDataAdapter(query, connection);
                        DS = new DataSet();
                        adapter.Fill(DS);
                    }


                    Console.WriteLine(countryId + ": " + cities.Count);
                }
                

                //var client = new HttpClient();

                ////client.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));
                ////client.DefaultRequestHeaders.Add("X-Mashape-Key", "4ciscbsAfnmsh0HJpzhZ0aARgUKEp1jwd3sjsn4aolJjtoryRZ");

                //var response = client.GetAsync("https://restcountries-v1.p.mashape.com/all").Result;
                //if (response.IsSuccessStatusCode)
                //{
                //    var result = response.Content.ReadAsAsync<List<RestCountry>>().Result;
                //    for (var i = 0; i < result.Count; i++)
                //    {
                //        var item = result[i];
                //        query = string.Format("{0} (N'{1}', N'{2}', 1)", query, item.Name, item.Code);
                //        if (i < result.Count - 1)
                //        {
                //            query += ",";
                //        }
                //        //Console.WriteLine(query);
                //    }
                //}


                
            }
        }
    }
}