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