static bool InsertOneSite(OD_1_1_1DataSet.SitesDataTable sitesTable,
SiteInfoType stinfo, SqlConnection sqlConn)
{
OD_1_1_1DataSet.SitesRow row = sitesTable.NewSitesRow();
row.SiteCode = stinfo.siteCode[0].network + "|" + stinfo.siteCode[0].Value;
string cond = "SiteCode = '" + row.SiteCode + "'";
if (OD_Utils.Exists(row.Table.TableName, cond, sqlConn))
{
return(false);
}
row.SiteName = stinfo.siteName;
LatLonPointType glt = (LatLonPointType)stinfo.geoLocation.geogLocation;
row.Latitude = glt.latitude;
row.Longitude = glt.longitude;
row.LatLongDatumID = 0;
row.Elevation_m = stinfo.elevation_m;
if (stinfo.verticalDatum != null)
{
row.VerticalDatum = stinfo.verticalDatum;
}
row.LocalProjectionID = 0;
//row.LocalX = 0;
//row.LocalY = 0;
//row.LocalProjectionID = 0;
//row.PosAccuracy_m = 0;
if (stinfo.note != null)
{
for (int i = 0; i < stinfo.note.Count(); i++)
{
NoteType note = stinfo.note[i];
switch (note.title)
{
case "State":
row.State = note.Value;
break;
case "County":
row.County = note.Value;
break;
case "agency":
row.Comments = note.Value;
break;
}
}
}
sitesTable.AddSitesRow(row);
return(true);
}
// No one single data source provides enough data for Sources table,
// so try all resources to put them together.
public static void HandleSourceInfo(SqlConnection sqlConn,
SiteInfoType stinfo, seriesCatalogTypeSeries scts, TimeSeriesType tst)
{
string cond = "SourceID = " + scts.source.sourceID;
if (OD_Utils.Exists("Sources", cond, sqlConn))
{
return;
}
SourcesTableAdapter srcAdapter = new SourcesTableAdapter();
srcAdapter.Connection = sqlConn;
int metadataID = 0;
string title = "Unknown";
OD_1_1_1DataSet.SourcesDataTable tblSources = new OD_1_1_1DataSet.SourcesDataTable();
// We currently don't have any information about ISOMetaDataTable. Just create
// an unkown entry to resolve foreign key dependency.
if (scts.source.metadata != null)
{
title = scts.source.metadata.title;
}
cond = string.Format("Title = '{0}'", title);
metadataID = OD_Utils.GetPrimaryKey("ISOMetadata", "MetadataID", cond, sqlConn);
if (metadataID < 0)
{
InsertOneMetadata(scts.source.metadata, sqlConn);
metadataID = OD_Utils.GetPrimaryKey("ISOMetadata", "MetadataID", cond, sqlConn);
}
Console.WriteLine(">>>Parsing and inserting SOURCES");
InsertOneSource(tblSources, stinfo, scts, tst, metadataID, sqlConn);
//srcAdapter.Update(tblSources);
//PrintTable(srcAdapter, tblSites);
}
// Return number of records inserted or found.
public static int HandleMethodsInfo(SqlConnection sqlConn, MethodType m)
{
Console.WriteLine(">>>Parsing and inserting METHODS");
// Check if MethodID is already in the table or not
string cond = "MethodID = " + m.methodID;
if (OD_Utils.Exists("Methods", cond, sqlConn))
{
return(1);
}
string sql = string.Format(@"SET IDENTITY_INSERT [Methods] ON;
INSERT INTO Methods (MethodID, MethodDescription, MethodLink)
VALUES ({0}, '{1}', '{2}');
SET IDENTITY_INSERT [Methods] OFF",
m.methodID,
m.methodDescription,
m.methodLink);
return(OD_Utils.RunNonQuery(sql, sqlConn));
}
static void InsertOneVariable(OD_1_1_1DataSet.VariablesDataTable tblVariables,
VariableInfoType varInfo, SqlConnection sqlConn)
{
string cond;
OD_1_1_1DataSet.VariablesRow row = tblVariables.NewVariablesRow();
string[] vars = Regex.Split(varInfo.variableCode[0].Value, "/");
row.DataType = varInfo.dataType;
//row.DataType = OD_Utils.ConvertToString(varInfo.dataType);
row.VariableCode = vars[0] + "_" + row.DataType;
// Check if the variable is already in the table or not
//??? need to add ValueType filter
cond = "VariableCode = '" + row.VariableCode + "'";
if (OD_Utils.Exists(row.Table.TableName, cond, sqlConn))
{
return;
}
// VariableName is a tricky one too
//??? I think VariableCode is enough to differentiate different variables
cond = "Term = '" + varInfo.variableName + "'";
if (OD_Utils.Exists("VariableNameCV", cond, sqlConn))
{
// Found it and use it
row.VariableName = varInfo.variableName;
}
else
{
string myString = varInfo.variableName;
System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(@"\bas\b");
string[] splitString = reg.Split(myString);
// Refer to the document, e.g.,
// including "as", eg., "Nitrogen, nitrate (NO3) as N, filtered"
// fix ing "Calcium, filtered as Ca"
if (splitString.Length == 2)
{
row.VariableName = splitString[0];
}
else
{
// Remove the string after ","
vars = Regex.Split(varInfo.variableName, ",");
row.VariableName = vars[0];
}
}
row.Speciation = varInfo.speciation; // "Not Applicable"; //?
// Note: in DavtaValue Response Type units.unitsCode is actually unitsAbbreviation
if (varInfo.unit.unitCode != null)
{
//cond = "UnitsName = '" + varInfo.unit.unitName + "'";
//row.VariableUnitsID = OD_Utils.GetPrimaryKey("Units", "UnitsID", cond, sqlConn);
//if (row.VariableUnitsID >= 143) row.VariableUnitsID = row.VariableUnitsID + 1;
row.VariableUnitsID = Convert.ToInt32(varInfo.unit.unitCode);
}
else
{
row.VariableUnitsID = varInfo.unit.unitID;
}
row.SampleMedium = varInfo.sampleMedium;
row.ValueType = varInfo.valueType;
row.IsRegular = varInfo.timeScale.isRegular;
row.TimeSupport = varInfo.timeScale.timeSupport;
if (varInfo.timeScale.unit.unitCode != null)
{
row.TimeUnitsID = Convert.ToInt32(varInfo.timeScale.unit.unitCode);
}
else
{
row.TimeUnitsID = varInfo.timeScale.unit.unitID;
}
row.GeneralCategory = varInfo.generalCategory;
if (varInfo.noDataValue != null)
{
row.NoDataValue = varInfo.noDataValue;
}
else
{
row.NoDataValue = -9999;
}
tblVariables.AddVariablesRow(row);
}