public ICollection<AvailableWaterDataByStation> FetchCurrentDataTypes()
{
List<AvailableWaterDataByStation> stationDataTypes = new List<AvailableWaterDataByStation>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand dataTypeLookup = new SqlCommand(string.Format("select * from AvailableWaterDataTypes"), conn))
using (SqlDataReader reader = dataTypeLookup.ExecuteReader())
{
while (reader.Read())
{
AvailableWaterDataByStation record = new AvailableWaterDataByStation();
record.DataID = reader.GetInt64(0);
record.StationID = reader.GetInt64(1);
long typeID = reader.GetInt64(2);
record.ParameterCode = this.GetCode(typeID, WaterCodeType.PARAMETER);
record.StatisticCode = this.GetCode(typeID, WaterCodeType.STATISTIC);
record.CurrentLastDate = reader.GetDateTime(4);
stationDataTypes.Add(record);
}
reader.Close();
}
}
return stationDataTypes;
}
public void UpdateAvailableDataTypes(AvailableWaterDataByStation dataType)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
// Create and execute an SQL statement.
SqlCommand command = new SqlCommand(null, conn);
command.CommandText = "update AvailableWaterDataTypes set ToDate=@date where ID=@ID";
command.Parameters.AddWithValue("@ID", dataType.DataID);
command.Parameters.Add("@date", SqlDbType.DateTime);
command.Parameters["@date"].Value = dataType.CurrentLastDate;
command.ExecuteNonQuery();
}
}
public ICollection<AvailableWaterDataByStation> FetchBestDataTypesForStationDate(ICollection<WaterStation> stationIDs, DateTime imageDate)
{
List<AvailableWaterDataByStation> stationDataTypes = new List<AvailableWaterDataByStation>();
Boolean found = false;
int stationIndex = 0;
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
while (!found && stationIDs.Count > stationIndex) {
using (SqlCommand dataTypeLookup = new SqlCommand(string.Format("select * from AvailableWaterDataTypes where Station_ID = {0}", stationIDs.ElementAt(stationIndex).ID), conn))
using (SqlDataReader reader = dataTypeLookup.ExecuteReader())
{
while (reader.Read())
{
if (imageDate.CompareTo(reader.GetDateTime(4)) <= 0)
{
AvailableWaterDataByStation record = new AvailableWaterDataByStation();
record.DataID = reader.GetInt64(0);
record.StationID = reader.GetInt64(1);
long typeID = reader.GetInt64(2);
record.ParameterCode = this.GetCode(typeID, WaterCodeType.PARAMETER);
record.StatisticCode = this.GetCode(typeID, WaterCodeType.STATISTIC);
record.CurrentLastDate = reader.GetDateTime(4);
stationDataTypes.Add(record);
found = true;
}
}
reader.Close();
}
stationIndex++;
} // End while not found
}
return stationDataTypes;
}