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