public ArrayList getPastPowerstripData(int powerstripNumber, Int64 startTicks, Int64 endTicks) { ArrayList result = new ArrayList(); DateTime startDate = new DateTime(startTicks); var start = startDate.Date; DateTime endDate = new DateTime(endTicks); var end = endDate.Date; SqlConnection conn = new System.Data.SqlClient.SqlConnection(); try { string connectionString = ConfigurationManager.ConnectionStrings["KnightWatchDBConnection"].ConnectionString; conn.ConnectionString = connectionString; conn.Open(); String sqlQuery = "select power_kw, max_power_kw, energy_kwh, entry_date, entry_time from powerstrip p where powerstrip_number = @powerstripnumber and entry_date between @startDate and @endDate"; SqlCommand command = new SqlCommand(sqlQuery, conn); SqlParameter powerstripNumb = command.Parameters.AddWithValue("@powerstripnumber", powerstripNumber); SqlParameter start_d = command.Parameters.AddWithValue("@startDate", start); SqlParameter end_d = command.Parameters.AddWithValue("@endDate", end); using (SqlDataReader commandReader = command.ExecuteReader()) { while (commandReader.Read()) { String dateOnly = commandReader["entry_date"].ToString(); String timeOnly = commandReader["entry_time"].ToString(); DateTime d = DateTime.Parse(dateOnly.Substring(0, dateOnly.IndexOf(" ")) + " " + timeOnly); if (DateTime.Compare(d, startDate) >= 0 && DateTime.Compare(d, endDate) <= 0) { PowerStrip powerStrip = new PowerStrip(); powerStrip.power = Double.Parse(commandReader["power_kw"].ToString()); powerStrip.max_power = Double.Parse(commandReader["max_power_kw"].ToString()); powerStrip.energy = Double.Parse(commandReader["energy_kwh"].ToString()); result.Add(powerStrip); } } } } catch (Exception ex) { Console.WriteLine(ex.Message); } conn.Close(); return result; }
public PowerStrip getCurrentPowerstripData(int powerstripNumber) { PowerStrip powerStrip = new PowerStrip(); SqlConnection conn = new System.Data.SqlClient.SqlConnection(); try { string connectionString = ConfigurationManager.ConnectionStrings["KnightWatchDBConnection"].ConnectionString; conn.ConnectionString = connectionString; conn.Open(); String sqlQuery = "select power_kw, max_power_kw, energy_kwh from powerstrip p where powerstrip_number = @powerstripnumber and entry_date = (select max(entry_date) from powerstrip where powerstrip_number = @powerstripnumber) and entry_time = (select max(entry_time) from powerstrip p1 where p1.entry_date = p.entry_date and powerstrip_number = @powerstripnumber)"; SqlCommand command = new SqlCommand(sqlQuery, conn); SqlParameter powerstripNumb = command.Parameters.AddWithValue("@powerstripnumber", powerstripNumber); SqlDataReader commandReader = command.ExecuteReader(); while (commandReader.Read()) { powerStrip.power = Double.Parse(commandReader["power_kw"].ToString()); powerStrip.max_power = Double.Parse(commandReader["max_power_kw"].ToString()); powerStrip.energy = Double.Parse(commandReader["energy_kwh"].ToString()); } } catch (Exception ex) { Console.WriteLine(ex.Message); } conn.Close(); return powerStrip; }