Exemple #1
0
        //Get Connection string from Registry for this Window Service to connect to database to get data
        //Get all Readings data from database, database name for Wcf Web Service from Registry and transform it to Json
        public string GetReadings(string lastRunDate, string remoteDbName, string connectionString)
        {
            _log.Debug("+GetReadings()");
            SqlConnection        connection  = null;
            SqlDataReader        dataReader  = null;
            JavaScriptSerializer jserializer = null;

            string sqlReadings = @"SELECT 
                                        r.CompanyID,
                                        r.GroupID,
                                        r.GroupName,
                                        r.CustTypeID,
                                        r.CustTypeName,
                                        r.DeviceNo,
                                        r.DeviceID,
                                        r.Serial,
                                        r.DateReceived,
                                        r.AuxAnalogue,
                                        r.DeviceLevel,
                                        r.UsableLevel,
                                        r.MeasuredLevel,
                                        r.RecordType,
                                        r.ErrorCondition,
                                        r.ErrorConditionLink,
                                        r.ErrorText,
                                        r.AvgUsage,
                                        r.DaysToEmpty,
                                        r.AccountNumber,
                                        r.LastName,
                                        r.FirstName,
                                        r.Address1,
                                        r.Address2,
                                        r.City,
                                        r.State,
                                        r.ZipCode,
                                        r.Country,
                                        r.HPhone,
                                        r.WPhone,
                                        r.Mobile,
                                        r.Email,
                                        r.DeviceSize,
                                        r.DeviceSizeTotal,
                                        r.DeviceCount,
                                        r.Identifier,
                                        r.SubstanceID,
                                        r.SubstanceName
                                   FROM 
	                                    TBLReadings r
                                   WHERE DateReceived > '" + lastRunDate + "'";

            try
            {
                using (connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(sqlReadings, connection);
                    connection.Open();
                    using (dataReader = command.ExecuteReader())
                    {
                        if (dataReader.HasRows)
                        {
                            while (dataReader.Read())
                            {
                                _numberOfRows++;
                                _readingObj                    = new Reading();
                                _readingObj.CompanyID          = SafeDao.SafeGetInt(dataReader, 0);
                                _readingObj.GroupID            = SafeDao.SafeGetInt(dataReader, 1);
                                _readingObj.GroupName          = SafeDao.SafeGetString(dataReader, 2);
                                _readingObj.CustTypeID         = SafeDao.SafeGetString(dataReader, 3);
                                _readingObj.CustTypeName       = SafeDao.SafeGetString(dataReader, 4);
                                _readingObj.DeviceNo           = SafeDao.SafeGetShort(dataReader, 5);
                                _readingObj.DeviceID           = SafeDao.SafeGetInt(dataReader, 6);
                                _readingObj.Serial             = SafeDao.SafeGetString(dataReader, 7);
                                _readingObj.DateReceived       = SafeDao.SafeGetDateTime(dataReader, 8);
                                _readingObj.AuxAnalogue        = SafeDao.SafeGetInt(dataReader, 9);
                                _readingObj.DeviceLevel        = SafeDao.SafeGetInt(dataReader, 10);
                                _readingObj.UsableLevel        = SafeDao.SafeGetInt(dataReader, 11);
                                _readingObj.MeasuredLevel      = SafeDao.SafeGetInt(dataReader, 12);
                                _readingObj.RecordType         = SafeDao.SafeGetInt(dataReader, 13);
                                _readingObj.ErrorCondition     = SafeDao.SafeGetInt(dataReader, 14);
                                _readingObj.ErrorConditionLink = SafeDao.SafeGetInt(dataReader, 15);
                                _readingObj.ErrorText          = SafeDao.SafeGetString(dataReader, 16);
                                _readingObj.AvgUsage           = SafeDao.SafeGetDouble(dataReader, 17);
                                _readingObj.DaysToEmpty        = SafeDao.SafeGetDouble(dataReader, 18);
                                _readingObj.AccountNumber      = SafeDao.SafeGetString(dataReader, 19);
                                _readingObj.LastName           = SafeDao.SafeGetString(dataReader, 20);
                                _readingObj.FirstName          = SafeDao.SafeGetString(dataReader, 21);
                                _readingObj.Address1           = SafeDao.SafeGetString(dataReader, 22);
                                _readingObj.Address2           = SafeDao.SafeGetString(dataReader, 23);
                                _readingObj.City               = SafeDao.SafeGetString(dataReader, 24);
                                _readingObj.State              = SafeDao.SafeGetString(dataReader, 25);
                                _readingObj.ZipCode            = SafeDao.SafeGetString(dataReader, 26);
                                _readingObj.Country            = SafeDao.SafeGetString(dataReader, 27);
                                _readingObj.HPhone             = SafeDao.SafeGetString(dataReader, 28);
                                _readingObj.WPhone             = SafeDao.SafeGetString(dataReader, 29);
                                _readingObj.Mobile             = SafeDao.SafeGetString(dataReader, 30);
                                _readingObj.Email              = SafeDao.SafeGetString(dataReader, 31);
                                _readingObj.DeviceSize         = SafeDao.SafeGetInt(dataReader, 32);
                                _readingObj.DeviceSizeTotal    = SafeDao.SafeGetInt(dataReader, 33);
                                _readingObj.DeviceCount        = SafeDao.SafeGetInt(dataReader, 34);
                                _readingObj.Identifier         = SafeDao.SafeGetString(dataReader, 35);
                                _readingObj.SubstanceID        = SafeDao.SafeGetInt(dataReader, 36);
                                _readingObj.SubstanceName      = SafeDao.SafeGetString(dataReader, 37);
                                _readingsList.Add(_readingObj);
                            }
                        }
                        dataReader.Close();
                    }
                    connection.Close();
                }
                _log.Debug("-GetReadings() Readings from database: " + _numberOfRows + "rows of data!");
                //Set the composite object with our data..
                JsonReading = new ReadingJson(_readingsList);
                JsonReading.DatabaseName = remoteDbName;

                jserializer = new JavaScriptSerializer();
                jserializer.MaxJsonLength = Int32.MaxValue;
            }
            catch (SqlException ex)
            {
                _log.Error("-GetReadings() Message: " + ex.Message + " InnerException: " + ex.InnerException);
            }
            finally
            {
                if (dataReader != null)
                {
                    dataReader.Close();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }

            _log.Debug("-GetReadings()");
            return(jserializer.Serialize(new { JsonReading }).ToString());
        }
Exemple #2
0
        //Get Connection string from Registry for this Window Service to connect to database to get data
        //Get all Device data from database, database name for Wcf Web Service from Registry and transform it to Json
        public string GetDevices(string lastRunDate, string remoteDbName, string connectionString)
        {
            _log.Debug("+GetDevices()");
            SqlConnection        connection  = null;
            SqlDataReader        dataReader  = null;
            JavaScriptSerializer jserializer = null;

            string sqlDevices = @"SELECT
	                                   d.SerialNumber,
                                       d.DeviceNo,
                                       d.CompanyID,
                                       d.DeviceID,
                                       d.DateRegistered,
                                       d.DateUpdated,
                                       d.DeviceName,
                                       d.DeviceRFAddress,
                                       d.DeviceSize,
                                       d.DeviceSizeTotal,
                                       d.DeviceSubType,
                                       d.HighLimit,
                                       d.LowLimit,
                                       d.DeviceHighThreshold,
                                       d.OverrideHighThreshhold,
                                       d.DeviceLowThreshold,
                                       d.OverrideLowThreshold,
                                       d.Differential,
                                       d.DiffPolarity,
                                       d.OverrideDifferential,
                                       d.DeviceDrivenInt,
                                       d.NoDataInt,
                                       d.ThresholdForCollection,
                                       d.ErrorRepeatDelay,
                                       d.OverrideErrorRepeatDelay,
                                       d.DisableInterruptForThisDevice,
                                       d.NoDataInterruptSamples,
                                       d.Height,
                                       d.Length,
                                       d.Width,
                                       d.BottomOutlet,
                                       d.CapacityOfAdjustment,
                                       d.DeviceCount,
                                       d.SensorOffset,
                                       d.UpdateDevice,
                                       d.RegisterDevice,
                                       d.MarkedForDeletion,
                                       d.DeviceLinkTableID,
                                       d.Identifier,
                                       d.AlertTypeH,
                                       d.AlertTypeL,
                                       d.AlertTypeD,
                                       d.AlertTypeI,
                                       d.AlertTypeN,
                                       d.AlertDesc,
                                       d.UnitMeasurement,
                                       d.DTEStatus,
                                       d.DeviceApplication,
                                       d.OverrideDDEM,
                                       d.DwellingFloors,
                                       d.DwellingUnits,
                                       d.DwellingRooms,
                                       d.DwellingArea,
                                       d.SubstanceID,
                                       d.EquipmentID
                                  FROM 
	                                    TBLDevices d
                                  WHERE d.DateUpdated > '" + lastRunDate + "'";

            try
            {
                using (connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(sqlDevices, connection);
                    connection.Open();
                    using (dataReader = command.ExecuteReader())
                    {
                        if (dataReader.HasRows)
                        {
                            while (dataReader.Read())
                            {
                                _numberOfRows++;
                                _deviceObj = new Device();
                                _deviceObj.SerialNumber                  = SafeDao.SafeGetString(dataReader, 0);
                                _deviceObj.DeviceNo                      = SafeDao.SafeGetInt(dataReader, 1);
                                _deviceObj.CompanyID                     = SafeDao.SafeGetInt(dataReader, 2);
                                _deviceObj.DeviceID                      = SafeDao.SafeGetInt(dataReader, 3);
                                _deviceObj.DateRegistered                = SafeDao.SafeGetDateTime(dataReader, 4);
                                _deviceObj.DateUpdated                   = SafeDao.SafeGetDateTime(dataReader, 5);
                                _deviceObj.DeviceName                    = SafeDao.SafeGetString(dataReader, 6);
                                _deviceObj.DeviceRFAddress               = SafeDao.SafeGetString(dataReader, 7);
                                _deviceObj.DeviceSize                    = SafeDao.SafeGetInt(dataReader, 8);
                                _deviceObj.DeviceSizeTotal               = SafeDao.SafeGetInt(dataReader, 9);
                                _deviceObj.DeviceSubType                 = SafeDao.SafeGetInt(dataReader, 10);
                                _deviceObj.HighLimit                     = SafeDao.SafeGetInt(dataReader, 11);
                                _deviceObj.LowLimit                      = SafeDao.SafeGetInt(dataReader, 12);
                                _deviceObj.DeviceHighThreshold           = SafeDao.SafeGetInt(dataReader, 13);
                                _deviceObj.OverrideHighThreshhold        = SafeDao.SafeGetByte(dataReader, 14);
                                _deviceObj.DeviceLowThreshold            = SafeDao.SafeGetInt(dataReader, 15);
                                _deviceObj.OverrideLowThreshold          = SafeDao.SafeGetByte(dataReader, 16);
                                _deviceObj.Differential                  = SafeDao.SafeGetInt(dataReader, 17);
                                _deviceObj.DiffPolarity                  = SafeDao.SafeGetShort(dataReader, 18);
                                _deviceObj.OverrideDifferential          = SafeDao.SafeGetByte(dataReader, 19);
                                _deviceObj.DeviceDrivenInt               = SafeDao.SafeGetByte(dataReader, 20);
                                _deviceObj.NoDataInt                     = SafeDao.SafeGetByte(dataReader, 21);
                                _deviceObj.ThresholdForCollection        = SafeDao.SafeGetByte(dataReader, 22);
                                _deviceObj.ErrorRepeatDelay              = SafeDao.SafeGetInt(dataReader, 23);
                                _deviceObj.OverrideErrorRepeatDelay      = SafeDao.SafeGetByte(dataReader, 24);
                                _deviceObj.DisableInterruptForThisDevice = SafeDao.SafeGetByte(dataReader, 25);
                                _deviceObj.NoDataInterruptSamples        = SafeDao.SafeGetShort(dataReader, 26);
                                _deviceObj.Height               = SafeDao.SafeGetDouble(dataReader, 27);
                                _deviceObj.Length               = SafeDao.SafeGetDouble(dataReader, 28);
                                _deviceObj.Width                = SafeDao.SafeGetDouble(dataReader, 29);
                                _deviceObj.BottomOutlet         = SafeDao.SafeGetDouble(dataReader, 30);
                                _deviceObj.CapacityOfAdjustment = SafeDao.SafeGetDouble(dataReader, 31);
                                _deviceObj.DeviceCount          = SafeDao.SafeGetInt(dataReader, 32);
                                _deviceObj.SensorOffset         = SafeDao.SafeGetDouble(dataReader, 33);
                                _deviceObj.UpdateDevice         = SafeDao.SafeGetByte(dataReader, 34);
                                _deviceObj.RegisterDevice       = SafeDao.SafeGetByte(dataReader, 35);
                                _deviceObj.MarkedForDeletion    = SafeDao.SafeGetByte(dataReader, 36);
                                _deviceObj.DeviceLinkTableID    = SafeDao.SafeGetInt(dataReader, 37);
                                _deviceObj.Identifier           = SafeDao.SafeGetString(dataReader, 38);
                                _deviceObj.AlertTypeH           = SafeDao.SafeGetInt(dataReader, 39);
                                _deviceObj.AlertTypeL           = SafeDao.SafeGetInt(dataReader, 40);
                                _deviceObj.AlertTypeD           = SafeDao.SafeGetInt(dataReader, 41);
                                _deviceObj.AlertTypeI           = SafeDao.SafeGetInt(dataReader, 42);
                                _deviceObj.AlertTypeN           = SafeDao.SafeGetInt(dataReader, 43);
                                _deviceObj.AlertDesc            = SafeDao.SafeGetString(dataReader, 44);
                                _deviceObj.UnitMeasurement      = SafeDao.SafeGetInt(dataReader, 45);
                                _deviceObj.DTEStatus            = SafeDao.SafeGetShort(dataReader, 46);
                                _deviceObj.DeviceApplication    = SafeDao.SafeGetShort(dataReader, 47);
                                _deviceObj.OverrideDDEM         = SafeDao.SafeGetByte(dataReader, 48);
                                _deviceObj.DwellingFloors       = SafeDao.SafeGetInt(dataReader, 49);
                                _deviceObj.DwellingUnits        = SafeDao.SafeGetInt(dataReader, 50);
                                _deviceObj.DwellingRooms        = SafeDao.SafeGetInt(dataReader, 51);
                                _deviceObj.DwellingArea         = SafeDao.SafeGetDouble(dataReader, 52);
                                _deviceObj.SubstanceID          = SafeDao.SafeGetInt(dataReader, 53);
                                _deviceObj.EquipmentID          = SafeDao.SafeGetInt(dataReader, 54);
                                _devices.Add(_deviceObj);
                            }
                        }
                        dataReader.Close();
                    }
                    connection.Close();
                }
                _log.Debug("-GetDevices() Devices from database: " + _numberOfRows + "rows of data!");
                //Set the composite object with our data..
                JsonDevice = new DeviceJson(_devices);
                JsonDevice.DatabaseName = remoteDbName;

                //Set Json object to max possible capasity
                jserializer = new JavaScriptSerializer();
                jserializer.MaxJsonLength = Int32.MaxValue;
            }
            catch (SqlException ex)
            {
                _log.Error("-GetDevices() Message: " + ex.Message + " InnerException: " + ex.InnerException);
            }
            finally
            {
                if (dataReader != null)
                {
                    dataReader.Close();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }
            _log.Debug("-GetDevices()");
            return(jserializer.Serialize(new { JsonDevice }).ToString());
        }