//saving neo list to database private void buttonSave_Click(object sender, EventArgs e) { try { //building connection string string connString = buildConnectionString(); //connecting to mysqp conn = new MySqlConnection(connString); //replacing neos if they dont already exist for (int i = 0; i < neoList.Count; i++) { NEO neo = neoList[i]; conn.Open(); //sql statement //database only updates if neo does not exist. string upSql = $"INSERT ignore neos (name, speed, size, threat, url) " + $"SELECT '{neo.Name}',{neo.Mph},{neo.Diameter},{neo.Threat},'{neo.Url}' " + $"FROM neos " + $" WHERE NOT EXISTS(SELECT *" + $" FROM neos WHERE name = '{neo.Name}'" + $" AND speed = {neo.Mph}" + $" AND size = {neo.Diameter}" + $" And threat = {neo.Threat}" + $" And url = '{neo.Url}') limit 1;"; //Create the DataAdapter MySqlCommand upcmd = new MySqlCommand(upSql, conn); MySqlDataReader reader1; reader1 = upcmd.ExecuteReader(); //closing conn.Close(); } MessageBox.Show("neos saved"); }//exception catch (MySqlException ex) { MessageBox.Show($"UH_OH\n\n{ex.ToString()}"); } }
private void ReadJsonData(string api, string date) { //try to check internet connection try { string numberOfNeos; //connecting to internet var apiData = apiConnection.DownloadString(api); //creating json object JObject o = JObject.Parse(apiData); int numOfNeos; //getting number of neos for that day for loop numberOfNeos = o["element_count"].ToString(); int.TryParse(numberOfNeos, out numOfNeos); //loop through data for (int i = 0; i < numOfNeos; i++) { //values string neoName; string diameter; decimal diam; string url; string threat; bool thrt; string speed; decimal mph; NEO newNeo = new NEO(); //json data neoName = o["near_earth_objects"][date][i]["name"].ToString(); diameter = o["near_earth_objects"][date][i]["estimated_diameter"]["meters"]["estimated_diameter_max"].ToString(); url = o["near_earth_objects"][date][i]["nasa_jpl_url"].ToString(); threat = o["near_earth_objects"][date][i]["is_potentially_hazardous_asteroid"].ToString(); speed = o["near_earth_objects"][date][i]["close_approach_data"][0]["relative_velocity"]["miles_per_hour"].ToString(); newNeo.Name = neoName; decimal.TryParse(diameter, out diam); newNeo.Diameter = decimal.Round(diam, 2); newNeo.Url = url; bool.TryParse(threat, out thrt); newNeo.Threat = thrt; decimal.TryParse(speed, out mph); newNeo.Mph = decimal.Round(mph, 2); //adding to list neoList.Add(newNeo); apiData = null; } //loop to add neos to list foreach (NEO neo in neoList) { listBoxNEO.Items.Add(neo); } }//exception catch (Exception e) { MessageBox.Show(e.ToString()); } }