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