public List <AlarmInfo> GetAlarms()
{
var alarms = new List <AlarmInfo>();
var sqlText = @"
;WITH Alarms AS
(
SELECT L.[LscID],L.[LscName],TA.[SerialNO],TA.[Area1Name],TA.[Area2Name],TA.[Area3Name],TA.[Area4Name],
TA.[StaName],TA.[DevName],TA.[DevDesc],TA.[NodeID],TA.[NodeType],TA.[NodeName],TA.[AlarmID],TA.[AlarmValue],
TA.[AlarmLevel],TA.[AlarmStatus],TA.[AlarmDesc],TA.[AuxAlarmDesc],TA.[StartTime],TA.[EndTime],TA.[ConfirmName],
TA.[ConfirmMarking],TA.[ConfirmTime],TA.[AuxSet],TA.[ProjName],TA.[TurnCount],TA.[UpdateTime]
FROM [dbo].[TA_Alarm] TA INNER JOIN [dbo].[TM_LSC] L ON TA.[LscID] = L.[LscID]
),
AlarmDetails AS
(
SELECT AN.[ID] AS AlarmID,AN.[AlarmName],AN.[AlarmInterpret],AN.[AlarmVer],AN.[AlarmLevel],
AN.[DevEffect],AN.[OperEffect],AN.[SubAlarmLogTypeID],SALT.[TypeDesc] AS SubAlarmLogType,
SALT.[AlarmLogTypeID],ALT.[TypeDesc] AS AlarmLogType,ALT.[AlarmDeviceTypeID],
ADT.[TypeDesc] AS AlarmDeviceType,AN.[NMAlarmID], AN.[AlarmClass] FROM [dbo].[TC_AlarmName] AN
LEFT OUTER JOIN [dbo].[TC_SubAlarmLogType] SALT ON AN.[SubAlarmLogTypeID] = SALT.[TypeID]
LEFT OUTER JOIN [dbo].[TC_AlarmLogType] ALT ON SALT.[AlarmLogTypeID] = ALT.[TypeID]
LEFT OUTER JOIN [dbo].[TC_AlarmDeviceType] ADT ON ALT.[AlarmDeviceTypeID] = ADT.[TypeID]
)
SELECT A.[LscID],A.[LscName],A.[SerialNO],A.[Area1Name],A.[Area2Name],A.[Area3Name],A.[Area4Name],
A.[StaName],A.[DevName],A.[DevDesc],A.[NodeID],A.[NodeType],A.[NodeName],A.[AlarmID],A.[AlarmValue],
A.[AlarmLevel],A.[AlarmStatus],A.[AlarmDesc],A.[AuxAlarmDesc],A.[StartTime],A.[EndTime],A.[ConfirmName],
A.[ConfirmMarking],A.[ConfirmTime],A.[AuxSet],A.[ProjName],A.[TurnCount],A.[UpdateTime],AD.[NMAlarmID],
AD.[AlarmName],AD.[AlarmClass],AD.[AlarmDeviceTypeID],AD.[AlarmDeviceType],AD.[AlarmLogTypeID],AD.[AlarmLogType]
FROM Alarms A LEFT OUTER JOIN AlarmDetails AD ON A.AlarmID = AD.AlarmID;";
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlText, null)) {
while (rdr.Read())
{
var alarm = new AlarmInfo();
alarm.LscID = ComUtility.DBNullInt32Handler(rdr["LscID"]);
alarm.LscName = ComUtility.DBNullStringHandler(rdr["LscName"]);
alarm.SerialNO = ComUtility.DBNullInt32Handler(rdr["SerialNO"]);
alarm.Area1Name = ComUtility.DBNullStringHandler(rdr["Area1Name"]);
alarm.Area2Name = ComUtility.DBNullStringHandler(rdr["Area2Name"]);
alarm.Area3Name = ComUtility.DBNullStringHandler(rdr["Area3Name"]);
alarm.Area4Name = ComUtility.DBNullStringHandler(rdr["Area4Name"]);
alarm.StaName = ComUtility.DBNullStringHandler(rdr["StaName"]);
alarm.DevName = ComUtility.DBNullStringHandler(rdr["DevName"]);
alarm.DevDesc = ComUtility.DBNullStringHandler(rdr["DevDesc"]);
alarm.NodeID = ComUtility.DBNullInt32Handler(rdr["NodeID"]);
alarm.NodeType = ComUtility.DBNullNodeTypeHandler(rdr["NodeType"]);
alarm.NodeName = ComUtility.DBNullStringHandler(rdr["NodeName"]);
alarm.AlarmID = ComUtility.DBNullInt32Handler(rdr["AlarmID"]);
alarm.AlarmValue = ComUtility.DBNullFloatHandler(rdr["AlarmValue"]);
alarm.AlarmLevel = ComUtility.DBNullAlarmLevelHandler(rdr["AlarmLevel"]);
alarm.AlarmStatus = ComUtility.DBNullAlarmStatusHandler(rdr["AlarmStatus"]);
alarm.AlarmDesc = ComUtility.DBNullStringHandler(rdr["AlarmDesc"]);
alarm.AuxAlarmDesc = ComUtility.DBNullStringHandler(rdr["AuxAlarmDesc"]);
alarm.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
alarm.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
alarm.ConfirmName = ComUtility.DBNullStringHandler(rdr["ConfirmName"]);
alarm.ConfirmMarking = ComUtility.DBNullConfirmMarkingHandler(rdr["ConfirmMarking"]);
alarm.ConfirmTime = ComUtility.DBNullDateTimeHandler(rdr["ConfirmTime"]);
alarm.AuxSet = ComUtility.DBNullStringHandler(rdr["AuxSet"]);
alarm.ProjName = ComUtility.DBNullStringHandler(rdr["ProjName"]);
alarm.TurnCount = ComUtility.DBNullInt32Handler(rdr["TurnCount"]);
alarm.UpdateTime = ComUtility.DBNullDateTimeHandler(rdr["UpdateTime"]);
alarm.NMAlarmID = ComUtility.DBNullStringHandler(rdr["NMAlarmID"]);
alarm.AlarmName = ComUtility.DBNullStringHandler(rdr["AlarmName"]);
alarm.AlarmClass = ComUtility.DBNullStringHandler(rdr["AlarmClass"]);
alarm.AlarmDeviceTypeID = ComUtility.DBNullInt32Handler(rdr["AlarmDeviceTypeID"]);
alarm.AlarmDeviceType = ComUtility.DBNullStringHandler(rdr["AlarmDeviceType"]);
alarm.AlarmLogTypeID = ComUtility.DBNullInt32Handler(rdr["AlarmLogTypeID"]);
alarm.AlarmLogType = ComUtility.DBNullStringHandler(rdr["AlarmLogType"]);
alarms.Add(alarm);
}
}
return(alarms);
}