C# AdoDataConnection.ExecuteScalar方法代码示例

        /// <summary>
        /// Saves measurement back to the configuration database
        /// </summary>
        /// <param name="database">Database connection for query. Will be created from config if this value is null.</param>
        /// <param name="measurement">Measurement to be inserted or updated</param>
        public void Save(AdoDataConnection database, PowerMeasurement measurement)
        {
            var createdConnection = false;

			try
			{
				createdConnection = CreateConnection(ref database);

				if (measurement.SignalID == Guid.Empty)
				{
					database.ExecuteNonQuery("INSERT INTO Measurement (DeviceID, PointTag, SignalTypeID, " +
                        "SignalReference, Adder, Multiplier, Description, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES " + 
                        "({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11})", ToNotNull(measurement.DeviceID), measurement.PointTag, 
                        measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description), 
                        database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, Thread.CurrentPrincipal.Identity.Name, database.UtcNow);

                    measurement.SignalID = database.ExecuteScalar<Guid>("SELECT SignalID FROM Measurement WHERE PointTag={0}", measurement.PointTag);
				}
				else
				{
					database.ExecuteNonQuery("UPDATE Measurement SET DeviceID = {0}, PointTag = {1}, " +
                        "SignalTypeID = {2}, SignalReference = {3}, Adder = {4}, Multiplier = {5}, Description = {6}, " +
                        "Enabled = {7}, UpdatedBy = {8}, UpdatedOn = {9} WHERE SignalId = {10}", ToNotNull(measurement.DeviceID), measurement.PointTag,
						measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description), 
                        database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, measurement.SignalID);
				}
			}
			finally
			{
				if (createdConnection)
					database?.Dispose();
			}
		}

 /// <summary>
 /// Creates a new <see cref="DataSubscriptionHubClient"/> instance.
 /// </summary>
 public DataSubscriptionHubClient()
 {
     m_statisticSubscriptionInfo = new UnsynchronizedSubscriptionInfo(false);
     m_dataSubscriptionInfo = new UnsynchronizedSubscriptionInfo(false);
     m_measurements = new List<MeasurementValue>();
     m_statistics = new List<MeasurementValue>();
     m_statusLights = new List<StatusLight>();
     m_deviceDetails = new List<DeviceDetail>();
     m_measurementDetails = new List<MeasurementDetail>();
     m_phasorDetails = new List<PhasorDetail>();
     m_schemaVersion = new List<SchemaVersion>();
     m_measurementLock = new object();
     using(AdoDataConnection conn = new AdoDataConnection("securityProvider"))
     {
         int index = conn.ExecuteScalar<int>("Select ID FROM ValueListGroup WHERE Name = 'ModbusSubscriptions'");
         m_connectionString = conn.ExecuteScalar<string>("Select Text FROM ValueList WHERE GroupID = {0} AND IsDefault = 1", index);
     }
 }

    public void getContourAnimationFrame()
    {
        int animationID = Convert.ToInt32(HttpContext.Current.Request.QueryString["animation"]);
        int frameIndex = Convert.ToInt32(HttpContext.Current.Request.QueryString["frame"]);
        byte[] frameImage;

        using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
        {
            frameImage = connection.ExecuteScalar<byte[]>("SELECT FrameImage FROM ContourAnimationFrame WHERE ContourAnimationID = {0} AND FrameIndex = {1}", animationID, frameIndex);
        }

        HttpContext.Current.Response.ContentType = "image/png";
        HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("inline;filename=ContourFrame{0}x{1}.png", animationID, frameIndex));
        HttpContext.Current.Response.BinaryWrite(frameImage);
    }

        /// <summary>
        /// Saves <see cref="Phasor"/> information to database.
        /// </summary>
        /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
        /// <param name="phasor">Information about <see cref="Phasor"/>.</param>
        /// <param name="oldSourceIndex">The old source index of the phasor.</param>
        /// <param name="skipMeasurementUpdate">Skips associated measurement update if this is already being handled.</param>
        /// <returns>String, for display use, indicating success.</returns>
        public static string SaveAndReorder(AdoDataConnection database, Phasor phasor, int oldSourceIndex, bool skipMeasurementUpdate = false)
        {
            bool createdConnection = false;
            string query;

            try
            {
                createdConnection = CreateConnection(ref database);

                if (phasor.SourceIndex == 0)
                    phasor.SourceIndex = database.ExecuteScalar<int>("SELECT MAX(SourceIndex) FROM Phasor WHERE DeviceID = {0}", phasor.DeviceID) + 1;

                // Since phasors could be reordered in the source device, this test could inadvertently throw an exception when it should not - so the validation has been removed
                //if (database.ExecuteScalar<int>("SELECT COUNT(*) FROM Phasor WHERE ID <> {0} AND DeviceID = {1} AND SourceIndex = {2}", phasor.ID, phasor.DeviceID, phasor.SourceIndex) > 0)
                //    throw new InvalidOperationException("Phasor source index must be unique per device.");

                if (phasor.ID == 0)
                {
                    query = database.ParameterizedQueryString("INSERT INTO Phasor (DeviceID, Label, Type, Phase, SourceIndex, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
                        "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", "deviceID", "label", "type", "phase", "sourceIndex", "updatedBy", "updatedOn", "createdBy",
                        "createdOn");

                    database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type, phasor.Phase, phasor.SourceIndex,
                        CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
                }
                else
                {
                    query = database.ParameterizedQueryString("UPDATE Phasor SET DeviceID = {0}, Label = {1}, Type = {2}, Phase = {3}, SourceIndex = {4}, " +
                        "UpdatedBy = {5}, UpdatedOn = {6} WHERE ID = {7}", "deviceID", "label", "type", "phase", "sourceIndex", "updatedBy", "updatedOn", "id");

                    database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type,
                        phasor.Phase, phasor.SourceIndex, CommonFunctions.CurrentUser, database.UtcNow, phasor.ID);
                }

                // Get reference to the device to which phasor is being added.
                Device device = Device.GetDevice(database, "WHERE ID = " + phasor.DeviceID);

                // Get Phasor signal types.
                ObservableCollection<SignalType> signals;

                if (phasor.Type == "V")
                    signals = SignalType.GetVoltagePhasorSignalTypes();
                else
                    signals = SignalType.GetCurrentPhasorSignalTypes();

                // Get reference to phasor which has just been added.
                Phasor addedPhasor = GetPhasor(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SourceIndex = " + phasor.SourceIndex);

                foreach (SignalType signal in signals)
                {
                    Measurement measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SignalTypeSuffix = '" + signal.Suffix + "' AND PhasorSourceIndex = " + oldSourceIndex);

                    if ((object)measurement == null)
                    {
                        measurement = new Measurement();

                        measurement.DeviceID = device.ID;
                        measurement.HistorianID = device.HistorianID;
                        measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.SourceIndex, addedPhasor.Phase[0]);
                        measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex;
                        measurement.SignalTypeID = signal.ID;
                        measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name;
                        measurement.PhasorSourceIndex = addedPhasor.SourceIndex;
                        measurement.Enabled = true;

                        Measurement.Save(database, measurement);
                    }
                    else if (!skipMeasurementUpdate || addedPhasor.SourceIndex != oldSourceIndex) //  || measurement.SignalTypeID != signal.ID
                    {
                        // Update existing record when needed or when phasor source index has changed
                        measurement.HistorianID = device.HistorianID;
                        measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.SourceIndex, addedPhasor.Phase[0]);
                        measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex;
                        measurement.SignalTypeID = signal.ID;
                        measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name;
                        measurement.PhasorSourceIndex = addedPhasor.SourceIndex;

                        Measurement.Save(database, measurement);
                    }
                }

                return "Phasor information saved successfully";
            }
            finally
            {
                if (createdConnection && database != null)
                    database.Dispose();
            }
        }

        private static void ValidateAlarmStatistics(AdoDataConnection connection, Guid nodeID, string source)
        {
            const string MissingStatisticsFormat = "SELECT DISTINCT Severity FROM Alarm WHERE Severity <> 0 AND Severity NOT IN (SELECT Arguments FROM Statistic WHERE Source = {0} AND MethodName = {1})";
            const string MaxSignalIndexFormat = "SELECT COALESCE(MAX(SignalIndex), 0) FROM Statistic WHERE Source = {0}";
            const string InsertAlarmStatisticFormat = "INSERT INTO Statistic(Source, SignalIndex, Name, Description, AssemblyName, TypeName, MethodName, Arguments, Enabled, DataType, DisplayFormat, IsConnectedState, LoadOrder) VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12})";

            string methodName;

            DataTable missingStatistics;

            int signalIndex;
            int severity;
            string name;
            string description;

            // Add statistics for the alarms defined in the Alarm table.
            methodName = string.Format("Get{0}Statistic_MeasurementCountForSeverity", source);
            missingStatistics = connection.RetrieveData(MissingStatisticsFormat, source, methodName);

            if (missingStatistics.Rows.Count > 0)
            {
                signalIndex = connection.ExecuteScalar<int>(MaxSignalIndexFormat, source);

                foreach (DataRow missingStatistic in missingStatistics.Rows)
                {
                    signalIndex++;
                    severity = missingStatistic.ConvertField<int>("Severity");
                    name = string.Format("Alarm Severity {0}", severity);
                    description = string.Format("Number of measurements received while alarm with severity {0} was raised during the last reporting interval.", severity);

                    connection.ExecuteNonQuery(InsertAlarmStatisticFormat, source, signalIndex, name, description, "DataQualityMonitoring.dll", "DataQualityMonitoring.AlarmStatistics", methodName, severity, 1, "System.Int32", "{0:N0}", 0, 1001 - severity);
                }
            }
        }

        /// <summary>
        /// Data operation to validate and ensure that certain records
        /// that are required for alarming exist in the database.
        /// </summary>
        private static void ValidateAlarming(AdoDataConnection connection, string nodeIDQueryString)
        {
            // SELECT queries
            const string AlarmCountFormat = "SELECT COUNT(*) FROM Alarm";
            const string AlarmAdapterCountFormat = "SELECT COUNT(*) FROM CustomActionAdapter WHERE AdapterName = 'ALARM!SERVICES' AND NodeID = {0}";
            const string AlarmConfigEntityCountFormat = "SELECT COUNT(*) FROM ConfigurationEntity WHERE RuntimeName = 'Alarms'";
            const string AlarmSignalTypeCountFormat = "SELECT COUNT(*) FROM SignalType WHERE Name = 'Alarm'";

            // INSERT queries
            const string AlarmAdapterInsertFormat = "INSERT INTO CustomActionAdapter(NodeID, AdapterName, AssemblyName, TypeName, LoadOrder, Enabled) VALUES({0}, 'ALARM!SERVICES', 'DataQualityMonitoring.dll', 'DataQualityMonitoring.AlarmAdapter', 0, 1)";
            const string AlarmConfigEntityInsertFormat = "INSERT INTO ConfigurationEntity(SourceName, RuntimeName, Description, LoadOrder, Enabled) VALUES('Alarm', 'Alarms', 'Defines alarms that monitor the values of measurements', 17, 1)";
            const string AlarmSignalTypeInsertFormat = "INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, Source, EngineeringUnits) VALUES('Alarm', 'ALRM', 'AL', 'AL', 'Any', '')";

            bool alarmTableExists;

            Guid nodeID;
            int alarmAdapterCount;
            int alarmConfigEntityCount;
            int alarmSignalTypeCount;

            try
            {
                // Determine whether the alarm table exists
                // before inserting records related to alarming
                connection.Connection.ExecuteScalar(AlarmCountFormat);
                alarmTableExists = true;
            }
            catch
            {
                alarmTableExists = false;
            }

            if (alarmTableExists)
            {
                nodeID = Guid.Parse(nodeIDQueryString.Trim('\''));

                // Ensure that the alarm adapter is defined.
                alarmAdapterCount = connection.ExecuteScalar<int>(AlarmAdapterCountFormat, nodeID);

                if (alarmAdapterCount == 0)
                    connection.ExecuteNonQuery(AlarmAdapterInsertFormat, nodeID);

                // Ensure that the alarm record is defined in the ConfigurationEntity table.
                alarmConfigEntityCount = connection.ExecuteScalar<int>(AlarmConfigEntityCountFormat);

                if (alarmConfigEntityCount == 0)
                    connection.ExecuteNonQuery(AlarmConfigEntityInsertFormat);

                // Ensure that the alarm record is defined in the SignalType table.
                alarmSignalTypeCount = connection.ExecuteScalar<int>(AlarmSignalTypeCountFormat);

                if (alarmSignalTypeCount == 0)
                    connection.ExecuteNonQuery(AlarmSignalTypeInsertFormat);

                ValidateAlarmStatistics(connection, nodeID, "Point");
            }
        }

    private InverseDistanceWeightingFunction GetIDWFunction(ContourQuery contourQuery, List<TrendingDataLocation> locations = null)
    {
        CoordinateReferenceSystem crs = new EPSG3857();
        List<double> xList = new List<double>();
        List<double> yList = new List<double>();
        List<double> valueList = new List<double>();

        if ((object)locations == null)
            locations = GetFrameFromDailySummary(contourQuery);

        locations
            .Select(location =>
            {
                GeoCoordinate Coordinate = new GeoCoordinate(location.Latitude, location.Longitude);

                double? Value =
                    (contourQuery.DataType == "Average") ? location.Average :
                    (contourQuery.DataType == "Minimum") ? location.Minimum :
                    (contourQuery.DataType == "Maximum") ? location.Maximum :
                    null;

                return new { Coordinate, Value };
            })
            .Where(obj => (object)obj.Value != null)
            .ToList()
            .ForEach(obj =>
            {
                xList.Add(obj.Coordinate.Longitude);
                yList.Add(obj.Coordinate.Latitude);
                valueList.Add(obj.Value.GetValueOrDefault());
            });

        if (valueList.Count == 0)
        {
            xList.Add(0.0D);
            yList.Add(0.0D);

            using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
            {
                valueList.Add(connection.ExecuteScalar<double>("SELECT NominalValue FROM ContourColorScale WHERE Name = {0}", contourQuery.ColorScaleName));
            }
        }

        return new InverseDistanceWeightingFunction()
            .SetXCoordinates(xList.ToArray())
            .SetYCoordinates(yList.ToArray())
            .SetValues(valueList.ToArray())
            .SetDistanceFunction((x1, y1, x2, y2) =>
            {
                GeoCoordinate coordinate1 = new GeoCoordinate(y1, x1);
                GeoCoordinate coordinate2 = new GeoCoordinate(y2, x2);
                return crs.Distance(coordinate1, coordinate2);
            });
    }

        /// <summary>
        /// Saves <see cref="UserAccount"/> information to database.
        /// </summary>
        /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
        /// <param name="userAccount">Information about <see cref="UserAccount"/>.</param>        
        /// <returns>String, for display use, indicating success.</returns>
        public static string Save(AdoDataConnection database, UserAccount userAccount)
        {
            const string ErrorMessage = "User name already exists.";

            bool createdConnection = false;
            string query;
            string userAccountSID;
            int existing;

            try
            {
                createdConnection = CreateConnection(ref database);

                string pColumn = "Password";

                if (database.IsJetEngine)
                    pColumn = "[Password]";

                object changePasswordOn = userAccount.ChangePasswordOn;

                if (userAccount.ChangePasswordOn == DateTime.MinValue)
                    changePasswordOn = (object)DBNull.Value;
                else if (database.IsJetEngine)
                    changePasswordOn = userAccount.ChangePasswordOn.ToOADate();

                userAccountSID = UserInfo.UserNameToSID(userAccount.Name);

                if (!userAccount.UseADAuthentication || !UserInfo.IsUserSID(userAccountSID))
                    userAccountSID = userAccount.Name;

                if (userAccount.ID == Guid.Empty)
                {
                    existing = Convert.ToInt32(database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM UserAccount WHERE Name = {0}", "name"), DefaultTimeout, userAccountSID));

                    if (existing > 0)
                        throw new InvalidOperationException(ErrorMessage);

                    query = database.ParameterizedQueryString("INSERT INTO UserAccount (Name, " + pColumn + ", FirstName, LastName, DefaultNodeID, Phone, Email, " +
                        "LockedOut, UseADAuthentication, ChangePasswordOn, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, " +
                        "{9}, {10}, {11}, {12}, {13})", "name", "password", "firstName", "lastName", "defaultNodeID", "phone", "email", "lockedOut", "useADAuthentication",
                        "changePasswordOn", "updatedBy", "updatedOn", "createdBy", "createdOn");

                    database.Connection.ExecuteNonQuery(query, DefaultTimeout, userAccountSID,
                        userAccount.Password.ToNotNull(), userAccount.FirstName.ToNotNull(), userAccount.LastName.ToNotNull(), database.CurrentNodeID(),
                        userAccount.Phone.ToNotNull(), userAccount.Email.ToNotNull(), database.Bool(userAccount.LockedOut), database.Bool(userAccount.UseADAuthentication),
                        changePasswordOn, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);

                    CommonFunctions.LogEvent(string.Format("New user \"{0}\" created successfully by user \"{1}\".", userAccount.Name, CommonFunctions.CurrentUser), 2);
                }
                else
                {
                    existing = database.ExecuteScalar<int>("SELECT COUNT(*) FROM UserAccount WHERE Name = {0} AND ID <> {1}", userAccountSID, userAccount.ID);

                    if (existing > 0)
                        throw new InvalidOperationException(ErrorMessage);

                    query = database.ParameterizedQueryString("UPDATE UserAccount SET Name = {0}, " + pColumn + " = {1}, FirstName = {2}, LastName = {3}, " +
                            "DefaultNodeID = {4}, Phone = {5}, Email = {6}, LockedOut = {7}, UseADAuthentication = {8}, ChangePasswordOn = {9}, UpdatedBy = {10}, " +
                            "UpdatedOn = {11} WHERE ID = {12}", "name", "password", "firstName", "lastName", "defaultNodeID", "phone", "email", "lockedOut",
                            "useADAuthentication", "changePasswordOn", "updatedBy", "updatedOn", "id");

                    database.Connection.ExecuteNonQuery(query, DefaultTimeout, userAccountSID,
                            userAccount.Password.ToNotNull(), userAccount.FirstName.ToNotNull(), userAccount.LastName.ToNotNull(), database.Guid(userAccount.DefaultNodeID),
                            userAccount.Phone.ToNotNull(), userAccount.Email.ToNotNull(), database.Bool(userAccount.LockedOut), database.Bool(userAccount.UseADAuthentication),
                            changePasswordOn, CommonFunctions.CurrentUser, database.UtcNow, database.Guid(userAccount.ID));

                    CommonFunctions.LogEvent(string.Format("Information about user \"{0}\" updated successfully by user \"{1}\".", userAccount.Name, CommonFunctions.CurrentUser), 3);
                }

                return "User account information saved successfully";
            }
            finally
            {
                if (createdConnection && database != null)
                    database.Dispose();
            }
        }

 /// <summary>
 /// Returns true if a data operation exists to run this class. Returns false otherwise.
 /// </summary>
 /// <param name="database">Database connection to use for checking the data operation</param>
 /// <returns>True or false indicating whether the operation exists</returns>
 private static bool DataOperationExists(AdoDataConnection database)
 {
     return Convert.ToInt32(database.ExecuteScalar($"SELECT COUNT(*) FROM DataOperation WHERE TypeName='{typeof(PowerCalculationConfigurationValidation).FullName}' AND MethodName='ValidatePowerCalculationConfigurations'")) > 0;
 }

 private static int GetEmailCount(DbAdapterContainer dbAdapterContainer, int eventID)
 {
     using (AdoDataConnection connection = new AdoDataConnection(dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
     {
         int timeout = dbAdapterContainer.CommandTimeout;
         string sql = "SELECT COUNT(*) FROM EventSentEmail WHERE EventID = {0}";
         object[] parameters = { eventID };
         return connection.ExecuteScalar<int>(timeout: timeout, sqlFormat: sql, parameters: parameters);
     }
 }

        private static void MeasurementDeviceAssociation(AdoDataConnection connection, string nodeIDQueryString, ulong trackingVersion, string arguments, Action<string> statusMessage, Action<Exception> processException)
        {
            if (string.IsNullOrEmpty(arguments))
            {
                statusMessage("WARNING: No arguments supplied to MeasurementDeviceAssociation data operation - no action will be performed. Expecting \"deviceAcronym\" and \"lookupExpression\" settings at a minimum.");
                return;
            }

            Dictionary<string, string> args = arguments.ParseKeyValuePairs();

            string deviceAcronym;

            if (!args.TryGetValue("DeviceAcronym", out deviceAcronym))
            {
                statusMessage("WARNING: No \"deviceAcronyym\" argument supplied to MeasurementDeviceAssociation data operation - no action will be performed. Expecting \"deviceAcronym\" and \"lookupExpression\" settings at a minimum.");
                return;
            }

            string lookupExpression;

            if (!args.TryGetValue("LookupExpression", out lookupExpression))
            {
                statusMessage("WARNING: No \"lookupExpression\" argument supplied to MeasurementDeviceAssociation data operation - no action will be performed. Expecting \"deviceAcronym\" and \"lookupExpression\" settings at a minimum.");
                return;
            }

            // Make sure device acronym exists
            if (connection.ExecuteScalar<int>($"SELECT COUNT(*) FROM Device WHERE NodeID={nodeIDQueryString} AND Acronym={{0}}", deviceAcronym) == 0)
            {
                // Lookup virtual device protocol
                if (connection.ExecuteScalar<int>("SELECT COUNT(*) FROM Protocol WHERE Acronym='VirtualInput'") == 0)
                {
                    statusMessage("WARNING: No VirutalInput device protocol was found in source database configuration for MeasurementDeviceAssociation data operation - no action will be performed.");
                    return;
                }

                statusMessage($"Creating new \"{deviceAcronym}\" virtual device...");

                int virtualProtocolID = connection.ExecuteScalar<int>("SELECT ID FROM Protocol WHERE Acronym='VirtualInput'");

                // Create new virtual device record
                connection.ExecuteNonQuery($"INSERT INTO Device(NodeID, Acronym, Name, ProtocolID, Enabled) VALUES({nodeIDQueryString}, {{0}}, {{1}}, {{2}}, 1)", deviceAcronym, deviceAcronym, virtualProtocolID);
            }

            statusMessage($"Validating \"{deviceAcronym}\" virtual device measurement associations...");

            // Get device ID
            int deviceID = connection.ExecuteScalar<int>($"SELECT ID FROM Device WHERE NodeID={nodeIDQueryString} AND Acronym={{0}}", deviceAcronym);

            // Get measurements that should be associated with device ID but are not currently
            IEnumerable<DataRow> measurements = connection.RetrieveData($"SELECT PointID FROM Measurement WHERE ({lookupExpression}) AND (DeviceID IS NULL OR DeviceID <> {{0}})", deviceID).AsEnumerable();

            int associatedMeasurements = 0;

            foreach (DataRow row in measurements)
            {
                connection.ExecuteNonQuery("UPDATE Measurement SET DeviceID={0} WHERE PointID={1}", deviceID, row.Field<int>("PointID"));
                associatedMeasurements++;
            }

            if (associatedMeasurements > 0)
                statusMessage($"Associated \"{associatedMeasurements}\" measurements to \"{deviceAcronym}\" virtual device...");
        }

        private static int LoadSentEmail(List<string> recipients, string subject, string body)
        {
            DateTime now = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, s_timeZone);
            string toLine = string.Join("; ", recipients.Select(recipient => recipient.Trim()));

            using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
            {
                connection.ExecuteNonQuery("INSERT INTO SentEmail VALUES({0}, {1}, {2}, {3})", now, toLine, subject, body);
                return connection.ExecuteScalar<int>("SELECT @@IDENTITY");
            }
        }

        private static void GenerateEmail(int eventID)
        {
            SystemInfoDataContext systemInfo;
            MeterInfoDataContext meterInfo;
            FaultLocationInfoDataContext faultInfo;
            EventTableAdapter eventAdapter;
            EventTypeTableAdapter eventTypeAdapter;

            EventRow eventRow;
            EventDataTable systemEvent;

            int faultTypeID;
            string eventDetail;
            XDocument htmlDocument;

            List<Attachment> attachments;
            string subject;
            string html;
            bool alreadySent;

            systemInfo = s_dbAdapterContainer.GetAdapter<SystemInfoDataContext>();
            meterInfo = s_dbAdapterContainer.GetAdapter<MeterInfoDataContext>();
            faultInfo = s_dbAdapterContainer.GetAdapter<FaultLocationInfoDataContext>();
            eventAdapter = s_dbAdapterContainer.GetAdapter<EventTableAdapter>();
            eventTypeAdapter = s_dbAdapterContainer.GetAdapter<EventTypeTableAdapter>();

            faultTypeID = eventTypeAdapter.GetData()
                .Where(eventType => eventType.Name == "Fault")
                .Select(eventType => eventType.ID)
                .FirstOrDefault();

            // Load the system event before the eventDetail record to avoid race conditions causing missed emails
            eventRow = eventAdapter.GetDataByID(eventID)[0];
            systemEvent = eventAdapter.GetSystemEvent(eventRow.StartTime, eventRow.EndTime, s_timeTolerance);
            eventDetail = eventAdapter.GetEventDetail(eventID);

            List<IGrouping<int, Guid>> templateGroups;

            using (SqlCommand command = new SqlCommand("GetEventEmailRecipients", s_dbAdapterContainer.Connection))
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                DataTable recipientTable = new DataTable();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@eventID", eventID);
                adapter.Fill(recipientTable);

                templateGroups = recipientTable
                    .Select()
                    .GroupBy(row => row.ConvertField<int>("TemplateID"), row => row.ConvertField<Guid>("UserAccountID"))
                    .ToList();
            }

            foreach (IGrouping<int, Guid> templateGroup in templateGroups)
            {
                string template;
                List<string> recipients;

                using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
                {
                    template = connection.ExecuteScalar<string>("SELECT Template FROM XSLTemplate WHERE ID = {0}", templateGroup.Key);

                    string paramString = string.Join(",", templateGroup.Select((userAccountID, index) => $"{{{index}}}"));
                    string sql = $"SELECT Email FROM UserAccount WHERE Email IS NOT NULL AND Email <> '' AND ID IN ({paramString})";
                    DataTable emailTable = connection.RetrieveData(sql, templateGroup.Cast<object>().ToArray());
                    recipients = emailTable.Select().Select(row => row.ConvertField<string>("Email")).ToList();
                }

                htmlDocument = XDocument.Parse(eventDetail.ApplyXSLTransform(template), LoadOptions.PreserveWhitespace);
                htmlDocument.TransformAll("format", element => element.Format());
                attachments = new List<Attachment>();

                try
                {
                    htmlDocument.TransformAll("chart", (element, index) =>
                    {
                        string cid = $"chart{index:00}.png";

                        Stream image = ChartGenerator.ConvertToChartImageStream(s_dbAdapterContainer, element);
                        Attachment attachment = new Attachment(image, cid);
                        attachment.ContentId = attachment.Name;
                        attachments.Add(attachment);

                        return new XElement("img", new XAttribute("src", $"cid:{cid}"));
                    });

                    subject = (string)htmlDocument.Descendants("title").FirstOrDefault() ?? "Fault detected by openXDA";
                    html = htmlDocument.ToString(SaveOptions.DisableFormatting).Replace("&amp;", "&");
                    alreadySent = false;

                    try
                    {
                        int sentEmailID;

                        using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
                        {
                            string systemEventIDs = string.Join(",", systemEvent.Where(row => row.LineID == eventRow.LineID).Select(row => row.ID));

                            string query =
                                $"SELECT SentEmail.ID " +
                                $"FROM " +
//.........这里部分代码省略.........

    private List<List<TrendingDataLocation>> GetFramesFromHistorian(ContourQuery contourQuery)
    {
        DataTable idTable;
        string historianServer;
        string historianInstance;

        using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
        {
            string query =
                "SELECT " +
                "    Channel.ID AS ChannelID, " +
                "    Meter.ID AS MeterID, " +
                "    Meter.Name AS MeterName, " +
                "    MeterLocation.Latitude, " +
                "    MeterLocation.Longitude, " +
                "    Channel.PerUnitValue " +
                "FROM " +
                "    Meter JOIN " +
                "    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN " +
                "    Channel ON " +
                "        Channel.MeterID = Meter.ID AND " +
                "        Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourColorScaleName = {1}) " +
                "WHERE " +
                "    Meter.ID IN (SELECT * FROM authMeters({0}))";

            idTable = connection.RetrieveData(query, contourQuery.UserName, contourQuery.ColorScaleName);
            historianServer = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
            historianInstance = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA";
        }

        List<DataRow> meterRows = idTable
            .Select()
            .DistinctBy(row => row.ConvertField<int>("MeterID"))
            .ToList();

        DateTime startDate = contourQuery.GetStartDate();
        DateTime endDate = contourQuery.GetEndDate();
        int stepSize = contourQuery.StepSize;

        // The frames to be included are those whose timestamps fall
        // within the range which is specified by startDate and
        // endDate. We start by aligning startDate and endDate with
        // the nearest frame timestamps which fall within that range
        int startTimeOffset = (int)Math.Ceiling((startDate - startDate.Date).TotalMinutes / stepSize);
        startDate = startDate.Date.AddMinutes(startTimeOffset * stepSize);

        int endTimeOffset = (int)Math.Floor((endDate - endDate.Date).TotalMinutes / stepSize);
        endDate = endDate.Date.AddMinutes(endTimeOffset * stepSize);

        // Since each frame includes data from all timestamps between
        // the previous frame's timestamp and its own timestamp, we
        // must include one additional frame of data before startDate
        startDate = startDate.AddMinutes(-stepSize);

        int frameCount = (int)((endDate - startDate).TotalMinutes / stepSize);

        List<Dictionary<int, TrendingDataLocation>> frames = Enumerable.Repeat(meterRows, frameCount)
            .Select(rows => rows.Select(row => new TrendingDataLocation()
            {
                id = row.ConvertField<int>("MeterID"),
                name = row.ConvertField<string>("MeterName"),
                Latitude = row.ConvertField<double>("Latitude"),
                Longitude = row.ConvertField<double>("Longitude")
            }))
            .Select(locations => locations.ToDictionary(location => location.id))
            .ToList();

        Dictionary<int, double?> nominalLookup = idTable
            .Select("ChannelID IS NOT NULL")
            .ToDictionary(row => row.ConvertField<int>("ChannelID"), row => row.ConvertField<double?>("PerUnitValue"));

        Dictionary<int, List<TrendingDataLocation>> lookup = idTable
            .Select("ChannelID IS NOT NULL")
            .Select(row =>
            {
                int meterID = row.ConvertField<int>("MeterID");

                return new
                {
                    ChannelID = row.ConvertField<int>("ChannelID"),
                    Frames = frames.Select(locationLookup => locationLookup[meterID]).ToList()
                };
            })
            .ToDictionary(obj => obj.ChannelID, obj => obj.Frames);

        using (Historian historian = new Historian(historianServer, historianInstance))
        {
            foreach (TrendingDataPoint point in historian.Read(lookup.Keys, startDate, endDate))
            {
                List<TrendingDataLocation> locations = lookup[point.ChannelID];

                // Use ceiling to sort data into the next nearest frame.
                // Subtract 1 because startDate was shifted to include one additional frame of data
                int frameIndex = (int)Math.Ceiling((point.Timestamp - startDate).TotalMinutes / stepSize) - 1;

                if (frameIndex < 0 || frameIndex >= locations.Count)
                    continue;

                TrendingDataLocation frame = locations[frameIndex];

//.........这里部分代码省略.........

    public ContourAnimationInfo getContourAnimations(ContourQuery contourQuery)
    {
        List<List<TrendingDataLocation>> frames = GetFramesFromHistorian(contourQuery);
        PiecewiseLinearFunction colorScale = GetColorScale(contourQuery);
        Func<double, double> colorFunc = colorScale;

        // The actual startDate is the timestamp of the
        // first frame after contourQuery.GetStartDate()
        DateTime startDate = contourQuery.GetStartDate();
        int stepSize = contourQuery.StepSize;
        int startTimeOffset = (int)Math.Ceiling((startDate - startDate.Date).TotalMinutes / stepSize);
        startDate = startDate.Date.AddMinutes(startTimeOffset * stepSize);

        double minLat = frames.Min(frame => frame.Min(location => location.Latitude)) - GetLatFromMiles(50.0D);
        double maxLat = frames.Min(frame => frame.Max(location => location.Latitude)) + GetLatFromMiles(50.0D);
        double minLng = frames.Min(frame => frame.Min(location => location.Longitude)) - GetLngFromMiles(50.0D, 0.0D);
        double maxLng = frames.Min(frame => frame.Max(location => location.Longitude)) + GetLngFromMiles(50.0D, 0.0D);

        GeoCoordinate topLeft = new GeoCoordinate(maxLat, minLng);
        GeoCoordinate bottomRight = new GeoCoordinate(minLat, maxLng);
        GSF.Drawing.Point topLeftPoint = s_crs.Translate(topLeft, contourQuery.Resolution);
        GSF.Drawing.Point bottomRightPoint = s_crs.Translate(bottomRight, contourQuery.Resolution);

        topLeftPoint = new GSF.Drawing.Point(Math.Floor(topLeftPoint.X), Math.Floor(topLeftPoint.Y));
        bottomRightPoint = new GSF.Drawing.Point(Math.Ceiling(bottomRightPoint.X), Math.Ceiling(bottomRightPoint.Y));
        topLeft = s_crs.Translate(topLeftPoint, contourQuery.Resolution);
        bottomRight = s_crs.Translate(bottomRightPoint, contourQuery.Resolution);

        int width = (int)(bottomRightPoint.X - topLeftPoint.X + 1);
        int height = (int)(bottomRightPoint.Y - topLeftPoint.Y + 1);

        int animationID;
        string timeZoneID = null;

        using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
        {
            connection.ExecuteNonQuery("INSERT INTO ContourAnimation(ColorScaleName, StartTime, EndTime, StepSize) VALUES({0}, {1}, {2}, {3})", contourQuery.ColorScaleName, contourQuery.GetStartDate(), contourQuery.GetEndDate(), contourQuery.StepSize);
            animationID = connection.ExecuteScalar<int>("SELECT @@IDENTITY");

            if (contourQuery.IncludeWeather)
                timeZoneID = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'XDATimeZone'");
        }

        GSF.Threading.CancellationToken cancellationToken = new GSF.Threading.CancellationToken();
        s_cancellationTokens[animationID] = cancellationToken;

        ProgressCounter progressCounter = new ProgressCounter(frames.Count);
        s_progressCounters[animationID] = progressCounter;

        Action<int> createFrame = i =>
        {
            List<TrendingDataLocation> frame = frames[i];
            IDWFunc idwFunction = GetIDWFunction(contourQuery, frame);
            uint[] pixelData;

            if (contourQuery.IncludeWeather)
            {
                TimeZoneInfo tzInfo = !string.IsNullOrEmpty(timeZoneID)
                    ? TimeZoneInfo.FindSystemTimeZoneById(timeZoneID)
                    : TimeZoneInfo.Local;

                // Weather data is only available in 5-minute increments
                DateTime frameTime = TimeZoneInfo.ConvertTimeToUtc(startDate.AddMinutes(stepSize * i), tzInfo);
                double minutes = (frameTime - frameTime.Date).TotalMinutes;
                int weatherMinutes = (int)Math.Ceiling(minutes / 5) * 5;

                NameValueCollection queryString = HttpUtility.ParseQueryString(string.Empty);
                queryString["service"] = "WMS";
                queryString["request"] = "GetMap";
                queryString["layers"] = "nexrad-n0r-wmst";
                queryString["format"] = "image/png";
                queryString["transparent"] = "true";
                queryString["version"] = "1.1.1";
                queryString["time"] = frameTime.Date.AddMinutes(weatherMinutes).ToString("o");
                queryString["height"] = height.ToString();
                queryString["width"] = width.ToString();
                queryString["srs"] = "EPSG:3857";

                GSF.Drawing.Point topLeftProjected = s_crs.Projection.Project(topLeft);
                GSF.Drawing.Point bottomRightProjected = s_crs.Projection.Project(bottomRight);
                queryString["bbox"] = string.Join(",", topLeftProjected.X, bottomRightProjected.Y, bottomRightProjected.X, topLeftProjected.Y);

                string weatherURL = "http://mesonet.agron.iastate.edu/cgi-bin/wms/nexrad/n0r-t.cgi?" + queryString.ToString();

                using (WebClient client = new WebClient())
                using (MemoryStream stream = new MemoryStream(client.DownloadData(weatherURL)))
                using (Bitmap bitmap = new Bitmap(stream))
                {
                    pixelData = bitmap.ToPixelData();
                }
            }
            else
            {
                pixelData = new uint[width * height];
            }

            if (cancellationToken.IsCancelled)
                return;

            for (int x = 0; x < width; x++)
//.........这里部分代码省略.........