public void UpdateSeriesCatalog_fast()
{
var siteLookup = GetSiteLookup();
var variableLookup = getVariableLookup();
var methodLookup = getMethodLookup();
var source = getSource();
var wsClient = new AwdbClient(_log);
List <Series> seriesList = wsClient.GetAllSeries();
Console.WriteLine("updating series catalog for " + seriesList.Count.ToString() + " series ...");
string connString = ConfigurationManager.ConnectionStrings["OdmConnection"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connString))
{
// delete old entries from series catalog
string sql = "TRUNCATE TABLE dbo.SeriesCatalog";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
try
{
connection.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("deleted old series from SeriesCatalog");
}
catch (Exception ex)
{
Console.WriteLine("error deleting old series from SeriesCatalog");
_log.LogWrite("UpdateSeriesCatalog: error deleting old series from SeriesCatalog");
return;
}
finally
{
connection.Close();
}
}
int batchSize = 500;
int numBatches = (seriesList.Count / batchSize) + 1;
long seriesID = 0L;
try
{
for (int b = 0; b < numBatches; b++)
{
// prepare for bulk insert
DataTable bulkTable = new DataTable();
bulkTable.Columns.Add("SeriesID", typeof(long));
bulkTable.Columns.Add("SiteID", typeof(long));
bulkTable.Columns.Add("SiteCode", typeof(string));
bulkTable.Columns.Add("SiteName", typeof(string));
bulkTable.Columns.Add("SiteType", typeof(string));
bulkTable.Columns.Add("VariableID", typeof(int));
bulkTable.Columns.Add("VariableCode", typeof(string));
bulkTable.Columns.Add("VariableName", typeof(string));
bulkTable.Columns.Add("Speciation", typeof(string));
bulkTable.Columns.Add("VariableUnitsID", typeof(int));
bulkTable.Columns.Add("VariableUnitsName", typeof(string));
bulkTable.Columns.Add("SampleMedium", typeof(string));
bulkTable.Columns.Add("ValueType", typeof(string));
bulkTable.Columns.Add("TimeSupport", typeof(float));
bulkTable.Columns.Add("TimeUnitsID", typeof(int));
bulkTable.Columns.Add("TimeUnitsName", typeof(string));
bulkTable.Columns.Add("DataType", typeof(string));
bulkTable.Columns.Add("GeneralCategory", typeof(string));
bulkTable.Columns.Add("MethodID", typeof(int));
bulkTable.Columns.Add("MethodDescription", typeof(string));
bulkTable.Columns.Add("SourceID", typeof(int));
bulkTable.Columns.Add("Organization", typeof(string));
bulkTable.Columns.Add("SourceDescription", typeof(string));
bulkTable.Columns.Add("Citation", typeof(string));
bulkTable.Columns.Add("QualityControlLevelID", typeof(int));
bulkTable.Columns.Add("QualityControlLevelCode", typeof(string));
bulkTable.Columns.Add("BeginDateTime", typeof(DateTime));
bulkTable.Columns.Add("EndDateTime", typeof(DateTime));
bulkTable.Columns.Add("BeginDateTimeUTC", typeof(DateTime));
bulkTable.Columns.Add("EndDateTimeUTC", typeof(DateTime));
bulkTable.Columns.Add("ValueCount", typeof(int));
int batchStart = b * batchSize;
int batchEnd = batchStart + batchSize;
if (batchEnd >= seriesList.Count)
{
batchEnd = seriesList.Count;
}
for (int i = batchStart; i < batchEnd; i++)
{
try
{
var variableCode = seriesList[i].VariableCode;
var siteCode = seriesList[i].SiteCode;
var methodCode = seriesList[i].MethodCode;
// only include series for variables which are already in the Variables table
// for example "DIAG" series are excluded because the "DIAG" variable has no CUAHSI equivalent ...
if (_variableLookup.ContainsKey(variableCode) && siteLookup.ContainsKey(siteCode) && methodLookup.ContainsKey(methodCode))
{
var row = bulkTable.NewRow();
seriesID = seriesID + 1;
Variable v = _variableLookup[variableCode];
Site s = siteLookup[siteCode];
MethodInfo m = methodLookup[methodCode];
row["SeriesID"] = seriesID;
row["SiteID"] = s.SiteID;
row["SiteCode"] = seriesList[i].SiteCode;
row["SiteName"] = s.SiteName;
row["SiteType"] = "Atmosphere";
row["VariableID"] = v.VariableID;
row["VariableCode"] = v.VariableCode;
row["VariableName"] = v.VariableName;
row["Speciation"] = v.Speciation;
row["VariableUnitsID"] = v.VariableUnitsID;
row["VariableUnitsName"] = v.VariableUnitsName;
row["SampleMedium"] = v.SampleMedium;
row["ValueType"] = v.ValueType;
row["TimeSupport"] = v.TimeSupport;
row["TimeUnitsID"] = v.TimeUnitsID;
row["TimeUnitsName"] = "Day"; // todo get from DB !!!
row["DataType"] = v.DataType;
row["GeneralCategory"] = v.GeneralCategory;
row["MethodID"] = m.MethodID;
row["MethodDescription"] = m.MethodDescription;
row["SourceID"] = source.SourceID;
row["Organization"] = source.Organization;
row["SourceDescription"] = source.SourceDescription;
row["Citation"] = source.Citation;
row["QualityControlLevelID"] = 1;
row["QualityControlLevelCode"] = "1";
row["BeginDateTime"] = seriesList[i].BeginDateTime;
row["EndDateTime"] = seriesList[i].EndDateTime;
row["BeginDateTimeUTC"] = seriesList[i].BeginDateTime;
row["EndDateTimeUTC"] = seriesList[i].EndDateTime;
row["ValueCount"] = seriesList[i].ValueCount;
bulkTable.Rows.Add(row);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);
bulkCopy.DestinationTableName = "dbo.SeriesCatalog";
connection.Open();
bulkCopy.WriteToServer(bulkTable);
connection.Close();
Console.WriteLine("SeriesCatalog inserted row " + batchEnd.ToString());
}
Console.WriteLine("UpdateSeriesCatalog: " + seriesList.Count.ToString() + " series updated.");
_log.LogWrite("UpdateSeriesCatalog: " + seriesList.Count.ToString() + " series updated.");
}
catch (Exception ex)
{
_log.LogWrite("UpdateSeriesCatalog ERROR: " + ex.Message);
}
}
}
public void UpdateSites_fast()
{
var client = new AwdbClient(_log);
List <Site> siteList = client.GetStations();
_log.LogWrite("UpdateSites for " + siteList.Count.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(siteList.Count, connection);
// the batch size can be adjusted
int batchSize = 500;
long siteID = 0L;
int numBatches = (siteList.Count / 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 >= siteList.Count)
{
batchEnd = siteList.Count;
}
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].Latitude;
row["Longitude"] = siteList[i].Longitude;
row["LatLongDatumID"] = 3; // WGS1984
row["Elevation_m"] = siteList[i].Elevation;
row["VerticalDatum"] = "Unknown";
row["LocalX"] = 0.0f;
row["LocalY"] = 0.0f;
row["LocalProjectionID"] = DBNull.Value;
row["PosAccuracy_m"] = 0.0f;
row["State"] = siteList[i].State;
row["County"] = siteList[i].County;
row["Comments"] = siteList[i].Comments;
row["SiteType"] = "Soil hole"; // 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: " + siteList.Count.ToString() + " sites updated.");
}
catch (Exception ex)
{
_log.LogWrite("UpdateSites ERROR: " + ex.Message);
}
}