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