// Return number of records inserted or found.
public static int HandleVariableInfo(SqlConnection sqlConn, VariableInfoType varInfo)
{
VariablesTableAdapter varAdapter = new VariablesTableAdapter();
varAdapter.Connection = sqlConn;
OD_1_1_1DataSet.VariablesDataTable tblVariables = new OD_1_1_1DataSet.VariablesDataTable();
Console.WriteLine(">>>Parsing and inserting VARIABLES");
InsertOneVariable(tblVariables, varInfo, sqlConn);
try
{
varAdapter.Update(tblVariables);
}
catch (Exception e)
{
Console.WriteLine("Failed to insert VARIABLE {0}: {1}",
varInfo.variableCode[0].Value, e.Message);
return(0);
}
return(1);
//PrintTable(varAdapter, tblVariables);
}
static void PrintTable(VariablesTableAdapter dtAdapter, OD_1_1_1DataSet.VariablesDataTable dt)
{
dtAdapter.Fill(dt);
// Print out the column names.
for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
{
Console.Write(dt.Columns[curCol].ColumnName + "\t");
}
Console.WriteLine("\n----------------------------------");
// Print the DataTable.
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
{
Console.Write(dt.Rows[curRow][curCol].ToString() + "\t");
}
Console.WriteLine();
}
}
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);
}
