public static bool GetRangeFromDatabase(short?ID, ref DateTime start, ref DateTime end) { using (SqlDataReader reader = DataHelper.ExecuteReader("SELECT MIN(TIMESTAMP),MAX(TIMESTAMP) FROM LOG_HDATA" + (ID.HasValue ? " WHERE ID=" + ID.Value : ""))) { if (reader != null) { while (reader.Read()) { if (!reader.IsDBNull(0)) { start = reader.GetDateTime(0); } if (!reader.IsDBNull(1)) { end = reader.GetDateTime(1); } return(true); } } } //start = end = DateTime.MinValue; return(false); }
public static IEnumerable <HistoryData> LoadFromDatabaseAtTime(short?ID, params DateTime[] timeStamps) { StringBuilder sql = new StringBuilder("SELECT "); if (ID == null) { sql.Append("ID,"); } sql.Append(" [TIMESTAMP],[VALUE],M.DATATYPE FROM LOG_HDATA L INNER JOIN META_TAG M ON L.ID=M.TAGID WHERE"); if (ID != null) { sql.Append(" ID=").Append(ID.Value).Append(" AND "); } sql.Append(" [TIMESTAMP] IN("); for (int i = 0; i < timeStamps.Length; i++) { sql.Append("'").Append(timeStamps[i]).Append("',"); } using (SqlDataReader dataReader = DataHelper.ExecuteReader(sql.Append("1)").ToString())) { if (dataReader == null) { yield break; } HistoryData data = HistoryData.Empty; int itime = ID == null ? 0 : 1; int ivalue = ID == null ? 1 : 2; int itype = ID == null ? 2 : 3; while (dataReader.Read()) { data.ID = ID == null?dataReader.GetInt16(0) : ID.Value; data.TimeStamp = dataReader.GetDateTime(itime); switch ((DataType)dataReader.GetByte(itype)) { case DataType.BOOL: data.Value.Boolean = dataReader.GetFloat(ivalue) > 0 ? true : false; break; case DataType.BYTE: data.Value.Byte = Convert.ToByte(dataReader.GetFloat(ivalue)); break; case DataType.WORD: case DataType.SHORT: data.Value.Int16 = Convert.ToInt16(dataReader.GetFloat(ivalue)); break; case DataType.INT: data.Value.Int32 = Convert.ToInt32(dataReader.GetFloat(ivalue)); break; case DataType.FLOAT: data.Value.Single = dataReader.GetFloat(ivalue); break; } yield return(data); } } yield break; }