//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());
}
}
