C# IDbConnection.ExecuteNonQuery方法代码示例

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

        public void Setup()
        {
            Filename = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "sqlite-test.db");

            var connectionString = string.Format("Data Source={0};Version=3;", Filename);

            SQLiteConnection.CreateFile(Filename);

            Connection = new SQLiteConnection(connectionString);

            Connection.Open();

            Connection.ExecuteNonQuery("CREATE TABLE [Person] ([Name] TEXT, [Age] INT);");
        }

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

        /// <summary>
        /// Data operation to validate and ensure that certain records
        /// that are required for alarming exist in the database.
        /// </summary>
        private static void ValidateAlarming(IDbConnection 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;
            int alarmAdapterCount;
            int alarmConfigEntityCount;
            int alarmSignalTypeCount;

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

            if (alarmTableExists)
            {
                // Ensure that the alarm adapter is defined.
                alarmAdapterCount = Convert.ToInt32(connection.ExecuteScalar(string.Format(AlarmAdapterCountFormat, nodeIDQueryString)));

                if (alarmAdapterCount == 0)
                    connection.ExecuteNonQuery(string.Format(AlarmAdapterInsertFormat, nodeIDQueryString));

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

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

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

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

//.........这里部分代码省略.........
            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;
            string statMethodSuffix;
            string statType;
            string statFormat;

            // Values from queries to ensure existence of statistic measurements
            int statHistorianID;
            int statSignalTypeID;
            int statMeasurementCount;
            int runtimeDeviceCount;

            // Statistic measurement info for inserting statistic measurements
            string nodeName;
            string pointTag;
            string signalReference;
            string measurementDescription;

            // Adapter info for inserting gateway measurements
            int adapterID;
            int adapterSourceID;
            string adapterName;
            string companyAcronym;

            // Ensure that STAT signal type exists
            if (statSignalTypeCount == 0)
                connection.ExecuteNonQuery(StatSignalTypeInsertFormat);

            // Ensure that statistics configuration entity record exists
            if (statConfigEntityCount == 0)
                connection.ExecuteNonQuery(StatConfigEntityInsertFormat);

            // Ensure that statistics historian exists
            if (statHistorianCount == 0)
                connection.ExecuteNonQuery(string.Format(StatHistorianInsertFormat, nodeIDQueryString));

            // Ensure that statistics engine exists
            if (statEngineCount == 0)
                connection.ExecuteNonQuery(string.Format(StatEngineInsertFormat, nodeIDQueryString));

            // Ensure that system statistics exist
            if (systemStatCount == 0)
            {
                for (int i = 0; i < SystemStatNames.Length; i++)
                {
                    signalIndex = i + 1;
                    statName = SystemStatNames[i];
                    statDescription = SystemStatDescriptions[i];
                    statMethodSuffix = statName.Replace(" ", "");
                    connection.ExecuteNonQuery(string.Format(SystemStatInsertFormat, signalIndex, statName, statDescription, statMethodSuffix));
                }
            }

            // Ensure that subscriber statistics exist
            if (subscriberStatCount == 0)
            {
                for (int i = 0; i < SubscriberStatNames.Length; i++)
                {
                    signalIndex = i + 1;

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

        public void Reset (IDbConnection conn)
        {
            diskFilepath = null;

            memConn = conn;
            memConn.ExecuteNonQuery(@"PRAGMA journal_mode=OFF;
                                      PRAGMA synchronous=OFF;");
            SessionFactoryFactory.DropIndexes(memConn);

            insertCommandByTable = new List<KeyValuePair<IDbCommand, List<object[]>>>();
            for (int i = 0; i <= (int) Table.QonverterSettings; ++i)
                insertCommandByTable.Add(new KeyValuePair<IDbCommand, List<object[]>>());

            insertCommandByTable[(int) Table.Protein] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.Protein].Key.CommandText = createInsertSql("Protein", "Id, Accession, Length");

            insertCommandByTable[(int) Table.ProteinMetadata] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.ProteinMetadata].Key.CommandText = createInsertSql("ProteinMetadata", "Id, Description");

            insertCommandByTable[(int) Table.ProteinData] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.ProteinData].Key.CommandText = createInsertSql("ProteinData", "Id, Sequence");

            insertCommandByTable[(int) Table.Peptide] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.Peptide].Key.CommandText = createInsertSql("Peptide", "Id, MonoisotopicMass, MolecularWeight");

            insertCommandByTable[(int) Table.PeptideSequences] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.PeptideSequences].Key.CommandText = createInsertSql("PeptideSequences", "Id, Sequence");

            insertCommandByTable[(int) Table.PeptideInstance] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.PeptideInstance].Key.CommandText = createInsertSql("PeptideInstance", "Id, Protein, Peptide, Offset, Length, NTerminusIsSpecific, CTerminusIsSpecific, MissedCleavages");

            insertCommandByTable[(int) Table.Analysis] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.Analysis].Key.CommandText = createInsertSql("Analysis", "Id, Name, SoftwareName, SoftwareVersion, StartTime, Type");

            insertCommandByTable[(int) Table.AnalysisParameter] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.AnalysisParameter].Key.CommandText = createInsertSql("AnalysisParameter", "Id, Analysis, Name, Value");

            insertCommandByTable[(int) Table.SpectrumSourceGroup] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.SpectrumSourceGroup].Key.CommandText = createInsertSql("SpectrumSourceGroup", "Id, Name");

            insertCommandByTable[(int) Table.SpectrumSource] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.SpectrumSource].Key.CommandText = createInsertSql("SpectrumSource", "Id, Name, URL, Group_");

            insertCommandByTable[(int) Table.SpectrumSourceMetadata] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.SpectrumSourceMetadata].Key.CommandText = createInsertSql("SpectrumSourceMetadata", "Id, MsDataBytes");

            insertCommandByTable[(int) Table.SpectrumSourceGroupLink] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.SpectrumSourceGroupLink].Key.CommandText = createInsertSql("SpectrumSourceGroupLink", "Id, Source, Group_");

            insertCommandByTable[(int) Table.Spectrum] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.Spectrum].Key.CommandText = createInsertSql("Spectrum", "Id, Index_, NativeID, Source, PrecursorMZ");

            insertCommandByTable[(int) Table.PeptideSpectrumMatch] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.PeptideSpectrumMatch].Key.CommandText =
                createInsertSql("PeptideSpectrumMatch",
                                "Id, Peptide, Spectrum, Analysis, " +
                                "ObservedNeutralMass, " +
                                "MonoisotopicMassError, MolecularWeightError, " +
                                "Rank, QValue, Charge");

            insertCommandByTable[(int) Table.PeptideSpectrumMatchScoreName] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.PeptideSpectrumMatchScoreName].Key.CommandText = createInsertSql("PeptideSpectrumMatchScoreName", "Id, Name");

            insertCommandByTable[(int) Table.PeptideSpectrumMatchScore] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.PeptideSpectrumMatchScore].Key.CommandText = createInsertSql("PeptideSpectrumMatchScore", "PsmId, ScoreNameId, Value");

            insertCommandByTable[(int) Table.Modification] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.Modification].Key.CommandText = createInsertSql("Modification", "Id, MonoMassDelta, AvgMassDelta, Name, Formula");

            insertCommandByTable[(int) Table.PeptideModification] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.PeptideModification].Key.CommandText = createInsertSql("PeptideModification", "Id, Modification, PeptideSpectrumMatch, Offset, Site");

            insertCommandByTable[(int) Table.IntegerSet] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.IntegerSet].Key.CommandText = "INSERT INTO IntegerSet VALUES (?)";

            insertCommandByTable[(int) Table.QonverterSettings] = new KeyValuePair<IDbCommand, List<object[]>>(conn.CreateCommand(), new List<object[]>());
            insertCommandByTable[(int) Table.QonverterSettings].Key.CommandText = createInsertSql("QonverterSettings", "Id, QonverterMethod, DecoyPrefix, RerankMatches, ScoreInfoByName");

            foreach (var itr in insertCommandByTable)
            {
                createParameters(itr.Key);
                itr.Key.Prepare();
            }

            scoreIdByName = new Dictionary<string, long>();

            foreach (var queryRow in conn.ExecuteQuery("SELECT Name, Id FROM PeptideSpectrumMatchScoreName"))
                scoreIdByName[queryRow.GetString(0)] = queryRow.GetInt64(1);

            /*diskConn.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS PeptideSequences (Id INTEGER PRIMARY KEY, Sequence TEXT)");
            diskConn.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS IntegerSet (Value INTEGER PRIMARY KEY)");
            currentMaxSequenceLength = diskConn.ExecuteQuery("SELECT IFNULL(MAX(Value),0) FROM IntegerSet").First().GetInt64(0);
            */
            memConn.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS PeptideSequences (Id INTEGER PRIMARY KEY, Sequence TEXT)");
            transaction = memConn.BeginTransaction();
        }