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