//If the Device does NOT exist in the table, INSERT new Device into TBLDevices
private string insertIntoDeviceTable(TBLDevice device, string dateUpdated, string dataRegistered)
{
_log.Info("+insertIntoDeviceTable() Insert statement executed.");
return(@" INSERT INTO TBLDevices( SerialNumber, DeviceNo, CompanyID, DeviceID, DateRegistered, DateUpdated, DeviceName,
DeviceRFAddress, DeviceSize, DeviceSizeTotal, DeviceSubType, HighLimit, LowLimit,
DeviceHighThreshold, OverrideHighThreshhold, DeviceLowThreshold, OverrideLowThreshold, Differential, DiffPolarity,
OverrideDifferential, DeviceDrivenInt, NoDataInt, ThresholdForCollection, ErrorRepeatDelay, OverrideErrorRepeatDelay,
DisableInterruptForThisDevice, NoDataInterruptSamples, Height, Length, Width, BottomOutlet,
CapacityOfAdjustment, DeviceCount, SensorOffset, UpdateDevice, RegisterDevice, MarkedForDeletion,
DeviceLinkTableID, Identifier, AlertTypeH, AlertTypeL, AlertTypeD, AlertTypeI,
AlertTypeN, AlertDesc, UnitMeasurement, DTEStatus, DeviceApplication, OverrideDDEM,
DwellingFloors, DwellingUnits, DwellingRooms, DwellingArea, SubstanceID, EquipmentID) " +
" VALUES('" + device.SerialNumber + "', " + device.DeviceNo + ", " + device.CompanyID + ", " + device.DeviceID + ", '" +
dataRegistered + "', '" + dateUpdated + "', '" + device.DeviceName + "', '" + device.DeviceRFAddress + "', " +
device.DeviceSize + ", " + device.DeviceSizeTotal + ", " + device.DeviceSubType + ", " + device.HighLimit + ", " +
device.LowLimit + ", " + device.DeviceHighThreshold + ", " + device.OverrideHighThreshhold + ", " + device.DeviceLowThreshold + ", " +
device.OverrideLowThreshold + ", " + device.Differential + ", " + device.DiffPolarity + ", " + device.OverrideDifferential + ", " +
device.DeviceDrivenInt + ", " + device.NoDataInt + ", " + device.ThresholdForCollection + ", " + device.ErrorRepeatDelay + ", " +
device.OverrideErrorRepeatDelay + ", " + device.DisableInterruptForThisDevice + ", " + device.NoDataInterruptSamples + ", " + device.Height + ", " +
device.Length + ", " + device.Width + ", " + device.BottomOutlet + ", " + device.CapacityOfAdjustment + ", " +
device.DeviceCount + ", " + device.SensorOffset + ", " + device.UpdateDevice + ", " + device.RegisterDevice + ", " +
device.MarkedForDeletion + ", " + device.DeviceLinkTableID + ", '" + device.Identifier + "', " + device.AlertTypeH + ", " +
device.AlertTypeL + ", " + device.AlertTypeD + ", " + device.AlertTypeI + ", " + device.AlertTypeN + ", '" +
device.AlertDesc + "', " + device.UnitMeasurement + ", " + device.DTEStatus + ", " + device.DeviceApplication + ", " +
device.OverrideDDEM + ", " + device.DwellingFloors + ", " + device.DwellingUnits + ", " + device.DwellingRooms + ", " +
device.DwellingArea + ", " + device.SubstanceID + ", " + device.EquipmentID + ")");
}
//SELECT statement to check if the Device exists in the database. (In case a new Device was added on the client machine)
private static int isDeviceExists(TBLDevice device, SqlCommand cmd, SqlConnection connection)
{
_log.Info("+isDeviceExists() Select statement executed.");
int doesDeviceExist = 0;
string SQLSelect = @" SELECT COUNT(*) " +
" FROM TBLDevices " +
" WHERE SerialNumber = '" + device.SerialNumber + "'" +
" AND DeviceNo = " + device.DeviceNo;
using (cmd = new SqlCommand(SQLSelect, connection))
{
doesDeviceExist = (int)cmd.ExecuteScalar();
}
_log.Info("-isDeviceExists() Select statement executed.");
return(doesDeviceExist);
}
//If the Device already exists in the table, UPDATE Device data in TBLDevices with changes made on Client machine
private static string updateDeviceTable(TBLDevice device, string dateUpdated, string dataRegistered)
{
_log.Info("+updateDeviceTable() Update statement executed.");
return(@" UPDATE TBLDevices
SET CompanyID=" + device.CompanyID + ", DeviceID=" + device.DeviceID + ", DateRegistered='" + dataRegistered + "', DateUpdated='" + dateUpdated +
"', DeviceName='" + device.DeviceName + "', DeviceRFAddress='" + device.DeviceRFAddress + "', DeviceSize=" + device.DeviceSize + ", DeviceSizeTotal=" + device.DeviceSizeTotal +
", DeviceSubType=" + device.DeviceSubType + ", HighLimit=" + device.HighLimit + ", LowLimit=" + device.LowLimit + ", DeviceHighThreshold=" + device.DeviceHighThreshold +
", OverrideHighThreshhold=" + device.OverrideHighThreshhold + ", DeviceLowThreshold=" + device.DeviceLowThreshold + ", OverrideLowThreshold=" + device.OverrideLowThreshold + ", Differential=" + device.Differential +
", DiffPolarity=" + device.DiffPolarity + ", OverrideDifferential=" + device.OverrideDifferential + ", DeviceDrivenInt=" + device.DeviceDrivenInt + ", NoDataInt=" + device.NoDataInt +
", ThresholdForCollection=" + device.ThresholdForCollection + ", ErrorRepeatDelay=" + device.ErrorRepeatDelay + ", OverrideErrorRepeatDelay=" + device.OverrideErrorRepeatDelay + ", DisableInterruptForThisDevice=" + device.DisableInterruptForThisDevice +
", NoDataInterruptSamples=" + device.NoDataInterruptSamples + ", Height=" + device.Height + ", Length=" + device.Length + ", Width=" + device.Width +
", BottomOutlet=" + device.BottomOutlet + ", CapacityOfAdjustment=" + device.CapacityOfAdjustment + ", DeviceCount=" + device.DeviceCount + ", SensorOffset=" + device.SensorOffset +
", UpdateDevice=" + device.UpdateDevice + ", RegisterDevice=" + device.RegisterDevice + ", MarkedForDeletion=" + device.MarkedForDeletion + ", DeviceLinkTableID=" + device.DeviceLinkTableID +
", Identifier='" + device.Identifier + "', AlertTypeH=" + device.AlertTypeH + ", AlertTypeL=" + device.AlertTypeL + ", AlertTypeD=" + device.AlertTypeD +
", AlertTypeI=" + device.AlertTypeI + ", AlertTypeN=" + device.AlertTypeN + ", AlertDesc='" + device.AlertDesc + "', UnitMeasurement=" + device.UnitMeasurement +
", DTEStatus=" + device.DTEStatus + ", DeviceApplication=" + device.DeviceApplication + ", OverrideDDEM=" + device.OverrideDDEM + ", DwellingFloors=" + device.DwellingFloors +
", DwellingUnits=" + device.DwellingUnits + ", DwellingRooms=" + device.DwellingRooms + ", DwellingArea=" + device.DwellingArea + ", SubstanceID=" + device.SubstanceID +
", EquipmentID=" + device.EquipmentID +
" WHERE SerialNumber='" + device.SerialNumber +
"' AND DeviceNo=" + device.DeviceNo);
}
