public List <WindRecord> GetListBetweenDate(DateTime startDate, DateTime endDate) { string cmdText = string.Format("select * from " + dbToUse + " WHERE imei=" + imei + " AND time between {0} order by time desc", (isMySqlDB ? "?start and ?end" : "@start and @end")); using (DbConnection conn = GetDbConnection(GetDBConnString())) { List <WindRecord> list = new List <WindRecord>(); conn.Open(); using (DbCommand cmd = GetDBCommand(cmdText, conn)) { cmd.Parameters.Add(GetDBParam("start", startDate)); cmd.Parameters.Add(GetDBParam("end", endDate)); DbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { WindRecord wr = new WindRecord(); wr.Time = Convert.ToDateTime(reader["time"]); wr.AverageDirection = int.Parse(reader["averageDir"].ToString()); wr.MaxDirection = int.Parse(reader["maxDir"].ToString()); wr.MinDirection = int.Parse(reader["minDir"].ToString()); wr.AverageSpeed = float.Parse(reader["averageSpeed"].ToString()); wr.MaxSpeed = float.Parse(reader["maxSpeed"].ToString()); wr.MinSpeed = float.Parse(reader["minSpeed"].ToString()); list.Add(wr); } } return(list); } }
public WindRecord GetCurrentWind() { WindRecord currWind = new WindRecord(); using (DbConnection conn = GetDbConnection(GetDBConnString())) using (DbCommand cmd = GetDBCommand("select * from " + dbToUse + " WHERE imei=" + imei + " order by time desc limit 0,1", conn)) { conn.Open(); DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { currWind.Time = Convert.ToDateTime(reader["time"]); currWind.AverageDirection = int.Parse(reader["averageDir"].ToString()); currWind.MaxDirection = int.Parse(reader["maxDir"].ToString()); currWind.MinDirection = int.Parse(reader["minDir"].ToString()); currWind.AverageSpeed = float.Parse(reader["averageSpeed"].ToString()); currWind.MaxSpeed = float.Parse(reader["maxSpeed"].ToString()); currWind.MinSpeed = float.Parse(reader["minSpeed"].ToString()); // TODO, uncomment next two lines when the database has posts for temperature //currWind.AverageAirTemp = int.Parse(reader["averageAirTemp"].ToString()); //currWind.AverageWaterTemp = int.Parse(reader["averageWaterTemp"].ToString()); } } return(currWind); }
public List <WindRecord> GetFullList() { string cmdText = "select * from " + dbToUse + " imei=" + imei + "order by time"; using (DbConnection conn = GetDbConnection(GetDBConnString())) { conn.Open(); List <WindRecord> list = new List <WindRecord>(); using (DbCommand cmd = GetDBCommand(cmdText, conn)) { DbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { WindRecord wr = new WindRecord(); wr.Time = Convert.ToDateTime(reader["time"]); wr.AverageDirection = int.Parse(reader["averageDir"].ToString()); wr.MaxDirection = int.Parse(reader["maxDir"].ToString()); wr.MinDirection = int.Parse(reader["minDir"].ToString()); wr.AverageSpeed = float.Parse(reader["averageSpeed"].ToString()); wr.MaxSpeed = float.Parse(reader["maxSpeed"].ToString()); wr.MinSpeed = float.Parse(reader["minSpeed"].ToString()); list.Add(wr); } } return(list); } }
public List <WindRecord> GetListBetweenDate2(DateTime startDate, DateTime endDate) { TimeSpan interval = endDate.Subtract(startDate); TimeSpan t = new TimeSpan(interval.Ticks / 50); endDate = startDate.Add(t); List <WindRecord> list = new List <WindRecord>(); try { using (DbConnection conn = GetDbConnection(GetDBConnString())) { conn.Open(); for (int i = 0; i < 50; i++) { string cmdText = string.Format("select avg(averageDir),avg(maxDir),avg(minDir),avg(averageSpeed),avg(maxSpeed),avg(minSpeed) from " + dbToUse + " WHERE imei=" + imei + " AND time >\"" + startDate.ToString() + "\" and time <\"" + endDate.ToString() + "\" order by time desc"); using (DbCommand cmd = GetDBCommand(cmdText, conn)) { DbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { WindRecord wr = new WindRecord(); //wr.Time = Convert.ToDateTime(reader["time"]); if (reader["avg(averageDir)"] != null) { string test = reader["avg(averageDir)"].ToString(); if (test.Equals("null") || test.Equals("")) { continue; } wr.Time = endDate;// wr.AverageDirection = (int)float.Parse(reader["avg(averageDir)"].ToString()); wr.MaxDirection = (int)float.Parse(reader["avg(maxDir)"].ToString()); wr.MinDirection = (int)float.Parse(reader["avg(minDir)"].ToString()); wr.AverageSpeed = float.Parse(reader["avg(averageSpeed)"].ToString()); wr.MaxSpeed = float.Parse(reader["avg(maxSpeed)"].ToString()); wr.MinSpeed = float.Parse(reader["avg(minSpeed)"].ToString()); list.Add(wr); } else { } } reader.Close(); } startDate = startDate.Add(t); endDate = endDate.Add(t); } list.Reverse(); } } catch (Exception e) { int a = 0; a++; } return(list); }