public GoogleMapAPI GetGoogleLatLon(string address) { GoogleMapAPI googlemapResult = new GoogleMapAPI(); string url = "https://maps.googleapis.com/maps/api/geocode/json?address=" + address + "&key=AIzaSyAambBvgsmatjUZlZFBjF2odFC0Lr20RIU"; HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url); HttpWebResponse response = (HttpWebResponse)request.GetResponse(); if (response.StatusCode == HttpStatusCode.OK) { Stream receiveStream = response.GetResponseStream(); StreamReader readStream = null; if (response.CharacterSet == null) { readStream = new StreamReader(receiveStream); } else { readStream = new StreamReader(receiveStream, Encoding.GetEncoding(response.CharacterSet)); } string data = readStream.ReadToEnd(); googlemapResult = new JavaScriptSerializer().Deserialize <GoogleMapAPI>(data); response.Close(); readStream.Close(); } return(googlemapResult); }
public void InsertDB(location l) { try { cn.Open(); MySqlCommand checkCountryCmd = new MySqlCommand("select country_id from country where country_tc = @country_tc and country_en = @country_en", cn); checkCountryCmd.CommandType = CommandType.Text; checkCountryCmd.Parameters.Add("@country_tc", MySqlDbType.VarChar).Value = l.country_tc; checkCountryCmd.Parameters.Add("@country_en", MySqlDbType.VarChar).Value = l.country_en; int country_id = 0; if (checkCountryCmd.ExecuteScalar() != null) { country_id = Int32.Parse(checkCountryCmd.ExecuteScalar().ToString()); } if (country_id == 0) { MySqlCommand insertCountryCmd = new MySqlCommand("insert into country (country_en, country_tc, country_sc) values (@country_en, @country_tc, @country_sc); select last_insert_id();", cn); insertCountryCmd.CommandType = CommandType.Text; insertCountryCmd.Parameters.Add("@country_en", MySqlDbType.VarChar).Value = l.country_en; insertCountryCmd.Parameters.Add("@country_tc", MySqlDbType.VarChar).Value = l.country_tc; insertCountryCmd.Parameters.Add("@country_sc", MySqlDbType.VarChar).Value = Microsoft.VisualBasic.Strings.StrConv(l.country_tc, VbStrConv.SimplifiedChinese, 2052); country_id = Int32.Parse(insertCountryCmd.ExecuteScalar().ToString()); } int area_id = 0; if (!string.IsNullOrEmpty(l.area_tc) && !string.IsNullOrEmpty(l.area_en)) { MySqlCommand checkAreaCmd = new MySqlCommand("select area_id from area where area_tc = @area_tc and area_en = @area_en and country_id = @country_id", cn); checkAreaCmd.CommandType = CommandType.Text; checkAreaCmd.Parameters.Add("@area_tc", MySqlDbType.VarChar).Value = l.area_tc; checkAreaCmd.Parameters.Add("@area_en", MySqlDbType.VarChar).Value = l.area_en; checkAreaCmd.Parameters.Add("@country_id", MySqlDbType.Int32).Value = country_id; if (checkAreaCmd.ExecuteScalar() != null) { area_id = Int32.Parse(checkAreaCmd.ExecuteScalar().ToString()); } if (area_id == 0) { MySqlCommand insertAreaCmd = new MySqlCommand("insert into area (country_id, area_en, area_tc, area_sc) values (@country_id, @area_en, @area_tc, @area_sc); select last_insert_id();", cn); insertAreaCmd.CommandType = CommandType.Text; insertAreaCmd.Parameters.Add("@country_id", MySqlDbType.Int32).Value = country_id; insertAreaCmd.Parameters.Add("@area_en", MySqlDbType.VarChar).Value = l.area_en; insertAreaCmd.Parameters.Add("@area_tc", MySqlDbType.VarChar).Value = l.area_tc; insertAreaCmd.Parameters.Add("@area_sc", MySqlDbType.VarChar).Value = Microsoft.VisualBasic.Strings.StrConv(l.area_tc, VbStrConv.SimplifiedChinese, 2052); area_id = Int32.Parse(insertAreaCmd.ExecuteScalar().ToString()); } } int district_id = 0; if (!string.IsNullOrEmpty(l.district_en) && !string.IsNullOrEmpty(l.district_tc)) { MySqlCommand checkDistrictCmd = new MySqlCommand("select district_id from district where district_tc = @district_tc and district_en = @district_en and area_id = @area_id", cn); checkDistrictCmd.CommandType = CommandType.Text; checkDistrictCmd.Parameters.Add("@district_tc", MySqlDbType.VarChar).Value = l.area_tc; checkDistrictCmd.Parameters.Add("@district_en", MySqlDbType.VarChar).Value = l.area_en; checkDistrictCmd.Parameters.Add("@area_id", MySqlDbType.Int32).Value = area_id; if (checkDistrictCmd.ExecuteScalar() != null) { district_id = Int32.Parse(checkDistrictCmd.ExecuteScalar().ToString()); } if (district_id == 0) { MySqlCommand insertDistrictCmd = new MySqlCommand(@"insert into district (country_id, area_id, district_en, district_tc, district_sc) values (@country_id, @area_id, @district_en, @district_tc, @district_sc); select last_insert_id();", cn); insertDistrictCmd.CommandType = CommandType.Text; insertDistrictCmd.Parameters.Add("@country_id", MySqlDbType.Int32).Value = country_id; insertDistrictCmd.Parameters.Add("@area_id", MySqlDbType.Int32).Value = area_id; insertDistrictCmd.Parameters.Add("@district_en", MySqlDbType.VarChar).Value = l.district_en; insertDistrictCmd.Parameters.Add("@district_tc", MySqlDbType.VarChar).Value = l.district_tc; insertDistrictCmd.Parameters.Add("@district_sc", MySqlDbType.VarChar).Value = Microsoft.VisualBasic.Strings.StrConv(l.district_tc, VbStrConv.SimplifiedChinese, 2052); district_id = Int32.Parse(insertDistrictCmd.ExecuteScalar().ToString()); } } string address = ""; if (!string.IsNullOrEmpty(l.address_en)) { address += l.address_en + ","; } if (!string.IsNullOrEmpty(l.extend_address)) { address += l.extend_address + ","; } if (!string.IsNullOrEmpty(l.district_en)) { address += l.district_en + ","; } if (!string.IsNullOrEmpty(l.area_en)) { address += l.area_en + ","; } address += l.country_en; GoogleMapAPI response = GetGoogleLatLon(address); MySqlCommand checkLocationCmd = new MySqlCommand("select location_id from location where address_en = @address_en and address_tc = @address_tc and district_id = @district_id", cn); checkLocationCmd.CommandType = CommandType.Text; checkLocationCmd.Parameters.Add("@address_en", MySqlDbType.VarChar).Value = l.address_en; checkLocationCmd.Parameters.Add("@address_tc", MySqlDbType.VarChar).Value = l.address_tc; checkLocationCmd.Parameters.Add("@district_id", MySqlDbType.Int32).Value = district_id; int location_id = 0; location_id = Int32.Parse(checkLocationCmd.ExecuteScalar() != null ? checkLocationCmd.ExecuteScalar().ToString() : "0"); if (location_id == 0) { MySqlCommand insertLocationCmd = new MySqlCommand(@"insert into location (name_en, name_tc, name_sc, phone, website, country_id, area_id, district_id, address_en, address_tc, address_sc, extend_address, postal_code, lat, lon) values ( @name_en, @name_tc, @name_sc, @phone, @website, @country_id, @area_id, @district_id, @address_en, @address_tc, @address_sc, @extend_address, @postal_code, @lat, @lon); select last_insert_id();", cn); insertLocationCmd.Parameters.Add("@name_en", MySqlDbType.VarChar).Value = l.name_en; insertLocationCmd.Parameters.Add("@name_tc", MySqlDbType.VarChar).Value = l.name_tc; insertLocationCmd.Parameters.Add("@name_sc", MySqlDbType.VarChar).Value = Microsoft.VisualBasic.Strings.StrConv(l.name_tc, VbStrConv.SimplifiedChinese, 2052); insertLocationCmd.Parameters.Add("@phone", MySqlDbType.VarChar).Value = l.phone; insertLocationCmd.Parameters.Add("@website", MySqlDbType.VarChar).Value = l.website; insertLocationCmd.Parameters.Add("@country_id", MySqlDbType.Int32).Value = country_id; insertLocationCmd.Parameters.Add("@area_id", MySqlDbType.Int32).Value = area_id; insertLocationCmd.Parameters.Add("@district_id", MySqlDbType.Int32).Value = district_id; insertLocationCmd.Parameters.Add("@address_en", MySqlDbType.VarChar).Value = l.address_en; insertLocationCmd.Parameters.Add("@address_tc", MySqlDbType.VarChar).Value = l.address_tc; insertLocationCmd.Parameters.Add("@address_sc", MySqlDbType.VarChar).Value = Microsoft.VisualBasic.Strings.StrConv(l.address_tc, VbStrConv.SimplifiedChinese, 2052); insertLocationCmd.Parameters.Add("@extend_address", MySqlDbType.VarChar).Value = l.extend_address; insertLocationCmd.Parameters.Add("@postal_code", MySqlDbType.VarChar).Value = l.postal_code; if (response.results.Count > 0) { insertLocationCmd.Parameters.Add("@lat", MySqlDbType.VarChar).Value = response.results[response.results.Count - 1].geometry.Location.lat.ToString(); insertLocationCmd.Parameters.Add("@lon", MySqlDbType.VarChar).Value = response.results[response.results.Count - 1].geometry.Location.lng.ToString(); } else { insertLocationCmd.Parameters.Add("@lat", MySqlDbType.VarChar).Value = "0"; insertLocationCmd.Parameters.Add("@lon", MySqlDbType.VarChar).Value = "0"; } insertLocationCmd.Parameters.Add("@location_type_id", MySqlDbType.Int32).Value = l.type_id; location_id = Int32.Parse(insertLocationCmd.ExecuteScalar().ToString()); } MySqlCommand deletePendCmd = new MySqlCommand("delete from pending_image where location_id = @location_id", cn); deletePendCmd.CommandType = CommandType.Text; deletePendCmd.Parameters.Add("@location_id", MySqlDbType.Int32).Value = location_id; deletePendCmd.ExecuteNonQuery(); for (int i = 0; i < l.imageList.Count; i++) { MySqlCommand insertCmd = new MySqlCommand("insert into pending_image (location_id, image_order, source) values (@location_id, @image_order, @source)", cn); insertCmd.CommandType = CommandType.Text; insertCmd.Parameters.Add("@location_id", MySqlDbType.Int32).Value = location_id; insertCmd.Parameters.Add("@image_order", MySqlDbType.Int32).Value = i + 1; insertCmd.Parameters.Add("@source", MySqlDbType.VarChar).Value = l.imageList[i]; insertCmd.ExecuteNonQuery(); } MySqlCommand completeCmd = new MySqlCommand("insert into complete_url (url) values (@url)", cn); completeCmd.CommandType = CommandType.Text; completeCmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = l.url; completeCmd.ExecuteNonQuery(); } catch (Exception ex) { File.AppendAllText(AppDomain.CurrentDomain.BaseDirectory + "log.txt", l.url + " , db error" + System.Environment.NewLine); File.AppendAllText(AppDomain.CurrentDomain.BaseDirectory + "log.txt", ex.StackTrace + System.Environment.NewLine); } finally { cn.Close(); } }