private async Task InsertGlobalStateTableRowAsync()

in src/TriggerBinding/SqlTriggerListener.cs [373:426]
47 lines of code
6 McCabe index (conditional complexity)

        private async Task<long> InsertGlobalStateTableRowAsync(SqlConnection connection, SqlTransaction transaction, int userTableId, CancellationToken cancellationToken)
        {
            object minValidVersion;

            string getMinValidVersionQuery = $"SELECT CHANGE_TRACKING_MIN_VALID_VERSION({userTableId});";

            using (var getMinValidVersionCommand = new SqlCommand(getMinValidVersionQuery, connection, transaction))
            using (SqlDataReader reader = getMinValidVersionCommand.ExecuteReaderWithLogging(this._logger))
            {
                if (!await reader.ReadAsync(cancellationToken))
                {
                    throw new InvalidOperationException($"Received empty response when querying the 'change tracking min valid version' for table: '{this._userTable.FullName}'.");
                }

                minValidVersion = reader.GetValue(0);

                if (minValidVersion is DBNull)
                {
                    throw new InvalidOperationException($"Could not find change tracking enabled for table: '{this._userTable.FullName}'.");
                }
            }

            string insertRowGlobalStateTableQuery = $@"
                {AppLockStatements}
                -- For back compatibility copy the lastSyncVersion from _oldUserFunctionId if it exists.
                IF NOT EXISTS (
                    SELECT * FROM {GlobalStateTableName}
                    WHERE UserFunctionID = '{this._userFunctionId}' AND UserTableID = {userTableId}
                )
                BEGIN
                    -- Migrate LastSyncVersion from oldUserFunctionId if it exists and delete the record
                    DECLARE @lastSyncVersion bigint;
                    SELECT @lastSyncVersion = LastSyncVersion from az_func.GlobalState where UserFunctionID = '{this._oldUserFunctionId}' AND UserTableID = {userTableId}
                    IF @lastSyncVersion IS NULL
                        SET @lastSyncVersion = {(long)minValidVersion};
                    ELSE
                        DELETE FROM az_func.GlobalState WHERE UserFunctionID = '{this._oldUserFunctionId}' AND UserTableID = {userTableId}
                    
                    INSERT INTO {GlobalStateTableName}
                    VALUES ('{this._userFunctionId}', {userTableId}, @lastSyncVersion, GETUTCDATE());
                END
            ";

            using (var insertRowGlobalStateTableCommand = new SqlCommand(insertRowGlobalStateTableQuery, connection, transaction))
            {
                var stopwatch = Stopwatch.StartNew();
                int rowsInserted = await insertRowGlobalStateTableCommand.ExecuteNonQueryAsyncWithLogging(this._logger, cancellationToken);
                if (rowsInserted > 0)
                {
                    TelemetryInstance.TrackEvent(TelemetryEventName.InsertGlobalStateTableRow);
                }
                return stopwatch.ElapsedMilliseconds;
            }
        }