public List <V_HMeasure> GetMeasuresInRoom(string room, DateTime start, DateTime end)
{
SqlParameter[] parms = { new SqlParameter("@RoomId", SqlDbType.VarChar, 100),
new SqlParameter("@Start", SqlDbType.DateTime),
new SqlParameter("@End", SqlDbType.DateTime) };
parms[0].Value = SqlTypeConverter.DBNullStringChecker(room);
parms[1].Value = SqlTypeConverter.DBNullDateTimeHandler(start);
parms[2].Value = SqlTypeConverter.DBNullDateTimeHandler(end);
var entities = new List <V_HMeasure>();
using (var rdr = SqlHelper.ExecuteReader(this._databaseConnectionString, CommandType.Text, SqlCommands_Cs.Sql_V_HMeasure_Repository_GetMeasuresInRoom, parms)) {
while (rdr.Read())
{
var entity = new V_HMeasure();
entity.AreaId = SqlTypeConverter.DBNullStringHandler(rdr["AreaId"]);
entity.StationId = SqlTypeConverter.DBNullStringHandler(rdr["StationId"]);
entity.RoomId = SqlTypeConverter.DBNullStringHandler(rdr["RoomId"]);
entity.FsuId = SqlTypeConverter.DBNullStringHandler(rdr["FsuId"]);
entity.DeviceId = SqlTypeConverter.DBNullStringHandler(rdr["DeviceId"]);
entity.PointId = SqlTypeConverter.DBNullStringHandler(rdr["PointId"]);
entity.Type = SqlTypeConverter.DBNullInt32Handler(rdr["Type"]);
entity.Value = SqlTypeConverter.DBNullDoubleHandler(rdr["Value"]);
entity.UpdateTime = SqlTypeConverter.DBNullDateTimeHandler(rdr["UpdateTime"]);
entities.Add(entity);
}
}
return(entities);
}
public V_HMeasure GetLast(string device, string point, DateTime start, DateTime end)
{
SqlParameter[] parms = { new SqlParameter("@DeviceId", SqlDbType.VarChar, 100),
new SqlParameter("@PointId", SqlDbType.VarChar, 100),
new SqlParameter("@Start", SqlDbType.DateTime),
new SqlParameter("@End", SqlDbType.DateTime) };
parms[0].Value = SqlTypeConverter.DBNullStringChecker(device);
parms[1].Value = SqlTypeConverter.DBNullStringChecker(point);
parms[2].Value = SqlTypeConverter.DBNullDateTimeHandler(start);
parms[3].Value = SqlTypeConverter.DBNullDateTimeHandler(end);
V_HMeasure entity = null;
using (var rdr = SqlHelper.ExecuteReader(this._databaseConnectionString, CommandType.Text, SqlCommands_Cs.Sql_V_HMeasure_Repository_GetLast, parms)) {
if (rdr.Read())
{
entity = new V_HMeasure();
entity.AreaId = SqlTypeConverter.DBNullStringHandler(rdr["AreaId"]);
entity.StationId = SqlTypeConverter.DBNullStringHandler(rdr["StationId"]);
entity.RoomId = SqlTypeConverter.DBNullStringHandler(rdr["RoomId"]);
entity.FsuId = SqlTypeConverter.DBNullStringHandler(rdr["FsuId"]);
entity.DeviceId = SqlTypeConverter.DBNullStringHandler(rdr["DeviceId"]);
entity.PointId = SqlTypeConverter.DBNullStringHandler(rdr["PointId"]);
entity.SignalDesc = SqlTypeConverter.DBNullStringHandler(rdr["SignalDesc"]);
entity.Type = SqlTypeConverter.DBNullInt32Handler(rdr["Type"]);
entity.Value = SqlTypeConverter.DBNullDoubleHandler(rdr["Value"]);
entity.UpdateTime = SqlTypeConverter.DBNullDateTimeHandler(rdr["UpdateTime"]);
}
}
return(entity);
}
public List <V_HMeasure> GetMeasuresInPoints(string device, string[] points, DateTime start, DateTime end)
{
if (points == null || points.Length == 0)
{
throw new ArgumentNullException("points");
}
var keys = string.Join(",", points.Select(p => string.Format("''{0}''", p)));
var query = string.Format(@"
DECLARE @tpDate DATETIME,
@tbName NVARCHAR(255),
@tableCnt INT = 0,
@SQL NVARCHAR(MAX) = N'';
SET @tpDate = @Start;
WHILE(DATEDIFF(MM,@tpDate,@End)>=0)
BEGIN
SET @tbName = N'[dbo].[V_HMeasure'+CONVERT(VARCHAR(6),@tpDate,112)+ N']';
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(@tbName) AND type in (N'U'))
BEGIN
IF(@tableCnt>0)
BEGIN
SET @SQL += N'
UNION ALL
';
END
SET @SQL += N'SELECT * FROM ' + @tbName + N' WHERE [UpdateTime] BETWEEN ''' + CONVERT(NVARCHAR,@Start,120) + N''' AND ''' + CONVERT(NVARCHAR,@End,120) + N''' AND [DeviceId] = ''' + @DeviceId + N''' AND [PointId] IN ({0})';
SET @tableCnt += 1;
END
SET @tpDate = DATEADD(MM,1,@tpDate);
END
IF(@tableCnt>0)
BEGIN
SET @SQL = N';WITH HisValue AS
(
' + @SQL + N'
)
SELECT * FROM HisValue ORDER BY [UpdateTime];'
END
EXECUTE sp_executesql @SQL;", keys);
SqlParameter[] parms = { new SqlParameter("@DeviceId", SqlDbType.VarChar, 100),
new SqlParameter("@Start", SqlDbType.DateTime),
new SqlParameter("@End", SqlDbType.DateTime) };
parms[0].Value = SqlTypeConverter.DBNullStringChecker(device);
parms[1].Value = SqlTypeConverter.DBNullDateTimeHandler(start);
parms[2].Value = SqlTypeConverter.DBNullDateTimeHandler(end);
var entities = new List <V_HMeasure>();
using (var rdr = SqlHelper.ExecuteReader(this._databaseConnectionString, CommandType.Text, query, parms)) {
while (rdr.Read())
{
var entity = new V_HMeasure();
entity.AreaId = SqlTypeConverter.DBNullStringHandler(rdr["AreaId"]);
entity.StationId = SqlTypeConverter.DBNullStringHandler(rdr["StationId"]);
entity.RoomId = SqlTypeConverter.DBNullStringHandler(rdr["RoomId"]);
entity.FsuId = SqlTypeConverter.DBNullStringHandler(rdr["FsuId"]);
entity.DeviceId = SqlTypeConverter.DBNullStringHandler(rdr["DeviceId"]);
entity.PointId = SqlTypeConverter.DBNullStringHandler(rdr["PointId"]);
entity.Type = SqlTypeConverter.DBNullInt32Handler(rdr["Type"]);
entity.Value = SqlTypeConverter.DBNullDoubleHandler(rdr["Value"]);
entity.UpdateTime = SqlTypeConverter.DBNullDateTimeHandler(rdr["UpdateTime"]);
entities.Add(entity);
}
}
return(entities);
}
