public NeonSiteCollection ReadSitesFromApi() { var neonSites = new NeonSiteCollection(); try { string url = "http://data.neonscience.org/api/v0/sites"; var client = new WebClient(); using (var stream = client.OpenRead(url)) using (var reader = new StreamReader(stream)) { var jsonData = client.DownloadString(url); neonSites = JsonConvert.DeserializeObject <NeonSiteCollection>(jsonData); } } catch (Exception ex) { _log.LogWrite("ReadSitesFromApi ERROR: " + ex.Message); } return(neonSites); }
/// <summary> /// Updates the NEON Sites using the NEON JSON Data API /// </summary> public void UpdateSites() { var neonSiteSensors = GetSensorPositions(); NeonSiteCollection neonSites = _apiReader.ReadSitesFromApi(); int numSitesFromApi = neonSiteSensors.Keys.Count; _log.LogWrite("UpdateSites for " + numSitesFromApi.ToString() + " sites ..."); try { string connString = ConfigurationManager.ConnectionStrings["OdmConnection"].ConnectionString; using (SqlConnection connection = new SqlConnection(connString)) { // delete old entries from series catalog (necessary for deleting entries from Sites table) string sqlDeleteSeries = "TRUNCATE TABLE dbo.SeriesCatalog"; using (SqlCommand cmd = new SqlCommand(sqlDeleteSeries, connection)) { try { connection.Open(); cmd.ExecuteNonQuery(); _log.LogWrite("deleted old series from SeriesCatalog"); } catch (Exception ex) { _log.LogWrite("error deleting old SeriesCatalog table: " + ex.Message); return; } finally { connection.Close(); } } // delete old entries from "sites" table // using batch delete DeleteOldSites(numSitesFromApi, connection); // to be adjusted int batchSize = 500; long siteID = 0L; var siteList = neonSites.data; int numBatches = (numSitesFromApi / batchSize) + 1; for (int b = 0; b < numBatches; b++) { // prepare for bulk insert DataTable bulkTable = new DataTable(); bulkTable.Columns.Add("SiteID", typeof(long)); bulkTable.Columns.Add("SiteCode", typeof(string)); bulkTable.Columns.Add("SiteName", typeof(string)); bulkTable.Columns.Add("Latitude", typeof(float)); bulkTable.Columns.Add("Longitude", typeof(float)); bulkTable.Columns.Add("LatLongDatumID", typeof(int)); bulkTable.Columns.Add("Elevation_m", typeof(float)); bulkTable.Columns.Add("VerticalDatum", typeof(string)); bulkTable.Columns.Add("LocalX", typeof(float)); bulkTable.Columns.Add("LocalY", typeof(float)); bulkTable.Columns.Add("LocalProjectionID", typeof(int)); bulkTable.Columns.Add("PosAccuracy_m", typeof(float)); bulkTable.Columns.Add("State", typeof(string)); bulkTable.Columns.Add("County", typeof(string)); bulkTable.Columns.Add("Comments", typeof(string)); bulkTable.Columns.Add("SiteType", typeof(string)); int batchStart = b * batchSize; int batchEnd = batchStart + batchSize; if (batchEnd >= numSitesFromApi) { batchEnd = numSitesFromApi; } for (int i = batchStart; i < batchEnd; i++) { siteID = siteID + 1; var row = bulkTable.NewRow(); row["SiteID"] = siteID; row["SiteCode"] = siteList[i].siteCode; row["SiteName"] = siteList[i].siteName; row["Latitude"] = siteList[i].siteLatitude; row["Longitude"] = siteList[i].siteLongitude; row["LatLongDatumID"] = 3; // WGS1984 row["Elevation_m"] = DBNull.Value; row["VerticalDatum"] = "Unknown"; row["LocalX"] = 0.0f; row["LocalY"] = 0.0f; row["LocalProjectionID"] = DBNull.Value; row["PosAccuracy_m"] = 0.0f; row["State"] = siteList[i].stateName; row["County"] = DBNull.Value; row["Comments"] = siteList[i].siteDescription + " " + siteList[i].siteCode; row["SiteType"] = "Atmosphere"; // from CUAHSI SiteTypeCV controlled vocabulary bulkTable.Rows.Add(row); } SqlBulkCopy bulkCopy = new SqlBulkCopy(connection); bulkCopy.DestinationTableName = "dbo.Sites"; connection.Open(); bulkCopy.WriteToServer(bulkTable); connection.Close(); Console.WriteLine("Sites inserted row " + batchEnd.ToString()); } } _log.LogWrite("UpdateSites: " + numSitesFromApi.ToString() + " sites updated."); } catch (Exception ex) { _log.LogWrite("UpdateSites ERROR: " + ex.Message); } }