public ICollection <WaterDataValue> FetchByDateRange(long stationID, long typeID, DateTime startDate, DateTime endDate)
{
List <WaterDataValue> values = new List <WaterDataValue>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand parameterLookup = new SqlCommand(string.Format("select * from WaterValues where Station_ID={0} and DataType_ID={1} and DateTime>='{2}' and DateTime<='{3}'", stationID, typeID, startDate.ToString("yyyy/MM/dd"), endDate.ToString("yyyy/MM/dd")), conn))
using (SqlDataReader reader = parameterLookup.ExecuteReader())
{
while (reader.Read())
{
WaterDataValue record = new WaterDataValue();
record.ID = reader.GetInt64(0);
record.StationID = reader.GetInt64(1);
record.DataTypeID = reader.GetInt64(2);
record.DateOf = reader.GetDateTime(3);
record.Value = reader.GetDouble(4);
values.Add(record);
}
reader.Close();
}//End Using SQL Command
} //End Using SQL Conection
return(values);
}
public void Add(WaterDataValue waterDataValue)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(null, conn);
// Create and prepare an SQL statement.
command.CommandText = "insert into WaterValues (Station_ID, DataType_ID, DateTime, Value) values (@station, @type, @date, @value)";
command.Parameters.Add("@station", SqlDbType.BigInt);
command.Parameters["@station"].Value = waterDataValue.StationID;
command.Parameters.Add("@type", SqlDbType.BigInt);
command.Parameters["@type"].Value = waterDataValue.DataTypeID;
command.Parameters.Add("@date", SqlDbType.DateTime);
command.Parameters.Add("@value", SqlDbType.Float);
command.Parameters["@date"].Value = waterDataValue.DateOf;
command.Parameters["@value"].Value = waterDataValue.Value;
command.Prepare(); // Calling Prepare after having set the Commandtext and parameters.
command.ExecuteNonQuery();
}
}
} //End ImportAndWriteDataValues
private DateTime WriteDataValues(List <string> rows, long stationID, long waterDataTypeID)
{
DateTime resultDate = new DateTime(2009, 1, 1);
float value = -999999;
WaterDataValue waterValue = new WaterDataValue();
waterValue.StationID = stationID;
waterValue.DataTypeID = waterDataTypeID;
foreach (string line in rows)
{
if (line.Equals(""))
{
continue;
}
// split out each column
string[] cols = line.Split('\t');
waterValue.DateOf = DateTime.Parse(cols[2]);
resultDate = DateTime.Parse(cols[2]);
if (float.TryParse(cols[3], out value))
{
waterValue.Value = value;
}
else
{
waterValue.Value = -999999;
}
_repo.Add(waterValue);
} //End foreach line in rows
return(resultDate);
} //End WriteDataValues
