C# AdoDataConnection.RetrieveData方法代码示例

    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";
        }

        if (!string.IsNullOrEmpty(contourQuery.Meters))
        {
            const int byteSize = 8;

            // Meter selections are stored as a base-64 string without padding, using '-' instead of '+' and '_' instead of '/'
            string padding = "A==".Remove(3 - (contourQuery.Meters.Length + 3) % 4);
            string base64 = contourQuery.Meters.Replace('-', '+').Replace('_', '/') + padding;
            byte[] meterSelections = Convert.FromBase64String(base64);

            // The resulting byte array is a simple set of bitflags ordered by meter ID and packed into the most significant bits.
            // In order to properly interpret the bytes, we must first group and order the data by meter ID to determine the location
            // of each meter's bitflag. Then we can filter out the unwanted data from the original table of IDs
            idTable.Select()
                .Select((Row, Index) => new { Row, Index })
                .GroupBy(obj => obj.Row.ConvertField<int>("MeterID"))
                .OrderBy(grouping => grouping.Key)
                .Where((grouping, index) => (meterSelections[index / byteSize] & (0x80 >> (index % byteSize))) == 0)
                .SelectMany(grouping => grouping)
                .OrderByDescending(obj => obj.Index)
                .ToList()
                .ForEach(obj => idTable.Rows.RemoveAt(obj.Index));
        }

        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
//.........这里部分代码省略.........

    public TrendingDataSet getTrendsforChannelIDDate(string ChannelID, string targetDate)
    {
        //DateTime epoch = new DateTime(1970, 1, 1);
        //string theSproc = "dbo.selectTrendingDataByChannelIDDate2";
        //DataSet dataSet = new DataSet();
        //TrendingDataSet trendingDataSet = new TrendingDataSet();

        //using (SqlConnection connection = new SqlConnection(connectionstring))
        //using (SqlCommand command = connection.CreateCommand())
        //using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        //{
        //    command.CommandText = theSproc;
        //    command.CommandType = CommandType.StoredProcedure;
        //    command.Parameters.AddWithValue("@EventDate", targetDate);
        //    command.Parameters.AddWithValue("@ChannelID", ChannelID);
        //    command.CommandTimeout = 300;

        //    connection.Open();
        //    adapter.Fill(dataSet);

        //    trendingDataSet.ChannelData = dataSet.Tables[0].Rows
        //        .Cast<DataRow>()
        //        .Select(row => new TrendingDataPoint()
        //        {
        //            Time = row.Field<DateTime>("thedate").Subtract(epoch).TotalMilliseconds,
        //            Maximum = row.Field<double>("themaximum"),
        //            Minimum = row.Field<double>("theminimum"),
        //            Average = row.Field<double>("theaverage")
        //        })
        //        .ToArray();

        //    trendingDataSet.AlarmLimits = dataSet.Tables[1].Rows
        //        .Cast<DataRow>()
        //        .Select(row => new TrendingAlarmLimit()
        //        {
        //            TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds,
        //            TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds,
        //            High = row.Field<double?>("alarmlimithigh"),
        //            Low = row.Field<double?>("alarmlimitlow")
        //        })
        //        .ToArray();

        //    trendingDataSet.OffNormalLimits = dataSet.Tables[2].Rows
        //        .Cast<DataRow>()
        //        .Select(row => new TrendingAlarmLimit()
        //        {
        //            TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds,
        //            TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds,
        //            High = row.Field<double?>("offlimithigh"),
        //            Low = row.Field<double?>("offlimitlow")
        //        })
        //        .ToArray();
        //}
        string historianServer;
        string historianInstance;
        IEnumerable<int> channelIDs = new List<int>() { Convert.ToInt32(ChannelID) };
        DateTime startDate = Convert.ToDateTime(targetDate);
        DateTime endDate = startDate.AddDays(1);
        TrendingDataSet trendingDataSet = new TrendingDataSet();
        DateTime epoch = new DateTime(1970, 1, 1);

        using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
        {
            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";

            using (Historian historian = new Historian(historianServer, historianInstance))
            {
                foreach (openHistorian.XDALink.TrendingDataPoint point in historian.Read(channelIDs, startDate, endDate))
                {
                    if (!trendingDataSet.ChannelData.Exists(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds))
                    {
                        trendingDataSet.ChannelData.Add(new TrendingDataDatum());
                        trendingDataSet.ChannelData[trendingDataSet.ChannelData.Count - 1].Time = point.Timestamp.Subtract(epoch).TotalMilliseconds;
                    }

                    if (point.SeriesID.ToString() == "Average")
                        trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Average = point.Value;
                    else if (point.SeriesID.ToString() == "Minimum")
                        trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Minimum = point.Value;
                    else if (point.SeriesID.ToString() == "Maximum")
                        trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Maximum = point.Value;

                }
            }
            IEnumerable<DataRow> table = Enumerable.Empty<DataRow>();

            table = connection.RetrieveData(" Select {0} AS thedatefrom, " +
                                                        "        DATEADD(DAY, 1, {0}) AS thedateto, " +
                                                        "        CASE WHEN AlarmRangeLimit.PerUnit <> 0 AND Channel.PerUnitValue IS NOT NULL THEN AlarmRangeLimit.High * PerUnitValue ELSE AlarmRangeLimit.High END AS alarmlimithigh," +
                                                        "        CASE WHEN AlarmRangeLimit.PerUnit <> 0 AND Channel.PerUnitValue IS NOT NULL THEN AlarmRangeLimit.Low * PerUnitValue ELSE AlarmRangeLimit.Low END AS alarmlimitlow " +
                                                        " FROM   AlarmRangeLimit JOIN " +
                                                        "        Channel ON AlarmRangeLimit.ChannelID = Channel.ID " +
                                                        "WHERE   AlarmRangeLimit.AlarmTypeID = (SELECT ID FROM AlarmType where Name = 'Alarm') AND " +
                                                        "        AlarmRangeLimit.ChannelID = {1}", startDate, Convert.ToInt32(ChannelID)).Select();

            foreach (DataRow row in table)
            {
                trendingDataSet.AlarmLimits.Add(new TrendingAlarmLimit() { High = row.Field<double?>("alarmlimithigh"), Low = row.Field<double?>("alarmlimitlow"), TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds, TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds });
            }
//.........这里部分代码省略.........

    public ContourInfo getLocationsTrendingData(ContourQuery contourQuery)
    {
        List<TrendingDataLocation> locations = new List<TrendingDataLocation>();
        DataTable colorScale;

        using (AdoDataConnection conn = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
        using (IDbCommand cmd = conn.Connection.CreateCommand())
        {
            cmd.Parameters.Add(new SqlParameter("@EventDateFrom", contourQuery.GetStartDate()));
            cmd.Parameters.Add(new SqlParameter("@EventDateTo", contourQuery.GetEndDate()));
            cmd.Parameters.Add(new SqlParameter("@colorScaleName", contourQuery.ColorScaleName));
            cmd.Parameters.Add(new SqlParameter("@username", contourQuery.UserName));
            cmd.CommandText = "dbo.selectMeterLocationsTrendingData";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 300;

            using (IDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    TrendingDataLocation ourStatus = new TrendingDataLocation();
                    ourStatus.Latitude = (double)rdr["Latitude"];
                    ourStatus.Longitude = (double)rdr["Longitude"];
                    ourStatus.name = (string)rdr["Name"];
                    ourStatus.Average = (rdr.IsDBNull(rdr.GetOrdinal("Average")) ? (double?)null : (double)rdr["Average"]);
                    ourStatus.Maximum = (rdr.IsDBNull(rdr.GetOrdinal("Maximum")) ? (double?)null : (double)rdr["Maximum"]);
                    ourStatus.Minimum = (rdr.IsDBNull(rdr.GetOrdinal("Minimum")) ? (double?)null : (double)rdr["Minimum"]);
                    ourStatus.id = (int)rdr["id"];
                    ourStatus.data.Add(ourStatus.Average);
                    ourStatus.data.Add(ourStatus.Maximum);
                    ourStatus.data.Add(ourStatus.Minimum);
                    locations.Add(ourStatus);
                }
            }

            string query =
                "SELECT " +
                "    ContourColorScalePoint.Value, " +
                "    ContourColorScalePoint.Color " +
                "FROM " +
                "    ContourColorScale JOIN " +
                "    ContourColorScalePoint ON ContourColorScalePoint.ContourColorScaleID = ContourColorScale.ID " +
                "WHERE ContourColorScale.Name = {0} " +
                "ORDER BY ContourColorScalePoint.OrderID";

            colorScale = conn.RetrieveData(query, contourQuery.ColorScaleName);
        }

        double[] colorDomain = colorScale
            .Select()
            .Select(row => row.ConvertField<double>("Value"))
            .ToArray();

        double[] colorRange = colorScale
            .Select()
            .Select(row => (double)(uint)row.ConvertField<int>("Color"))
            .ToArray();

        return new ContourInfo()
        {
            Locations = locations,
            ColorDomain = colorDomain,
            ColorRange = colorRange,
        };
    }

    private PiecewiseLinearFunction GetColorScale(ContourQuery contourQuery)
    {
        DataTable colorScale;

        using (AdoDataConnection conn = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
        {
            string query =
                "SELECT " +
                "    ContourColorScalePoint.Value, " +
                "    ContourColorScalePoint.Color " +
                "FROM " +
                "    ContourColorScale JOIN " +
                "    ContourColorScalePoint ON ContourColorScalePoint.ContourColorScaleID = ContourColorScale.ID " +
                "WHERE ContourColorScale.Name = {0} " +
                "ORDER BY ContourColorScalePoint.OrderID";

            colorScale = conn.RetrieveData(query, contourQuery.ColorScaleName);
        }

        double[] colorDomain = colorScale
            .Select()
            .Select(row => row.ConvertField<double>("Value"))
            .ToArray();

        double[] colorRange = colorScale
            .Select()
            .Select(row => (double)(uint)row.ConvertField<int>("Color"))
            .ToArray();

        return new PiecewiseLinearFunction()
            .SetDomain(colorDomain)
            .SetRange(colorRange);
    }

        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 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);
                }
            }
        }

        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 static void LoadEventSentEmail(EventRow eventRow, EventDataTable systemEvent, int sentEmailID)
        {
            BulkLoader bulkLoader;
            DataTable eventSentEmailTable;

            using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
            {
                // Query an empty table with matching schema --
                // union table to itself to eliminate unique key constraints
                eventSentEmailTable = connection.RetrieveData("SELECT * FROM EventSentEmail WHERE 1 IS NULL UNION ALL SELECT * FROM EventSentEmail WHERE 1 IS NULL");
                eventSentEmailTable.TableName = "EventSentEmail";
            }

            foreach (MeterData.EventRow evt in systemEvent)
            {
                if (eventRow.LineID == evt.LineID)
                    eventSentEmailTable.Rows.Add(0, evt.ID, sentEmailID);
            }

            bulkLoader = new BulkLoader();
            bulkLoader.Connection = s_dbAdapterContainer.Connection;
            bulkLoader.CommandTimeout = s_dbAdapterContainer.CommandTimeout;
            bulkLoader.Load(eventSentEmailTable);
        }

    private static List<string> GetFaultCurveInfo(AdoDataConnection connection, int eventID)
    {
        const string query =
            "SELECT Algorithm " +
            "FROM FaultCurve LEFT OUTER JOIN FaultLocationAlgorithm ON Algorithm = MethodName " +
            "WHERE EventID = {0} " +
            "ORDER BY CASE WHEN ExecutionOrder IS NULL THEN 1 ELSE 0 END, ExecutionOrder";

        DataTable table = connection.RetrieveData(query, eventID);

        return table.Rows
            .Cast<DataRow>()
            .Select(row => row.Field<string>("Algorithm"))
            .ToList();
    }

        /// <summary>
        /// Retrieves <see cref="MeasurementGroup"/> information for the group with the given ID.
        /// </summary>
        /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
        /// <param name="groupID">The ID of the measurement group to be retrieved.</param>
        /// <returns>Measurement group with the given ID.</returns>
        public static MeasurementGroup GetMeasurementGroup(AdoDataConnection database, int groupID)
        {
            DataTable measurementGroupTable;
            bool createdConnection = false;
            DataRow row;

            try
            {
                createdConnection = CreateConnection(ref database);
                measurementGroupTable = database.RetrieveData(DefaultTimeout, "SELECT * FROM MeasurementGroup WHERE ID = {0}", groupID);

                if (measurementGroupTable.Rows.Count == 0)
                    return null;

                row = measurementGroupTable.Rows[0];

                MeasurementGroup measurementGroup = new MeasurementGroup()
                {
                    NodeID = database.Guid(row, "NodeID"),
                    ID = row.ConvertField<int>("ID"),
                    Name = row.Field<string>("Name"),
                    Description = row.Field<object>("Description").ToNonNullString(),
                    FilterExpression = row.Field<object>("FilterExpression").ToNonNullString(),
                };

                return measurementGroup;
            }
            finally
            {
                if (createdConnection && database != null)
                    database.Dispose();
            }
        }

    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];

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

        /// <summary>
        /// Retrieves a <see cref="Measurement"/> information from the database based on the signal ID of the measurement.
        /// </summary>
        /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
        /// <param name="signalID">Signal ID of the measurement.</param>
        /// <returns><see cref="Measurement"/> information.</returns>
        public static Measurement GetMeasurement(AdoDataConnection database, Guid signalID)
        {
            bool createdConnection = false;
            DataTable measurementTable;
            DataRow row;

            try
            {
                createdConnection = CreateConnection(ref database);
                measurementTable = database.RetrieveData(DefaultTimeout, "SELECT * FROM MeasurementDetail WHERE SignalID = {0}", signalID);

                if (measurementTable.Rows.Count == 0)
                    return null;

                row = measurementTable.Rows[0];

                Measurement measurement = new Measurement()
                {
                    SignalID = database.Guid(row, "SignalID"),
                    HistorianID = row.ConvertNullableField<int>("HistorianID"),
                    PointID = row.ConvertField<int>("PointID"),
                    DeviceID = row.ConvertNullableField<int>("DeviceID"),
                    PointTag = row.Field<string>("PointTag"),
                    AlternateTag = row.Field<string>("AlternateTag"),
                    SignalTypeID = row.ConvertField<int>("SignalTypeID"),
                    PhasorSourceIndex = row.ConvertNullableField<int>("PhasorSourceIndex"),
                    SignalReference = row.Field<string>("SignalReference"),
                    Adder = row.ConvertField<double>("Adder"),
                    Multiplier = row.ConvertField<double>("Multiplier"),
                    Description = row.Field<string>("Description"),
                    Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
                    m_historianAcronym = row.Field<string>("HistorianAcronym"),
                    m_deviceAcronym = row.Field<object>("DeviceAcronym") == null ? string.Empty : row.Field<string>("DeviceAcronym"),
                    m_signalName = row.Field<string>("SignalName"),
                    m_signalAcronym = row.Field<string>("SignalAcronym"),
                    m_signalSuffix = row.Field<string>("SignalTypeSuffix"),
                    m_phasorLabel = row.Field<string>("PhasorLabel"),
                    m_framesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30),
                    m_id = row.Field<string>("ID"),
                    m_companyAcronym = row.Field<object>("CompanyAcronym") == null ? string.Empty : row.Field<string>("CompanyAcronym"),
                    m_companyName = row.Field<object>("CompanyName") == null ? string.Empty : row.Field<string>("CompanyName"),
                    Selected = false
                };

                return measurement;
            }
            finally
            {
                if (createdConnection && database != null)
                    database.Dispose();
            }
        }

        /// <summary>
        /// LoadKeys <see cref="PowerCalculation"/> information as an <see cref="ObservableCollection{T}"/> style list.
        /// </summary>
        /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
        /// <returns>Collection of <see cref="int"/>.</returns>
        public static IList<int> LoadKeys(AdoDataConnection database)
		{
			var createdConnection = false;

			try
			{
				createdConnection = CreateConnection(ref database);

				var calculationList = new List<int>();
				var queryFormat = "SELECT ID FROM PowerCalculation WHERE NodeID = {0}";
				var calculationTable = database.RetrieveData(queryFormat, database.CurrentNodeID());

				foreach (DataRow row in calculationTable.Rows)
				{
					calculationList.Add(row.ConvertField<int>("ID"));
				}

				return calculationList;
			}
			finally
			{
				if (createdConnection && database != null)
					database.Dispose();
			}
		}

        // Static Methods
        public static Stream ConvertToChartImageStream(DbAdapterContainer dbAdapterContainer, XElement chartElement)
        {
            ChartGenerator chartGenerator;

            Lazy<DataRow> faultSummary;
            Lazy<double> systemFrequency;
            DateTime inception;
            DateTime clearing;

            int width;
            int height;
            double prefaultCycles;
            double postfaultCycles;

            string title;
            List<string> keys;
            List<string> names;
            DateTime startTime;
            DateTime endTime;

            int eventID;
            int faultID;

            // Read parameters from the XML data and set up defaults
            eventID = Convert.ToInt32((string)chartElement.Attribute("eventID") ?? "-1");
            faultID = Convert.ToInt32((string)chartElement.Attribute("faultID") ?? "-1");
            prefaultCycles = Convert.ToDouble((string)chartElement.Attribute("prefaultCycles") ?? "NaN");
            postfaultCycles = Convert.ToDouble((string)chartElement.Attribute("postfaultCycles") ?? "NaN");

            title = (string)chartElement.Attribute("yAxisTitle");
            keys = GetKeys(chartElement);
            names = GetNames(chartElement);

            width = Convert.ToInt32((string)chartElement.Attribute("width"));
            height = Convert.ToInt32((string)chartElement.Attribute("height"));

            startTime = DateTime.MinValue;
            endTime = DateTime.MaxValue;

            using (AdoDataConnection connection = new AdoDataConnection(dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
            {
                faultSummary = new Lazy<DataRow>(() => connection.RetrieveData("SELECT * FROM FaultSummary WHERE ID = {0}", faultID).Select().FirstOrDefault());
                systemFrequency = new Lazy<double>(() => connection.ExecuteScalar(60.0D, "SELECT Value FROM Setting WHERE Name = 'SystemFrequency'"));

                // If prefaultCycles is specified and we have a fault summary we can use,
                // we can determine the start time of the chart based on fault inception
                if (!double.IsNaN(prefaultCycles) && (object)faultSummary.Value != null)
                {
                    inception = faultSummary.Value.ConvertField<DateTime>("Inception");
                    startTime = inception.AddSeconds(-prefaultCycles / systemFrequency.Value);
                }

                // If postfaultCycles is specified and we have a fault summary we can use,
                // we can determine the start time of the chart based on fault clearing
                if (!double.IsNaN(postfaultCycles) && (object)faultSummary.Value != null)
                {
                    inception = faultSummary.Value.ConvertField<DateTime>("Inception");
                    clearing = inception.AddSeconds(faultSummary.Value.ConvertField<double>("DurationSeconds"));
                    endTime = clearing.AddSeconds(postfaultCycles / systemFrequency.Value);
                }

                // Create the chart generator to generate the chart
                chartGenerator = new ChartGenerator(dbAdapterContainer, eventID);

                using (Chart chart = chartGenerator.GenerateChart(title, keys, names, startTime, endTime))
                {
                    // Set the chart size based on the specified width and height;
                    // this allows us to dynamically change font sizes and line
                    // widths before converting the chart to an image
                    SetChartSize(chart, width, height);

                    // Determine if either the minimum or maximum of the y-axis is specified explicitly
                    if ((object)chartElement.Attribute("yAxisMaximum") != null)
                        chart.ChartAreas[0].AxisY.Maximum = Convert.ToDouble((string)chartElement.Attribute("yAxisMaximum"));

                    if ((object)chartElement.Attribute("yAxisMinimum") != null)
                        chart.ChartAreas[0].AxisY.Minimum = Convert.ToDouble((string)chartElement.Attribute("yAxisMinimum"));

                    // If the calculation cycle is to be highlighted, determine whether the highlight should be in the range of a single index or a full cycle.
                    // If we have a fault summary we can use, apply the appropriate highlight based on the calculation cycle
                    if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "index", StringComparison.OrdinalIgnoreCase))
                    {
                        if ((object)faultSummary.Value != null)
                        {
                            int calculationCycle = faultSummary.Value.ConvertField<int>("CalculationCycle");
                            DateTime calculationTime = chartGenerator.ToDateTime(calculationCycle);
                            double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds;
                            chart.ChartAreas[0].CursorX.Position = calculationPosition;
                        }
                    }
                    else if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "cycle", StringComparison.OrdinalIgnoreCase))
                    {
                        if ((object)faultSummary.Value != null)
                        {
                            int calculationCycle = faultSummary.Value.ConvertField<int>("CalculationCycle");
                            DateTime calculationTime = chartGenerator.ToDateTime(calculationCycle);
                            double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds;
                            chart.ChartAreas[0].CursorX.SelectionStart = calculationPosition;
                            chart.ChartAreas[0].CursorX.SelectionEnd = calculationPosition + 1.0D / 60.0D;
//.........这里部分代码省略.........

        // Static Methods

        // ReSharper disable UnusedMember.Local
        // ReSharper disable UnusedParameter.Local
        private static void OptimizeLocalHistorianSettings(AdoDataConnection connection, string nodeIDQueryString, ulong trackingVersion, string arguments, Action<string> statusMessage, Action<Exception> processException)
        {
            // Make sure setting exists to allow user to by-pass local historian optimizations at startup
            ConfigurationFile configFile = ConfigurationFile.Current;
            CategorizedSettingsElementCollection settings = configFile.Settings["systemSettings"];
            settings.Add("OptimizeLocalHistorianSettings", true, "Determines if the defined local historians will have their settings optimized at startup");

            // See if this node should optimize local historian settings
            if (settings["OptimizeLocalHistorianSettings"].ValueAsBoolean())
            {
                statusMessage("Optimizing settings for local historians...");

                // Load the defined local system historians
                IEnumerable<DataRow> historians = connection.RetrieveData($"SELECT AdapterName FROM RuntimeHistorian WHERE NodeID = {nodeIDQueryString} AND TypeName = 'openHistorian.Adapters.LocalOutputAdapter'").AsEnumerable();

                List<string> validHistorians = new List<string>();
                string name, acronym;

                // Apply settings optimizations to local historians
                foreach (DataRow row in historians)
                {
                    acronym = row.Field<string>("AdapterName").ToLower();
                    validHistorians.Add(acronym);
                }

                // Local statics historian is valid regardless of historian type
                if (!validHistorians.Contains("stat"))
                    validHistorians.Add("stat");

                // Sort valid historians for binary search
                validHistorians.Sort();

                // Create a list to track categories to remove
                HashSet<string> categoriesToRemove = new HashSet<string>();

                // Search for unused settings categories
                foreach (PropertyInformation info in configFile.Settings.ElementInformation.Properties)
                {
                    name = info.Name;

                    if (name.EndsWith("AdoMetadataProvider") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("AdoMetadataProvider", StringComparison.Ordinal))) < 0)
                        categoriesToRemove.Add(name);

                    if (name.EndsWith("OleDbMetadataProvider") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("OleDbMetadataProvider", StringComparison.Ordinal))) < 0)
                        categoriesToRemove.Add(name);

                    if (name.EndsWith("RestWebServiceMetadataProvider") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("RestWebServiceMetadataProvider", StringComparison.Ordinal))) < 0)
                        categoriesToRemove.Add(name);

                    if (name.EndsWith("MetadataService") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("MetadataService", StringComparison.Ordinal))) < 0)
                        categoriesToRemove.Add(name);

                    if (name.EndsWith("TimeSeriesDataService") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("TimeSeriesDataService", StringComparison.Ordinal))) < 0)
                        categoriesToRemove.Add(name);

                    if (name.EndsWith("HadoopReplicationProvider") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("HadoopReplicationProvider", StringComparison.Ordinal))) < 0)
                        categoriesToRemove.Add(name);
                }

                if (categoriesToRemove.Count > 0)
                {
                    statusMessage("Removing unused local historian configuration settings...");

                    // Remove any unused settings categories
                    foreach (string category in categoriesToRemove)
                    {
                        configFile.Settings.Remove(category);
                    }
                }

                // Save any applied changes
                configFile.Save();
            }
        }