public static VariableInfoType[] GetVariablesFromDb()
{
List<VariableInfoType> variablesList = new List<VariableInfoType>();
string cnn = GetConnectionString();
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sqlVariables = "select var_id, var_name, var_units, interval_h from variables where var_id=8 OR var_id=1";
cmd.CommandText = sqlVariables;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
VariableInfoType varInfo = new VariableInfoType();
int var_id = Convert.ToInt32(dr["var_id"]);
if (var_id == 1)
{
varInfo.dataType = "Incremental";
varInfo.generalCategory = "Climate";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = "Precipitation";
varInfo.speciation = "Not Applicable";
//time support and time unit
varInfo.timeScale = new VariableInfoTypeTimeScale();
varInfo.timeScale.isRegular = true;
varInfo.timeScale.timeSpacingSpecified = false;
varInfo.timeScale.timeSupport = 0.0f;
varInfo.timeScale.timeSupportSpecified = true;
varInfo.timeScale.unit = new UnitsType();
varInfo.timeScale.unit.unitAbbreviation = "d";
varInfo.timeScale.unit.unitCode = "104";
varInfo.timeScale.unit.unitID = 0;
varInfo.timeScale.unit.unitName = "day";
varInfo.timeScale.unit.unitType = "Time";
//variable unit
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = "mm";
varInfo.unit.unitCode = "54";
varInfo.unit.unitDescription = "millimeter";
varInfo.unit.unitID = 1;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = "millimeter";
varInfo.unit.unitType = "Length";
//variable code
varInfo.valueType = "Field Observation";
varInfo.variableCode = new VariableInfoTypeVariableCode[1];
varInfo.variableCode[0] = new VariableInfoTypeVariableCode();
varInfo.variableCode[0].@default = true;
varInfo.variableCode[0].defaultSpecified = true;
varInfo.variableCode[0].Value = Convert.ToString(dr["var_id"]);
varInfo.variableCode[0].vocabulary = ConfigurationManager.AppSettings["vocabulary"];
varInfo.variableCode[0].variableID = Convert.ToInt32(dr["var_id"]);
varInfo.variableName = "Precipitation";
}
else
{
varInfo.dataType = "Continuous";
varInfo.generalCategory = "Climate";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = "Snow";
varInfo.speciation = "Not Applicable";
//time support and time unit
varInfo.timeScale = new VariableInfoTypeTimeScale();
varInfo.timeScale.isRegular = true;
varInfo.timeScale.timeSpacingSpecified = false;
varInfo.timeScale.timeSupport = 0.0f;
varInfo.timeScale.timeSupportSpecified = true;
varInfo.timeScale.unit = new UnitsType();
varInfo.timeScale.unit.unitAbbreviation = "d";
varInfo.timeScale.unit.unitCode = "104";
varInfo.timeScale.unit.unitID = 0;
varInfo.timeScale.unit.unitName = "day";
varInfo.timeScale.unit.unitType = "Time";
//variable unit
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = "cm";
varInfo.unit.unitCode = "47";
varInfo.unit.unitDescription = "centimeter";
varInfo.unit.unitID = 1;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = "centimeter";
varInfo.unit.unitType = "Length";
//variable code
varInfo.valueType = "Field Observation";
varInfo.variableCode = new VariableInfoTypeVariableCode[1];
varInfo.variableCode[0] = new VariableInfoTypeVariableCode();
varInfo.variableCode[0].@default = true;
varInfo.variableCode[0].defaultSpecified = true;
varInfo.variableCode[0].Value = Convert.ToString(dr["var_id"]);
varInfo.variableCode[0].vocabulary = ConfigurationManager.AppSettings["vocabulary"];
varInfo.variableCode[0].variableID = Convert.ToInt32(dr["var_id"]);
varInfo.variableName = "Snow depth";
}
variablesList.Add(varInfo);
}
}
}
return variablesList.ToArray();
}
internal static VariableInfoType GetVariableInfoFromDb(string VariableParameter)
{
VariableInfoType varInfo = new VariableInfoType();
string cnn = GetConnectionString();
string variableId = VariableParameter.Substring(VariableParameter.LastIndexOf(":") + 1);
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sqlVariables = string.Format(
"select var_id, var_name, var_units, interval_h from variables where var_id={0}", variableId);
cmd.CommandText = sqlVariables;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
int var_id = Convert.ToInt32(dr["var_id"]);
if (var_id == 1)
{
varInfo.dataType = "Incremental";
varInfo.generalCategory = "Climate";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = "Precipitation";
varInfo.speciation = "Not Applicable";
//time support and time unit
varInfo.timeScale = new VariableInfoTypeTimeScale();
varInfo.timeScale.isRegular = true;
varInfo.timeScale.timeSpacingSpecified = false;
varInfo.timeScale.timeSupport = 0.0f;
varInfo.timeScale.timeSupportSpecified = true;
varInfo.timeScale.unit = new UnitsType();
varInfo.timeScale.unit.unitAbbreviation = "d";
varInfo.timeScale.unit.unitCode = "104";
varInfo.timeScale.unit.unitID = 0;
varInfo.timeScale.unit.unitName = "day";
varInfo.timeScale.unit.unitType = "Time";
//variable unit
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = "mm";
varInfo.unit.unitCode = "54";
varInfo.unit.unitDescription = "millimeter";
varInfo.unit.unitID = 1;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = "millimeter";
varInfo.unit.unitType = "Length";
//variable code
varInfo.valueType = "Field Observation";
varInfo.variableCode = new VariableInfoTypeVariableCode[1];
varInfo.variableCode[0] = new VariableInfoTypeVariableCode();
varInfo.variableCode[0].@default = true;
varInfo.variableCode[0].defaultSpecified = true;
varInfo.variableCode[0].Value = Convert.ToString(dr["var_id"]);
varInfo.variableCode[0].vocabulary = ConfigurationManager.AppSettings["vocabulary"];
varInfo.variableCode[0].variableID = Convert.ToInt32(dr["var_id"]);
varInfo.variableName = "Precipitation";
}
else
{
varInfo.dataType = "Average";
varInfo.generalCategory = "Climate";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = "Snow";
varInfo.speciation = "Not Applicable";
//time support and time unit
varInfo.timeScale = new VariableInfoTypeTimeScale();
varInfo.timeScale.isRegular = true;
varInfo.timeScale.timeSpacingSpecified = false;
varInfo.timeScale.timeSupport = 1.0f;
varInfo.timeScale.timeSupportSpecified = true;
varInfo.timeScale.unit = new UnitsType();
varInfo.timeScale.unit.unitAbbreviation = "d";
varInfo.timeScale.unit.unitCode = "104";
varInfo.timeScale.unit.unitID = 0;
varInfo.timeScale.unit.unitName = "day";
varInfo.timeScale.unit.unitType = "Time";
//variable unit
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = Convert.ToString(dr["var_units"]);
varInfo.unit.unitCode = "47";
varInfo.unit.unitDescription = "centimeter";
varInfo.unit.unitID = 1;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = "centimeter";
varInfo.unit.unitType = "Length";
//variable code
varInfo.valueType = "Field Observation";
varInfo.variableCode = new VariableInfoTypeVariableCode[1];
varInfo.variableCode[0] = new VariableInfoTypeVariableCode();
varInfo.variableCode[0].@default = true;
varInfo.variableCode[0].defaultSpecified = true;
varInfo.variableCode[0].Value = Convert.ToString(dr["var_id"]);
varInfo.variableCode[0].vocabulary = ConfigurationManager.AppSettings["vocabulary"];
varInfo.variableCode[0].variableID = Convert.ToInt32(dr["var_id"]);
varInfo.variableName = "Snow depth";
}
}
}
}
return varInfo;
}
internal static VariableInfoType GetVariableInfoFromDb(string VariableParameter)
{
VariableInfoType varInfo = new VariableInfoType();
int var_id = VariableCodeToID(VariableParameter);
SetVariableProperties(var_id, varInfo);
return varInfo;
}
internal static void SetVariableProperties(int var_id, VariableInfoType varInfo)
{
//time support and time unit (same for all variables here)
varInfo.timeScale = new VariableInfoTypeTimeScale();
varInfo.timeScale.isRegular = true;
varInfo.timeScale.timeSpacingSpecified = false;
varInfo.timeScale.timeSupport = 1.0f;
varInfo.timeScale.timeSupportSpecified = true;
varInfo.timeScale.unit = new UnitsType();
varInfo.timeScale.unit.unitAbbreviation = "hr";
varInfo.timeScale.unit.unitCode = "103";
varInfo.timeScale.unit.unitID = 103;
varInfo.timeScale.unit.unitName = "hour";
varInfo.timeScale.unit.unitType = "Time";
//variable code (same computation for all variables here)
varInfo.valueType = "Field Observation";
varInfo.variableCode = new VariableInfoTypeVariableCode[1];
varInfo.variableCode[0] = new VariableInfoTypeVariableCode();
varInfo.variableCode[0].@default = true;
varInfo.variableCode[0].defaultSpecified = true;
varInfo.variableCode[0].Value = VariableIDToShortCode(var_id);
varInfo.variableCode[0].vocabulary = ConfigurationManager.AppSettings["vocabulary"];
varInfo.variableCode[0].variableID = var_id;
switch(var_id)
{
case 1:
//precipitation
varInfo.dataType = "Incremental";
varInfo.generalCategory = "Climate";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = "Precipitation";
varInfo.speciation = "Not Applicable";
//variable unit
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = "mm";
varInfo.unit.unitCode = "54";
varInfo.unit.unitDescription = "millimeter";
varInfo.unit.unitID = 1;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = "millimeter";
varInfo.unit.unitType = "Length";
varInfo.variableName = "Precipitation";
break;
case 4:
//water level
varInfo.dataType = "Average";
varInfo.generalCategory = "Hydrology";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = "Water";
varInfo.speciation = "Not Applicable";
//variable unit - centimeter
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = "cm";
varInfo.unit.unitCode = "47";
varInfo.unit.unitDescription = "centimeter";
varInfo.unit.unitID = 47;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = "centimeter";
varInfo.unit.unitType = "Length";
//variable value type and name
varInfo.valueType = "Field Observation";
varInfo.variableName = "Gage height";
break;
case 5:
//discharge
varInfo.dataType = "Average";
varInfo.generalCategory = "Hydrology";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = "Water";
varInfo.speciation = "Not Applicable";
//variable unit - cubic meter per second
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = "m^3/s";
varInfo.unit.unitCode = "36";
varInfo.unit.unitDescription = "cubic meters per second";
varInfo.unit.unitID = 36;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = "cubic meters per second";
varInfo.unit.unitType = "Flow";
//variable value type and name
varInfo.valueType = "Field Observation";
varInfo.variableName = "Discharge";
break;
case 8:
//snow
varInfo.dataType = "Average";
varInfo.generalCategory = "Climate";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = "Snow";
varInfo.speciation = "Not Applicable";
//variable unit
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = "cm";
varInfo.unit.unitCode = "47";
varInfo.unit.unitDescription = "centimeter";
varInfo.unit.unitID = 47;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = "centimeter";
varInfo.unit.unitType = "Length";
//variable value type and name
varInfo.valueType = "Field Observation";
varInfo.variableName = "Snow depth";
break;
case 16:
//temperature
varInfo.dataType = "Average";
varInfo.generalCategory = "Climate";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = "Air";
varInfo.speciation = "Not Applicable";
//variable unit
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = "degC";
varInfo.unit.unitCode = "96";
varInfo.unit.unitDescription = "degree celsius";
varInfo.unit.unitID = 96;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = "degree celsius";
varInfo.unit.unitType = "Temperature";
//variable code
varInfo.valueType = "Field Observation";
varInfo.variableName = "Temperature";
break;
case 17:
//temperature min
varInfo.dataType = "Minimum";
varInfo.generalCategory = "Climate";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = "Air";
varInfo.speciation = "Not Applicable";
//variable unit
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = "degC";
varInfo.unit.unitCode = "96";
varInfo.unit.unitDescription = "degree celsius";
varInfo.unit.unitID = 96;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = "degree celsius";
varInfo.unit.unitType = "Temperature";
//variable code
varInfo.valueType = "Field Observation";
varInfo.variableName = "Temperature";
break;
case 18:
//temperature
varInfo.dataType = "Maximum";
varInfo.generalCategory = "Climate";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = "Air";
varInfo.speciation = "Not Applicable";
//variable unit
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = "degC";
varInfo.unit.unitCode = "96";
varInfo.unit.unitDescription = "degree celsius";
varInfo.unit.unitID = 96;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = "degree celsius";
varInfo.unit.unitType = "Temperature";
//variable code
varInfo.valueType = "Field Observation";
varInfo.variableName = "Temperature";
break;
}
}
public static VariableInfoType[] GetVariablesFromDb()
{
List<VariableInfoType> variablesList = new List<VariableInfoType>();
int[] variableIDs = { 1, 4, 5, 16 };
foreach (int var_id in variableIDs)
{
VariableInfoType varInfo = new VariableInfoType();
SetVariableProperties(var_id, varInfo);
variablesList.Add(varInfo);
}
return variablesList.ToArray();
}
internal static VariableInfoType GetVariableInfoFromDb(string VariableParameter)
{
VariableInfoType varInfo = new VariableInfoType();
int var_id = VariableCodeToID(VariableParameter);
SetVariableProperties(var_id, varInfo);
//string cnn = GetConnectionString();
//string variableId = VariableCodeToID(VariableParameter).ToString();
//using (SqlConnection conn = new SqlConnection(cnn))
//{
// using (SqlCommand cmd = new SqlCommand())
// {
// string sqlVariables = string.Format(
// "select var_id, var_name, var_units, interval_h from variables where var_id={0}", variableId);
// cmd.CommandText = sqlVariables;
// cmd.Connection = conn;
// conn.Open();
// SqlDataReader dr = cmd.ExecuteReader();
// if (dr.HasRows)
// {
// dr.Read();
// int var_id = Convert.ToInt32(dr["var_id"]);
// SetVariableProperties(var_id, varInfo);
// }
// }
//}
return varInfo;
}
public static VariableInfoType[] GetVariablesFromDb()
{
List<VariableInfoType> variablesList = new List<VariableInfoType>();
int[] variableIDs = { 1, 4, 5, 8, 16, 17, 18 };
string cnn = GetConnectionString();
foreach (int var_id in variableIDs)
{
VariableInfoType varInfo = new VariableInfoType();
SetVariableProperties(var_id, varInfo);
variablesList.Add(varInfo);
}
return variablesList.ToArray();
}
internal static VariableInfoType GetVariableInfoFromDb(string VariableParameter)
{
WriteLog("Executing GetVariableInfoFromDb(" + VariableParameter + ")");
VariableInfoType varInfo = new VariableInfoType();
try
{
string cnn = GetConnectionString();
string variableCode = VariableParameter.Substring(VariableParameter.LastIndexOf(":") + 1);
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sqlVariables = string.Format(
"SELECT var_id, VariableCode, VariableName, UnitsName, UnitsAbbrev, DataType, ValueType, " +
"NoDataValue, SampleMedium, UnitsID FROM variables where VariableCode='{0}'", variableCode);
//WriteLog("variableCode:" + variableCode);
cmd.CommandText = sqlVariables;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
varInfo.dataType = dr["DataType"].ToString();
varInfo.generalCategory = "Climate";
varInfo.metadataTimeSpecified = false;
varInfo.noDataValue = -9999.0;
varInfo.noDataValueSpecified = true;
varInfo.sampleMedium = dr["SampleMedium"].ToString();
varInfo.speciation = "Not Applicable";
//time support and time unit
varInfo.timeScale = new VariableInfoTypeTimeScale();
varInfo.timeScale.isRegular = true;
varInfo.timeScale.timeSpacingSpecified = false;
varInfo.timeScale.timeSupport = 1.0f;
varInfo.timeScale.timeSupportSpecified = true;
varInfo.timeScale.unit = new UnitsType();
varInfo.timeScale.unit.unitAbbreviation = "d";
varInfo.timeScale.unit.unitCode = "104";
varInfo.timeScale.unit.unitID = 0;
varInfo.timeScale.unit.unitName = "day";
varInfo.timeScale.unit.unitType = "Time";
//variable unit
varInfo.unit = new UnitsType();
varInfo.unit.unitAbbreviation = Convert.ToString(dr["UnitsAbbrev"]);
varInfo.unit.unitCode = dr["UnitsID"].ToString();
varInfo.unit.unitDescription = dr["UnitsName"].ToString();
varInfo.unit.unitID = 1;
varInfo.unit.unitIDSpecified = true;
varInfo.unit.unitName = dr["UnitsName"].ToString();
varInfo.unit.unitType = "Length";
//variable code
varInfo.valueType = dr["ValueType"].ToString();
varInfo.variableCode = new VariableInfoTypeVariableCode[1];
varInfo.variableCode[0] = new VariableInfoTypeVariableCode();
varInfo.variableCode[0].@default = true;
varInfo.variableCode[0].defaultSpecified = true;
varInfo.variableCode[0].Value = Convert.ToString(dr["VariableCode"]);
varInfo.variableCode[0].vocabulary = ConfigurationManager.AppSettings["vocabulary"];
varInfo.variableCode[0].variableID = Convert.ToInt32(dr["var_id"]);
varInfo.variableName = Convert.ToString(dr["VariableName"]);
}
}
}
catch (Exception ex)
{
WriteLog("GetVariableInfoFromDb - " + ex.Message);
}
return varInfo;
}
public static VariableInfoType[] GetVariablesFromDb()
{
List<VariableInfoType> variablesList = new List<VariableInfoType>();
string cnn = GetConnectionString();
List<string> variableCodes = new List<string>();
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sqlVariables = "SELECT VariableCode from variables WHERE VariableCode in (SELECT var_code FROM series)";
cmd.CommandText = sqlVariables;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string code = dr["VariableCode"].ToString();
variableCodes.Add("HCLIMATE:" + code);
VariableInfoType varInfo = new VariableInfoType();
}
conn.Close();
}
}
foreach (string varCode in variableCodes)
{
VariableInfoType vi = GetVariableInfoFromDb(varCode);
variablesList.Add(vi);
}
return variablesList.ToArray();
}
