C# IDbConnection.ExecuteScalar方法代码示例

        private static void Rollup(string baseTableName, int level, IDbConnection c, string innerRollupSQL)
        {
            var sourceTableName = GetSourceTableName(baseTableName, level);
            var rollupTableName = GetRollupTableName(baseTableName, level);

            int? lastRollupTimestamp = GetLastRollupTimestamp(baseTableName, level, c);
            if (lastRollupTimestamp == null)
            {
                // never rolled up, get a new min timestamp
                lastRollupTimestamp = c.ExecuteScalar<int>("SELECT MIN(Timestamp) FROM " + sourceTableName);
                if (lastRollupTimestamp == null) { return; } // no rows, no work
                lastRollupTimestamp = RoundDownToNearestRollupStartTimestamp(lastRollupTimestamp.Value, level);
            }

            while (IsTimeToRollup(lastRollupTimestamp.Value, level)) // loop until we're completely rolled up
            {
                var maxTimstamp = lastRollupTimestamp.Value + GetLevelRollupInterval(level);
                using (var t = c.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    var sql = string.Format(OuterRollupSQL, string.Format(innerRollupSQL, rollupTableName, sourceTableName));
                    c.Execute(sql, param: new
                    {
                        minTS = lastRollupTimestamp.Value,
                        maxTS = maxTimstamp,
                        RollupTableName = rollupTableName,
                    }, transaction: t);
                    t.Commit();
                }
                lastRollupTimestamp = maxTimstamp; // next loop
            }
        }

		public virtual string GetDatabaseVersion(IDbConnection connection)
		{
			var result = connection.ExecuteScalar(
				@"SELECT CHARACTER_VALUE " +
				@"FROM INFORMATION_SCHEMA.SQL_IMPLEMENTATION_INFO " +
				@"WHERE IMPLEMENTATION_INFO_NAME='DBMS VERSION'");

			return Convert.ToString(result);
		}

        public SubProcessor(uint subID)
        {
            SubID = subID;

            DbConnection = Database.GetConnection();

            PackageName = DbConnection.ExecuteScalar<string>("SELECT `Name` FROM `Subs` WHERE `SubID` = @SubID LIMIT 1", new { SubID });
            CurrentData = DbConnection.Query<PICSInfo>("SELECT `Name` as `KeyName`, `Value`, `Key` FROM `SubsInfo` INNER JOIN `KeyNamesSubs` ON `SubsInfo`.`Key` = `KeyNamesSubs`.`ID` WHERE `SubID` = @SubID", new { SubID }).ToDictionary(x => x.KeyName, x => x);
        }

		public override bool SupportsThisDialect(IDbConnection connection)
		{
			try
			{
				var versionString = (string)connection.ExecuteScalar(@"SELECT VERSION();");
				return versionString.StartsWith("PostgreSQL ");
			}
			catch
			{
				return false;
			}
		}

        /// <summary>
        /// Data operation to validate and ensure there is a node in the database.
        /// </summary>
        private static void ValidateDefaultNode(IDbConnection connection, string nodeIDQueryString)
        {
            // Queries
            const string NodeCountFormat = "SELECT COUNT(*) FROM Node";

            const string NodeInsertFormat = "INSERT INTO Node(Name, CompanyID, Description, Settings, MenuType, MenuData, Master, LoadOrder, Enabled) " +
                "VALUES('Default', NULL, 'Default node', 'RemoteStatusServerConnectionString={server=localhost:8500};datapublisherport=6165;RealTimeStatisticServiceUrl=http://localhost:6052/historian', " +
                "'File', 'Menu.xml', 1, 0, 1)";

            const string NodeUpdateFormat = "UPDATE Node SET ID = {0}";

            // Determine whether the node exists in the database and create it if it doesn't.
            int nodeCount = Convert.ToInt32(connection.ExecuteScalar(NodeCountFormat));

            if (nodeCount == 0)
            {
                connection.ExecuteNonQuery(NodeInsertFormat);
                connection.ExecuteNonQuery(string.Format(NodeUpdateFormat, nodeIDQueryString));
            }
        }

        // Static Methods

        /// <summary>
        /// Deletes old <see cref="RestoreLogSize"/> record from database.
        /// </summary>
        /// <param name="dbConnection"><see cref="IDbConnection"/> to connection to database.</param>
        private static void RestoreLogSize(IDbConnection dbConnection)
        {
            string query = string.Empty;
            int minIDToDelete = 0;

            try
            {
                while (Convert.ToInt32(dbConnection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM ErrorLog"))) >= DefaultExceptionLogSize)
                {
                    minIDToDelete = Convert.ToInt32(dbConnection.ExecuteScalar(string.Format("SELECT MIN(ID) FROM ErrorLog")));
                    dbConnection.ExecuteNonQuery(string.Format("DELETE FROM ErrorLog WHERE ID = {0}", minIDToDelete));
                }
            }
            catch
            {
                // Do nothing at this time.
            }
        }

        /// <summary>
        /// Data operation to validate and ensure there is a record
        /// in the ConfigurationEntity table for ActiveMeasurements.
        /// </summary>
        private static void ValidateActiveMeasurements(IDbConnection connection, string nodeIDQueryString)
        {
            const string MeasurementConfigEntityCountFormat = "SELECT COUNT(*) FROM ConfigurationEntity WHERE RuntimeName = 'ActiveMeasurements'";
            const string MeasurementConfigEntityInsertFormat = "INSERT INTO ConfigurationEntity(SourceName, RuntimeName, Description, LoadOrder, Enabled) VALUES('ActiveMeasurement', 'ActiveMeasurements', 'Defines active system measurements for a TSF node', 4, 1)";

            int measurementConfigEntityCount = Convert.ToInt32(connection.ExecuteScalar(MeasurementConfigEntityCountFormat));

            if (measurementConfigEntityCount == 0)
                connection.ExecuteNonQuery(MeasurementConfigEntityInsertFormat);
        }

        // Attempts to get company acronym from database and, failing
        // that, attempts to get it from the configuration file.
        private static string GetCompanyAcronym(IDbConnection connection, string nodeIDQueryString)
        {
            const string NodeCompanyIDFormat = "SELECT CompanyID FROM Node WHERE ID = {0}";
            const string CompanyAcronymFormat = "SELECT MapAcronym FROM Company WHERE ID = {0}";

            int nodeCompanyID;
            string companyAcronym;

            nodeCompanyID = int.Parse(connection.ExecuteScalar(string.Format(NodeCompanyIDFormat, nodeIDQueryString)).ToNonNullString("0"));

            if (nodeCompanyID > 0)
                companyAcronym = connection.ExecuteScalar(string.Format(CompanyAcronymFormat, nodeCompanyID)).ToNonNullString();
            else
                companyAcronym = ConfigurationFile.Current.Settings["systemSettings"]["CompanyAcronym"].Value.TruncateRight(3);

            return companyAcronym;
        }

        // Attempts to get company acronym from device table in database
        private static bool TryGetCompanyAcronymFromDevice(IDbConnection connection, int deviceID, out string companyAcronym)
        {
            string CompanyIDFormat = "SELECT CompanyID FROM Device WHERE ID = {0}";
            string CompanyAcronymFormat = "SELECT MapAcronym FROM Company WHERE ID = {0}";
            int companyID;

            try
            {
                companyID = Convert.ToInt32(connection.ExecuteScalar(string.Format(CompanyIDFormat, deviceID)));
                companyAcronym = connection.ExecuteScalar(string.Format(CompanyAcronymFormat, companyID)).ToNonNullString();
                return true;
            }
            catch
            {
                companyAcronym = string.Empty;
                return false;
            }
        }

 // Gets the name of the node identified by the given node ID query string.
 private static string GetNodeName(IDbConnection connection, string nodeIDQueryString)
 {
     const string NodeNameFormat = "SELECT Name FROM Node WHERE ID = {0}";
     return connection.ExecuteScalar(string.Format(NodeNameFormat, nodeIDQueryString)).ToString();
 }

        private static int? GetLastRollupTimestamp(string baseTableName, int level, IDbConnection c)
        {
            return c.ExecuteScalar<int?>(@"
MERGE
    dbo.HAProxyTrafficRollup AS t 
USING
    (SELECT @RollupLevel AS [RollupLevel], @BaseTableName AS [BaseTableName]) AS s
 ON t.[RollupLevel]   = s.[RollupLevel]
AND t.[BaseTableName] = s.[BaseTableName]
WHEN MATCHED THEN
    UPDATE SET @[email protected]
WHEN NOT MATCHED THEN
    INSERT ([BaseTableName], [RollupTableName], [SourceTableName], [RollupLevel]) VALUES (@BaseTableName, @RollupTableName, @SourceTableName, @RollupLevel)
OUTPUT inserted.LastRollupTimestamp;
", param: new
 {
     RollupTableName = new DbString { Value = GetRollupTableName(baseTableName, level), Length = 200, IsAnsi = true, },
     SourceTableName = new DbString { Value = GetSourceTableName(baseTableName, level), Length = 200, IsAnsi = true, },
     BaseTableName = new DbString { Value = baseTableName, Length = 200, IsAnsi = true, },
     RollupLevel = level,
 });
        }

        /// <summary>
        /// Data operation to validate and ensure that certain records that
        /// are required for statistics calculations exist in the database.
        /// </summary>
        private static void ValidateStatistics(IDbConnection connection, string nodeIDQueryString)
        {
            // SELECT queries
            const string StatConfigEntityCountFormat = "SELECT COUNT(*) FROM ConfigurationEntity WHERE RuntimeName = 'Statistics'";
            const string StatSignalTypeCountFormat = "SELECT COUNT(*) FROM SignalType WHERE Acronym = 'STAT'";

            const string StatHistorianCountFormat = "SELECT COUNT(*) FROM Historian WHERE Acronym = 'STAT' AND NodeID = {0}";
            const string StatEngineCountFormat = "SELECT COUNT(*) FROM CustomActionAdapter WHERE AdapterName = 'STATISTIC!SERVICES' AND NodeID = {0}";
            const string SystemStatCountFormat = "SELECT COUNT(*) FROM Statistic WHERE Source = 'System'";
            const string SubscriberStatCountFormat = "SELECT COUNT(*) FROM Statistic WHERE Source = 'Subscriber'";
            const string PublisherStatCountFormat = "SELECT COUNT(*) FROM Statistic WHERE Source = 'Publisher'";
            const string RuntimeDeviceCountFormat = "SELECT COUNT(*) FROM Runtime WHERE ID = {0} AND SourceTable = 'Device'";

            const string StatHistorianIDFormat = "SELECT ID FROM Historian WHERE Acronym = 'STAT' AND NodeID = {0}";
            const string StatSignalTypeIDFormat = "SELECT ID FROM SignalType WHERE Acronym = 'STAT'";
            const string StatMeasurementCountFormat = "SELECT COUNT(*) FROM Measurement WHERE SignalReference = '{0}' AND HistorianID = {1}";

            const string SubscriberRowsFormat = "SELECT * FROM IaonInputAdapter WHERE TypeName = 'TimeSeriesFramework.Transport.DataSubscriber' AND NodeID = {0}";
            const string PublisherRowsFormat = "SELECT * FROM IaonActionadapter WHERE TypeName = 'TimeSeriesFramework.Transport.DataPublisher' AND NodeID = {0}";
            const string RuntimeSourceIDFormat = "SELECT SourceID FROM Runtime WHERE ID = {0}";

            // INSERT queries
            const string StatConfigEntityInsertFormat = "INSERT INTO ConfigurationEntity(SourceName, RuntimeName, Description, LoadOrder, Enabled) VALUES('RuntimeStatistic', 'Statistics', 'Defines statistics that are monitored for the system, devices, and output streams', 11, 1)";
            const string StatSignalTypeInsertFormat = "INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, Source, EngineeringUnits) VALUES('Statistic', 'STAT', 'ST', 'P', 'Any', '')";

            const string StatHistorianInsertFormat = "INSERT INTO Historian(NodeID, Acronym, Name, AssemblyName, TypeName, ConnectionString, IsLocal, Description, LoadOrder, Enabled) VALUES({0}, 'STAT', 'Statistics Archive', 'TestingAdapters.dll', 'TestingAdapters.VirtualOutputAdapter', '', 1, 'Local historian used to archive system statistics', 9999, 1)";
            const string StatEngineInsertFormat = "INSERT INTO CustomActionAdapter(NodeID, AdapterName, AssemblyName, TypeName, LoadOrder, Enabled) VALUES({0}, 'STATISTIC!SERVICES', 'TimeSeriesFramework.dll', 'TimeSeriesFramework.Statistics.StatisticsEngine', 0, 1)";
            const string SystemStatInsertFormat = "INSERT INTO Statistic(Source, SignalIndex, Name, Description, AssemblyName, TypeName, MethodName, Arguments, Enabled, DataType, DisplayFormat, IsConnectedState, LoadOrder) VALUES('System', {0}, '{1}', '{2}', 'TimeSeriesFramework.dll', 'TimeSeriesFramework.Statistics.PerformanceStatistics', 'GetSystemStatistic_{3}', '', 1, 'System.Double', '{{0:N3}}', 0, {0})";
            const string SubscriberStatInsertFormat = "INSERT INTO Statistic(Source, SignalIndex, Name, Description, AssemblyName, TypeName, MethodName, Arguments, Enabled, DataType, DisplayFormat, IsConnectedState, LoadOrder) VALUES('Subscriber', {0}, '{1}', '{2}', 'TimeSeriesFramework.dll', 'TimeSeriesFramework.Statistics.GatewayStatistics', 'GetSubscriberStatistic_{3}', '', 1, '{4}', '{5}', 0, {0})";
            const string PublisherStatInsertFormat = "INSERT INTO Statistic(Source, SignalIndex, Name, Description, AssemblyName, TypeName, MethodName, Arguments, Enabled, DataType, DisplayFormat, IsConnectedState, LoadOrder) VALUES('Publisher', {0}, '{1}', '{2}', 'TimeSeriesFramework.dll', 'TimeSeriesFramework.Statistics.GatewayStatistics', 'GetPublisherStatistic_{3}', '', 1, '{4}', '{5}', 0, {0})";

            const string StatMeasurementInsertFormat = "INSERT INTO Measurement(HistorianID, PointTag, SignalTypeID, SignalReference, Description, Enabled) VALUES({0}, {1}, {2}, {3}, {4}, 1)";
            const string SubscriberDeviceStatMeausrementInsertFormat = "INSERT INTO Measurement(HistorianID, DeviceID, PointTag, SignalTypeID, SignalReference, Description, Enabled) VALUES({0}, {1}, {2}, {3}, {4}, {5}, 1)";

            // Names and descriptions for each of the statistics
            string[] SystemStatNames = { "CPU Usage", "Average CPU Usage", "Memory Usage", "Average Memory Usage", "Thread Count", "Average Thread Count", "Threading Contention Rate", "Average Threading Contention Rate", "IO Usage", "Average IO Usage", "Datagram Send Rate", "Average Datagram Send Rate", "Datagram Receive Rate", "Average Datagram Receive Rate" };

            string[] SystemStatDescriptions = { "Percentage of CPU currently used by this process.",
                                                "Average percentage of CPU used by this process.",
                                                "Amount of memory currently used by this process in megabytes.",
                                                "Average amount of memory used by this process in megabytes.",
                                                "Number of threads currently used by this process.",
                                                "Average number of threads used by this process.",
                                                "Current thread lock contention rate in attempts per second.",
                                                "Average thread lock contention rate in attempts per second.",
                                                "Amount of IO currently used by this process in kilobytes per second.",
                                                "Average amount of IO used by this process in kilobytes per second.",
                                                "Number of IPv4 datagrams currently sent by this process per second.",
                                                "Average number of IPv4 datagrams sent by this process per second.",
                                                "Number of IPv4 datagrams currently received by this process per second.",
                                                "Average number of IPv4 datagrams received by this process per second."
                                              };

            string[] SubscriberStatNames = { "Subscriber Connected", "Subscriber Authenticated", "Processed Measurements", "Total Bytes Received", "Authorized Signal Count", "Unauthorized Signal Count" };

            string[] SubscriberStatDescriptions = { "Boolean value representing if the subscriber was continually connected during last reporting interval.",
                                                    "Boolean value representing if the subscriber was authenticated to the publisher during last reporting interval.",
                                                    "Number of processed measurements reported by the subscriber during last reporting interval.",
                                                    "Number of bytes received from subscriber during last reporting interval.",
                                                    "Number of signals authorized to the subscriber by the publisher.",
                                                    "Number of signals denied to the subscriber by the publisher."
                                                  };

            string[] SubscriberStatMethodSuffix = { "Connected", "Authenticated", "ProcessedMeasurements", "TotalBytesReceived", "AuthorizedCount", "UnauthorizedCount" };
            string[] SubscriberStatTypes = { "System.Boolean", "System.Boolean", "System.Int32", "System.Int32", "System.Int32", "System.Int32" };
            string[] SubscriberStatFormats = { "{0}", "{0}", "{0:N0}", "{0:N0}", "{0:N0}", "{0:N0}" };

            string[] PublisherStatNames = { "Publisher Connected", "Connected Clients", "Processed Measurements" };

            string[] PublisherStatDescriptions = { "Boolean value representing if the publisher was continually connected during last reporting interval.",
                                                   "Number of clients connected to the command channel of the publisher during last reporting interval.",
                                                   "Number of processed measurements reported by the publisher during last reporting interval."
                                                 };

            string[] PublisherStatMethodSuffix = { "Connected", "ConnectedClientCount", "ProcessedMeasurements" };
            string[] PublisherStatTypes = { "System.Boolean", "System.Int32", "System.Int32" };
            string[] PublisherStatFormats = { "{0}", "{0:N0}", "{0:N0}" };

            // Parameterized query string for inserting statistic measurements
            string statMeasurementInsertQuery = ParameterizedQueryString(connection.GetType(), StatMeasurementInsertFormat, "historianID", "pointTag", "signalTypeID", "signalReference", "description");
            string subscriberDeviceStatInsertQuery = ParameterizedQueryString(connection.GetType(), SubscriberDeviceStatMeausrementInsertFormat, "historianID", "deviceID", "pointTag", "signalTypeID", "signalReference", "description");

            // Query for count values to ensure existence of these records
            int statConfigEntityCount = Convert.ToInt32(connection.ExecuteScalar(StatConfigEntityCountFormat));
            int statSignalTypeCount = Convert.ToInt32(connection.ExecuteScalar(StatSignalTypeCountFormat));

            int statHistorianCount = Convert.ToInt32(connection.ExecuteScalar(string.Format(StatHistorianCountFormat, nodeIDQueryString)));
            int statEngineCount = Convert.ToInt32(connection.ExecuteScalar(string.Format(StatEngineCountFormat, nodeIDQueryString)));
            int systemStatCount = Convert.ToInt32(connection.ExecuteScalar(SystemStatCountFormat));
            int subscriberStatCount = Convert.ToInt32(connection.ExecuteScalar(SubscriberStatCountFormat));
            int publisherStatCount = Convert.ToInt32(connection.ExecuteScalar(PublisherStatCountFormat));

            // Statistic info for inserting statistics
            int signalIndex;
            string statName;
            string statDescription;
//.........这里部分代码省略.........

        /// <summary>
        /// Data operation to validate and ensure there is a record in the
        /// CustomActionAdapter table for the external data publisher.
        /// </summary>
        private static void ValidateExternalDataPublisher(IDbConnection connection, string nodeIDQueryString)
        {
            const string ExternalDataPublisherCountFormat = "SELECT COUNT(*) FROM CustomActionAdapter WHERE AdapterName='EXTERNAL!DATAPUBLISHER' AND NodeID = {0}";
            const string ExternalDataPublisherInsertFormat = "INSERT INTO CustomActionAdapter(NodeID, AdapterName, AssemblyName, TypeName, ConnectionString, Enabled) VALUES({0}, 'EXTERNAL!DATAPUBLISHER', 'TimeSeriesFramework.dll', 'TimeSeriesFramework.Transport.DataPublisher', 'requireAuthentication=true; allowSynchronizedSubscription=false; useBaseTimeOffsets=true', 1)";

            int externalDataPublisherCount = Convert.ToInt32(connection.ExecuteScalar(string.Format(ExternalDataPublisherCountFormat, nodeIDQueryString)));

            if (externalDataPublisherCount == 0)
                connection.ExecuteNonQuery(string.Format(ExternalDataPublisherInsertFormat, nodeIDQueryString));
        }

        private static int GetMetricID(string key, string type, IDbConnection c)
        {
            var metricId = c.ExecuteScalar<int>(@"
MERGE
    " + SchemaName + @".Metric AS t 
USING
    (SELECT @Key AS [Key], @Type AS [Type]) AS s
ON t.[Key] = s.[Key]
WHEN MATCHED THEN
    UPDATE SET t.[Key] = s.[Key]
WHEN NOT MATCHED THEN
    INSERT ([Key], [Type]) VALUES (s.[Key], s.[Type])
OUTPUT inserted.MetricID;
", new { Key = key, Type = type });
            return metricId;
        }

        private static string GetMetricTableName(int metricID, string tableNamePrefix, IDbConnection c)
        {
            var tableName = c.ExecuteScalar<string>(@"
MERGE
    " + SchemaName + @".MetricTable AS t 
USING
    (SELECT @MetricId AS MetricID) AS s
ON t.MetricID = s.MetricID
WHEN MATCHED THEN
    UPDATE SET t.TableName = @prefix + CAST(s.MetricID AS VARCHAR(10))
WHEN NOT MATCHED THEN
    INSERT (MetricID, TableName) VALUES (s.MetricID, @prefix + CAST(s.MetricID AS VARCHAR(10)))
OUTPUT inserted.TableName;
", new { MetricId = metricID, prefix = new DbString { Value = tableNamePrefix, Length = 2, IsAnsi = true, } });

            c.ExecuteScalar<string>(@"
MERGE
    " + SchemaName + @".MetricTableDataRollupLevel AS t 
USING
    (SELECT @MetricId AS MetricID, 1 AS [Level]) AS s
ON t.MetricID = s.MetricID AND t.[Level] = s.[Level]
WHEN NOT MATCHED THEN
    INSERT (MetricID, [Level], RollupTableName) VALUES (s.MetricID, 1, @prefix + CAST(s.MetricID AS VARCHAR(10)) + '_RollupLevel1');
", new { MetricId = metricID, prefix = new DbString { Value = tableNamePrefix, Length = 2, IsAnsi = true, } });

            return tableName;
        }