public void ProcessData([ServiceBusTrigger("PowerScout_HalfHours")] PowerScoutData powerScoutData, TextWriter logger) { using (SqlConnection sqlConnection = new SqlConnection(ApplicationConfiguration.SqlConnectionString)) { if (!this.buildings.ContainsKey(powerScoutData.Building)) { var buildingId = this.powerScoutService.AddBuilding(powerScoutData.Building); this.buildings.Add(powerScoutData.Building, buildingId); } if (!this.powerScouts.Any(p => p.Equals(powerScoutData.PowerScout))) { var meterInfo = new MeterInfo { Breaker_details = powerScoutData.Breaker_details, PowerScout = powerScoutData.PowerScout, BuildingId = this.buildings[powerScoutData.Building], UTCConversionTime = powerScoutData.UTCConversionTime }; this.powerScoutService.AddPowerScout(meterInfo); } this.powerScoutService.AddPowerScoutData(powerScoutData); } }
public void AddPowerScoutData(PowerScoutData powerScoutData) { var powerScoutDataQuery = @"INSERT INTO LiveData(AMPS_SYSTEM_AVG,Breaker_details,Building,Daily_electric_cost,Daily_KWH_System,Monthly_electric_cost,Monthly_KWH_System,PowerScout,Temperature,Timestamp,Visibility,kW_System,days,time_period,PiServerName, ClassOccupiedValue, Relative_humidity, ClassOccupanyRemaining, TotalClassCapacity, isClassOccupied, Pressure)VALUES (@AMPS_SYSTEM_AVG,@Breaker_details,@Building,@Daily_electric_cost,@Daily_KWH_System,@Monthly_electric_cost,@Monthly_KWH_System,@PowerScout,@Temperature,@Timestamp,@Visibility,@kW_System,@days,@time_period,@PiServerName, @ClassOccupiedValue, @Relative_humidity, @ClassOccupanyRemaining, @TotalClassCapacity, @isClassOccupied, @Pressure)"; using (SqlConnection sqlConnection = new SqlConnection(ApplicationConfiguration.SqlConnectionString)) { using (SqlCommand sqlCommand = new SqlCommand(powerScoutDataQuery, sqlConnection)) { sqlCommand.Parameters.Add(new SqlParameter("AMPS_SYSTEM_AVG", powerScoutData.AMPS_SYSTEM_AVG)); sqlCommand.Parameters.Add(new SqlParameter("Breaker_details", powerScoutData.Breaker_details)); sqlCommand.Parameters.Add(new SqlParameter("Building", powerScoutData.Building)); sqlCommand.Parameters.Add(new SqlParameter("Daily_electric_cost", powerScoutData.Daily_electric_cost)); sqlCommand.Parameters.Add(new SqlParameter("Daily_KWH_System", powerScoutData.Daily_KWH_System)); sqlCommand.Parameters.Add(new SqlParameter("Monthly_electric_cost", powerScoutData.Monthly_electric_cost)); sqlCommand.Parameters.Add(new SqlParameter("Monthly_KWH_System", powerScoutData.Monthly_KWH_System)); sqlCommand.Parameters.Add(new SqlParameter("PowerScout", powerScoutData.PowerScout)); sqlCommand.Parameters.Add(new SqlParameter("Temperature", powerScoutData.Temperature)); sqlCommand.Parameters.Add(new SqlParameter("Timestamp", powerScoutData.Timestamp.ToString(ApplicationConstant.DateTimeFormat))); sqlCommand.Parameters.Add(new SqlParameter("Visibility", powerScoutData.Visibility)); sqlCommand.Parameters.Add(new SqlParameter("kW_System", powerScoutData.KwSystem)); sqlCommand.Parameters.Add(new SqlParameter("days", powerScoutData.Timestamp.ToString("dddd"))); sqlCommand.Parameters.Add(new SqlParameter("time_period", this.GetTimePriodFromTimestamp(powerScoutData.Timestamp))); sqlCommand.Parameters.Add(new SqlParameter("PiServerName", powerScoutData.PiServerName)); sqlCommand.Parameters.Add(new SqlParameter("ClassOccupiedValue", powerScoutData.ClassOccupiedValue)); sqlCommand.Parameters.Add(new SqlParameter("Relative_humidity", powerScoutData.Relative_humidity)); sqlCommand.Parameters.Add(new SqlParameter("ClassOccupanyRemaining", powerScoutData.ClassOccupanyRemaining)); sqlCommand.Parameters.Add(new SqlParameter("TotalClassCapacity", powerScoutData.TotalClassCapacity)); sqlCommand.Parameters.Add(new SqlParameter("isClassOccupied", powerScoutData.IsClassOccupied)); sqlCommand.Parameters.Add(new SqlParameter("Pressure", powerScoutData.Pressure)); sqlCommand.ExecuteNonQuery(); } sqlConnection.Close(); } }